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