Backup script for SQL Server databases

• Author
• Recent Posts

Riccardo Bicelli

Riccardo is a system administrator and software developer for small and mid-size businesses in Italy.

Latest posts by Riccardo Bicelli (see all)

• Tactical RMM: Open Source remote monitoring and management for Windows - Thu, Jan 27 2022
• Use OpenSSL-based software XCA as offline root certificate authority for AD Certificate Services - Wed, Jan 27 2021
• Deploy software with WPKG and Active Directory - Tue, Dec 8 2020

The backup strategy

Let's have two sets for backup, SET-O and SET-E, used respectively in Odd Weeks and in Even Weeks. Every Sunday, the corresponding set will be overwritten by the full backup process. Every day, a differential backup process takes place.

For databases configured for the Full Recovery model, a transaction log backup process takes place every hour. Backup files are stored on a network share. For each database, let's have two backup files per set, one for databases and one for transaction logs, if the database is configured for the full recovery model.

The backup scripts

Configuration

To accomplish our task, we're using three scripts (or two, if you're running all your databases in the simple recovery model). The scripts have a common configuration section that has to be consistent.

We must set the backup path, folder names for odd and even weeks, and a list of excluded databases. Find this block and change the variable definitions relative to paths according to your setup.

-- SET PARAMETERS HERE
SET @BackupPath = '\\YOURSERVER\YOURSHARE\YOURFOLDER\'
@OddSetName = 'SET-O'
@EvenSetname = 'SET-E'
-- END SET PARAMS

Then adjust the databases excluded from backup:
-- Open a cursor and change the list of excluded databases as needed.
DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServerTempDB') 
AND recovery_model <> 3

Do this for every script you are using.

Next, you must create the target folders.

Assuming that backups will be stored under UNC path \\YOURSERVER\YOURSHARE\YOURFOLDER\, you'll have to create SET-ODD and SET-EVEN folders on that path prior to making the backups, since the scripts don't include logic for interacting with the file system.

Full backup script

-- SQL SERVER USER'S DATABASE BACKUP WITH ODD/EVEN WEEK ROTATION
-- Full Backup

DECLARE

	@EvenOdd INT,
	@BackupSet VARCHAR(20),
	@BackupFile VARCHAR(128),
	@MediaName VARCHAR(128),
	@BackupName VARCHAR(128),
	@DBName VARCHAR(128),
	@BackupPath VARCHAR(256),
	@OddSetName VARCHAR(32),
      @EvenSetName VARCHAR(32)
    
-- SET PARAMETERS HERE
SET @filePath = '\\YOURSERVER\YOURSHARE\YOURFOLDER\'
SET @OddSetName = 'SET-ODD'
**SET @EvenSetname = 'SET-EVEN'** 
-- END PARAMS

DECLARE @ErrExec TINYINT
SET @ErrExec = 0

-- Check if current week is odd or even
SET @EvenOdd=datepart(wk, CAST(GETDATE() AS DATE)) % 2 
IF @EvenOdd = 0 SET @BackupSet=@EvenSetName ELSE SET @BackupSet=@OddSetName

-- Exclude system databases from backup
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN (**'master','model','msdb','tempdb','ReportServerTempDB'**) 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @DBName   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @BackupFile = @BackupPath + @BackupSet + '\' +  @DBName + '.bak'
       SET @MediaName = @DBName + '_FullBackup_' + @BackupSet
       SET @BackupName = N'Backup Database ' + @DBName + ' (' + @BackupSet + ')'
       
       -- Encapsulate the backup block in a try/catch statement, so a single failed backup
       -- doesn't stop the entire job
       BEGIN TRY
	      BACKUP DATABASE @DBName
	      TO DISK = @BackupFile
           WITH INIT, FORMAT,
           MEDIANAME = @MediaName,
           NAME = @BackupName
       END TRY
       BEGIN CATCH
            -- Can't backup database, log error to eventviewer
            DECLARE @ErrorDescr nvarchar(128)
            SET @ErrorDescr = N'Error During Full Backup of Database ' + @DBName
            EXEC xp_logevent 50001, @ErrorDescr, warning;
            SET @ErrExec = 1
       END CATCH

       FETCH NEXT FROM db_cursor INTO @DBName   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

-- If any errors are encountered, exit step with an error.
IF @ErrExec > 0
BEGIN
	RAISERROR('Full Backup threw an exception', 20, 1) WITH LOG;
END

GO

Differential backup

This is the differential backup script:

-- SQL SERVER USER'S DATABASE BACKUP WITH ODD/EVEN WEEK ROTATION
-- Differential Backup


DECLARE 
	@EvenOdd INT,
	@BackupSet VARCHAR(20),
	@BackupFile VARCHAR(128),
	@MediaName VARCHAR(128),
	@BackupName VARCHAR(128),
	@DBName VARCHAR(128),
	@BackupPath VARCHAR(256),
	@OddSetName VARCHAR(32),
      @EvenSetName VARCHAR(32)
    
-- SET PARAMETERS HERE
SET @filePath = '\\YOURSERVER\YOURSHARE\YOURFOLDER\'
SET @OddSetName = 'SET-ODD'
SET @EvenSetname = 'SET-EVEN'  
-- END PARAMS

DECLARE @ErrExec TINYINT
SET @ErrExec = 0

-- Check if current week is odd or even
SET @EvenOdd=datepart(wk, CAST(GETDATE() AS DATE)) % 2 
IF @EvenOdd = 0 SET @BackupSet=@EvenSetName ELSE SET @BackupSet=@OddSetName

-- Exclude system databases from backup
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServerTempDB') 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @DBName   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @BackupFile = @BackupPath + @BackupSet + '\' +  @DBName + '.bak'
       SET @MediaName = @DBName + '_FullBackup_' + @BackupSet
       SET @BackupName = N'Backup Database ' + @DBName + ' (' + @BackupSet + ')'
       
       -- Encapsulate the backup block in a try/catch statement, so a single failed backup
       -- doesn't stop the entire job
       BEGIN TRY
	     BACKUP DATABASE @DBName
	     TO DISK = @BackupFile
         WITH
         DIFFERENTIAL,
         NAME = @BackupName
       END TRY
       BEGIN CATCH
            -- Can't backup database, log error to eventviewer
            DECLARE @ErrorDescr nvarchar(128)
            SET @ErrorDescr = N'Error During Differential Backup of Database ' + @DBName
            EXEC xp_logevent 50001, @ErrorDescr, warning;
            SET @ErrExec = 1
       END CATCH

       FETCH NEXT FROM db_cursor INTO @DBName   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

-- If any errors are encountered, exit step with an error.
IF @ErrExec > 0
BEGIN
	RAISERROR('Differential Backup threw an exception', 20, 1) WITH LOG;
END

GO

Transactions log backup

For databases configured for the full recovery model, we have to make the transaction log backup to keep it small.

-- SQL SERVER BACKUP LOG ODD/EVEN WEEK ROTATION
-- Transactions Log Backup

DECLARE 
      @EvenOdd INT,
      @BackupSet VARCHAR(20),
      @BackupFile VARCHAR(128),
      @BackupName VARCHAR(128),
      @DBName VARCHAR(128),
      @ErrExec TINYINT,
      @filePath VARCHAR(256),
      @OddSetname VARCHAR(32),
      @EvenSetName VARCHAR(32)
    
-- SET PARAMETERS HERE
SET @filePath = '\\YOURSERVER\YOURSHARE\YOURFOLDER\'
@OddSetName = 'SET-O'
@EvenSetname = 'SET-E'
-- SET PARAMS

SET @ErrExec = 0
SET @EvenOdd=datepart(wk, CAST(GETDATE() AS DATE)) % 2 
IF @EvenOdd = 0 SET @BackupSet=@EvenSetname ELSE SET @BackupSet=@OddSetName

DECLARE db_cursor CURSOR FOR  
SELECT name
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServerTempDB') 

AND recovery_model <> 3 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @DBName   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       
       --Transaction Log Backup
       SET @BackupFile = @filePath + '\' + @BackupSet + '\' +  @DBName + '.TRN'
       SET @BackupName = N'Backup Transaction Logs of ' + @DBName + ' (' + @BackupSet + ')'

       -- To prevent exiting the whole step if only one backup fails, let's use
       -- this try/catch statement
       BEGIN TRY
           BACKUP LOG @DBName
           TO DISK = @BackupFile WITH NOFORMAT, NOINIT,  
           NAME = @BackupName,
           DESCRIPTION = 'Transaction Log Backup'
       END TRY
       BEGIN CATCH  
          -- If an error is caught, set @ErrExec Variable to a value greater than zero      
          DECLARE @ErrorDescr nvarchar(128)
          SET @ErrorDescr = N'Error During Transaction Log Backup of Database ' + @DBName           
          EXEC xp_logevent 50001, @ErrorDescr, warning;   
          SET @ErrExec = 1
       END CATCH
       FETCH NEXT FROM db_cursor INTO @DBName   

END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

-- If any errors are encountered, exit step with error.
IF @ErrExec > 0
BEGIN
	RAISERROR('Backup Transaction Log threw an exception', 20, 1) WITH LOG;
END

GO

Scheduling the tasks

Depending on which version of SQL Server you are using, there are two methods of scheduling:

1. Via the Windows Task Manager: The script is scheduled as a command. In order for it to work, you have to copy the SQL script files to the folder of your choice (e.g., C:\etc\sqlbackup), and then call the scripts in the schedule via sqlcmd:

sqlcmd -i C:\etc\sqlbackup\Fullbackup.sql

You can also specify the SQL Server user if needed:

sqlcmd -U sqladmin -P secretpassword -i C:\etc\sqlbackup\Fullbackup.sql

1. Via the SQL Server Agent's Job Scheduler: The script is scheduled as a T-SQL script. Simply copy the scripts in the "command" field of

Simply copy:paste the script's contents in the command text area

In both solutions, you must:

• Set up a full backup on the first day of the week.
• Set up a differential backup other days of the week, at least one per day.
• If you have databases in the full recovery model, set up a daily scheduled transaction log backup. Depending on how your databases are hammered, you may consider scheduling an hourly backup for transactions logs.

Troubleshooting

If the backup process fails, it will record events in the application log and terminate the step, raising an error.

Differential schedule every day except the first day of week

Remember that either the assigned system user in the scheduled task or the SQL agent user needs write permissions to back up target folders.

Final thoughts and further improvements

Now we have a reliable tool for backing up our SQL databases.

Since the scripts have a lot of code in common, it is also advisable to turn the common parts into a stored procedure and put some parameters in tables, such as databases excluded from backup. I may explain this in a future tutorial.

You can download the updated version of the backup scripts from my github repo.