Automating SQL Server Express Backups

As we all know that many of the third party applications uses SQL Server Express Edition to store their backend data. And also these application will be used in Live environments which requires backing up of the databases to recover data during a disaster.
Since there is no SQL Server agent in Express Edition, we cannot schedule SQL Backups or any other DB Maintenance activities using SQL Scheduler. So for this purpose, we have to make use of the windows scheduler.
For Automating the backup process, i have developed the below query which can be used for multiple purpose and can be scheduled using windows scheduler as well as SQL Agent.

Script:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_BackupDatabases]') AND type in (N'P', N'PC')) BEGIN PRINT 'Stored Procedure "sp_BackupDatabases" already exists in the database. Dropping the SP to create a newer Version.' DROP PROCEDURE [dbo].[sp_BackupDatabases] END GO CREATE PROC sp_BackupDatabases (@BackupDBType nvarchar(10)='Help', @DBName nvarchar(max)=NULL, @BackupPath nvarchar(max)=NULL ) AS /* Author: Sandesh Segu Website: http://www.SansSQL.com */ SET NOCOUNT ON DECLARE @DateTime nvarchar(25) SET @DateTime=LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),120),':',''),'-',''),' ',''),12) IF @BackupDBType not in ('ALL','System','User','Specific','Help') BEGIN RAISERROR ('Incorrect Parameter Value Passed. @BackupDBType Parameters should be ''ALL'',''System'',''User'',''Specific''',16,1) END IF @BackupDBType<> 'HELP' and @BackupPath IS NULL BEGIN RAISERROR ('Specify the path to backup databases. @BackupPath must be specified',16,1) END IF (RIGHT(@BackupPath,1))<>'\' BEGIN Select @BackupPath=@BackupPath+'\' END ELSE BEGIN Select @BackupPath=@BackupPath END IF @BackupDBType='Help' AND @DBName IS NULL AND @BackupPath IS NULL BEGIN Print 'Usage of this Stored Proc can be in any of the below format.' Print '-------------------------------------------------------------------------' Print '1. To Backup All Databases in a instance' Print '   Exec sp_BackupDatabases @BackupDBType = ''ALL'', @BackupPath = ''C:\Backup''' + Char(10) Print '2. To Backup only System Databases in a instance' Print '   Exec sp_BackupDatabases @BackupDBType = ''System'', @BackupPath = ''C:\Backup''' + Char(10) Print '3. To Backup only User Databases in a instance' Print '   Exec sp_BackupDatabases @BackupDBType = ''User'', @BackupPath = ''C:\Backup''' + Char(10) Print '4. To Backup specific (One) Database(s) in a instance' Print '   Exec sp_BackupDatabases @BackupDBType = ''Specific'', @DBName = ''AdventureWorks'', @BackupPath = ''C:\Backup''' + Char(10) Print '5. To Backup specific (more than One) Database(s) in a instance' Print '   Exec sp_BackupDatabases @BackupDBType = ''Specific'', @DBName = ''AdventureWorks,master,msdb'', @BackupPath = ''C:\Backup''' Print '-------------------------------------------------------------------------' END IF exists (select * from sys.objects where name='BackupDatabases') DROP TABLE BackupDatabases CREATE TABLE BackupDatabases (DBName nvarchar(100), DatabaseID int, [BackupStatement] nvarchar(max)) IF @BackupDBType='ALL' AND @DBName IS NULL AND @BackupPath IS NOT NULL BEGIN INSERT INTO BackupDatabases SELECT name,database_id, '' FROM sys.databases WHERE name <>'tempdb' END IF @BackupDBType='System' AND @DBName IS NULL AND @BackupPath IS NOT NULL BEGIN INSERT INTO BackupDatabases SELECT name,database_id, '' FROM sys.databases WHERE name in ('master', 'model','msdb') END IF @BackupDBType='User' AND @DBName IS NULL AND @BackupPath IS NOT NULL BEGIN INSERT INTO BackupDatabases SELECT name,database_id, '' FROM sys.databases WHERE database_id>4 END IF @BackupDBType='Specific' AND @DBName IS NULL BEGIN RAISERROR ('Specify the Database Name(s) to Backup. @DBName must be specified.',16,1) END IF @BackupDBType='Specific' AND @DBName IS NOT NULL AND @BackupPath IS NOT NULL BEGIN DECLARE @DelimiterPos int -- Find the first comma SET @DelimiterPos = PATINDEX( '%,%', @DBName) -- If a delimiter was found, @DelimiterPos will be > 0. WHILE @DelimiterPos > 0 BEGIN -- Insert the value between the start of the string and the first delimiter, into the table variable. INSERT INTO BackupDatabases SELECT name,database_id, '' FROM sys.databases WHERE name in (SELECT CAST(LTRIM(RTRIM((SUBSTRING(@DBName, 1, @DelimiterPos -1)))) AS nvarchar      ))                   -- Trim the string of the first value and delimiter. SET @DBName = SUBSTRING(@DBName, @DelimiterPos +1, LEN(@DBName) - @DelimiterPos)                         -- Look for the next delimiter in the string. SET @DelimiterPos = PATINDEX( '%,%', @DBName) END INSERT INTO BackupDatabases SELECT name,database_id, '' FROM sys.databases WHERE name in (SELECT CAST(LTRIM(RTRIM((@DBName))) AS nvarchar)) END UPDATE BackupDatabases SET BackupStatement= 'Print ''Backup of Database '+DBName+' Started''; Backup Database ['+DBName+'] TO DISK='''+@BackupPath+DBName+'_Backup_'+@DateTime+'.bak'' WITH INIT, STATS=10' Print 'The Requested database(s) are being backed up to the location "'+@BackupPath+'"' WHILE (Select COUNT(*) from BackupDatabases)>0 BEGIN DECLARE @BackupStatement nvarchar(max) SELECT @BackupStatement= [BackupStatement] FROM BackupDatabases Exec sp_executesql @BackupStatement Delete from BackupDatabases where [BackupStatement]=@BackupStatement END DROP TABLE BackupDatabases SET NOCOUNT OFF GO

Usage:

Usage of this Stored Proc can be in any of the below format. ------------------------------------------------------------------------- 1. To Backup All Databases in a instance    Exec sp_BackupDatabases @BackupDBType = 'ALL', @BackupPath = 'C:\Backup' 2. To Backup only System Databases in a instance    Exec sp_BackupDatabases @BackupDBType = 'System', @BackupPath = 'C:\Backup' 3. To Backup only User Databases in a instance    Exec sp_BackupDatabases @BackupDBType = 'User', @BackupPath = 'C:\Backup' 4. To Backup specific (One) Database(s) in a instance    Exec sp_BackupDatabases @BackupDBType = 'Specific', @DBName = 'AdventureWorks', @BackupPath = 'C:\Backup' 5. To Backup specific (more than One) Database(s) in a instance    Exec sp_BackupDatabases @BackupDBType = 'Specific', @DBName = 'AdventureWorks,master,msdb', @BackupPath = 'C:\Backup'

To Delete the old backups I use the below query.

DECLARE @TwoDaysOld VARCHAR(50) Set @TwoDaysOld=CAST(DATEADD(d, -2, GETDATE()) AS VARCHAR) Select @TwoDaysOld Exec master.dbo.xp_delete_file 0,N'C:\Backups\',N'bak',@TwoDaysOld

This will delete any .bak files in the given location which are older than 2 days.

So after creating the sp_BackupDatabases stored proc, if we want to schedule the backups using windows scheduler than we have create a batch file (.bat file) which will be called in the windows scheduler at the scheduled time.

sqlcmd -S(local)\SQLEXPRESS -E -Q"DECLARE @TwoDaysOld VARCHAR(50) Set @TwoDaysOld=CAST(DATEADD(d, -2, GETDATE()) AS VARCHAR) Exec master.dbo.sp_BackupDatabases @BackupDBType = 'User', @BackupPath = 'C:\Backup' Exec master.dbo.xp_delete_file 0,N'D:\Backups\',N'bak',@TwoDaysOld"

Scripts can be downloaded from the below locations

1. SansSQL_Automate Backup in Express Edition.sql
2. SansSQL_Delete_bakfilesOlderThan2Days.sql
3. SansSQL_sp_BackupDatabases_User.bat

T-SQL Query to find list of Instances Installed on a machine

Here is a T-SQL Query to find the list of instances Installed on a machine.

DECLARE @GetInstances TABLE ( Value nvarchar(100),  InstanceNames nvarchar(100),  Data nvarchar(100)) Insert into @GetInstances EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',   @value_name = 'InstalledInstances'   Select InstanceNames from @GetInstances

OR

Create Table #GetInstances ( Value nvarchar(100),  InstanceNames nvarchar(100),  Data nvarchar(100)) Insert into #GetInstances EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',   @value_name = 'InstalledInstances'   Select InstanceNames from #GetInstances drop table #GetInstances

Both the queries are almost similar, except for that first query uses a table variable and the second one uses temporary table.

T-SQL Query to find the date when was DBCC CHECKDB Last run

As a Database Administrator, we know the importance of DBCC CHECKDB and will run this command to check the logical and physical integrity of all objects in the specified database.
When DBCC CHECKDB is run on a database, it does the following actions

• Runs DBCC CHECKALLOC on the database
• Runs DBCC CHECKTABLE on every table and view in the database
• Runs DBCC CHECKCATALOG on the database
• Validates the contents of every indexed view in the database
• Validates Service Broker data in the database

Before you start the DBCC CHECKDB on a database you might want to know the date and time when this command was lust run by you or someone else from your team.
Prior to SQL Server 2005, this data was not getting logged in the system. But in SQL Server 2005 onwards this data is getting logged in the system and using the below script you can find the date and time when DBCC CHECKDB was last run on a database.

CREATE TABLE #DBInfo (        Id INT IDENTITY(1,1),        ParentObject VARCHAR(255),        [Object] VARCHAR(255),        Field VARCHAR(255),        [Value] VARCHAR(255) ) CREATE TABLE #Value( DatabaseName VARCHAR(255), LastDBCCCHeckDB_RunDate VARCHAR(255) ) EXECUTE SP_MSFOREACHDB'INSERT INTO #DBInfo Execute (''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS''); INSERT INTO #Value (DatabaseName) SELECT [Value] FROM #DBInfo WHERE Field IN (''dbi_dbname''); UPDATE #Value SET LastDBCCCHeckDB_RunDate=(SELECT TOP 1 [Value] FROM #DBInfo WHERE Field IN (''dbi_dbccLastKnownGood'')) where LastDBCCCHeckDB_RunDate is NULL; TRUNCATE TABLE #DBInfo'; SELECT * FROM #Value DROP TABLE #DBInfo DROP TABLE #Value

The Script can be downloaded from here.

ERROR - Cannot set a credential for principal 'sa'

You may sometimes receive an error which says "Cannot set a credential for principal 'sa'" while you are trying to alter the login 'sa' in SQL Server 2005 or SQL Server 2008.

This happens because, the SQL Server tries to drop any credential that is mapped to your login credentials while altering and because your credential cannot be set to "sa" login, you will receive this error.

FIX:
Fix for this error is very simple. Just make sure that the option "Map to Credential" is checked in the "General" tab of the Login Properties Page.

Reference: http://support.microsoft.com/kb/956177

SQL Server 2011 (Denali) - First Look

First look of SQL Server Denali after Installation.

Splash Screen

Login Window

SQL Server Management Studio