Backups: Doing it right so you can recover quickly!– Automating the backups

Backups: Doing it right so you can recover quickly!– Automating the backups

  • Comments 1
  • Likes

[Prior Post]    [Next Post]

Getting backups scheduled appropriately tends to be a slow process with a lot of keying and mouse clicks.  The process can be simplified by the use of a couple of scripts that will backup every database on your SQL Server (including databases added to your SQL Server later!)

There are three types of backups:

  • Full backup – complete copy of the database
  • Differential backup – the records that have changed since the last full backup 
    • Note: Differential is not just the records changed since the last diffferential
  • Transaction log backup – the records that have changed since the last log backup.
    • Does not apply to databases with a SIMPLE recovery model

The pattern that I coded below is to:

  • Do a full backup every weekend
  • Do a differential backup every night
  • Do a transaction log backup every hour during the working weekday.

If this pattern is not satisfactory for you needs, feel free to leave a comment on what you need and I will provide it in a follow up post.

I am providing you the option of doing two types of backups:

  • Rollover backup -- the data overwrites older data that has expired (21 days of data is kept).
  • Weekly-distinct backup – the backup is done to a different file every week and you have unlimited backups.

Roll-over backups allow the latest version to be recovered only, with the advantage that you reuse disk space. Weekly-distinct backups have the disadvantage of consuming more and more disk space, but they allow you to recover to any point in the past, as long as you have a backup for it. The table below illustrates the backup files you will see for database “ISV”.

Rollover Backup

Weekly Distinct Backup
ISV Backup.bak ISV 20110430.bak
  ISV 20110507.bak
  ISV 20110514.bak
  ISV 20110521.bak
  ISV 20110528.bak

The choice often depends on any potential need to rollback to a point of time. Typical needs driving weekly distinct backups are:

  • Detecting fraud by a sophisticated user.
  • Ability to recover lost data that was accidentally deleted but not discovered immediately.

The Scripts Summary

The scripts build on each other, so I will describe them from the bottom up.

  • [SQLISV_DoBackup] – backups one database as described above. You supply the name of the database.
  • [SQLISV_BackupAllDatabases] – backs up all of the databases on the SQL server.
  • [SQLISV_SchduleBackupAllDatabases] – adds the jobs to execute [SQLISV_BackupAllDatabases] regularly.

The scripts are adaptive. If you add a database, or the server is down when a backup should happen, the scripts adapt to these conditions.

SQLISV_DoBackup Script

This script takes four parameters that you may wish to change the default values for:

  • @DBName – the name of the database
  • @BackupPath – the physical path to the folder to use (with an ending \ )
  • @MinLogSize – sets how big the transaction log should be before a log backup occurs
  • @Overwrite – determines if a roll-over or weekly-distinct backup is done.
  • @CheckDb -- determines if the database integrity is checked before doing a backup
    • 2 is the recommended production setting (you want backups to be perfect)
    • 0 is the default until you have timed how long DBCC CHECKDB takes (if may take days)

There are many options available as shown in help. I tried to keep things simple but I decided to put compression on because it detects disk write errors.

CREATE PROC [dbo].[SQLISV_DoBackup] @DBName Sysname=null, @BackupPath nvarchar(255)='P:\Backups\', @MinLogSize int =1, @Overwrite bit = 1, @CheckDb tinyint = 0 -- 0: Nocheck -- 1:Check on FULL -- 2: Check on FULL and Differertial AS SET NOCOUNT ON DECLARE @BackupFileName nvarchar(max), @WeekDate Date, @Cmd Nvarchar(max), @WithOptions Nvarchar(max), @DayOffset int, @Hour int, @SimpleRecoverModel bit = 0, @FullBackupHour int=1, @FullBackupDayOffset int=1 IF @DBName is Null SET @DBName=DB_Name() IF @DBName in ('tempdb') BEGIN Print 'TempDb is not backed up' RETURN END IF (SELECT DATABASEPROPERTYEX(@DBName,'Recovery'))='SIMPLE' SET @SimpleRecoverModel =1 SET @Hour=DatePart(hh,GetDate()) SET @WeekDate=GetDate() SET @DayOffset=DatePart(dw,@WeekDate) SET @WeekDate=DateAdd(dy,-@DayOffset,@WeekDate) IF @Overwrite = 0 BEGIN SET @BackupFileName= @BackupPath + REPLACE(@@SERVERNAME,'\','-')+' ' + @DBName +' ' + Convert(varchar(8),@WeekDate, 112) +'.bak' SET @WithOptions ='COMPRESSION' END ELSE BEGIN SET @BackupFileName= @BackupPath + REPLACE(@@SERVERNAME,'\','-')+' ' + @DBName +' Backup' +'.bak' SET @WithOptions ='COMPRESSION, RETAINDAYS=21' END -- Borrowed from early post, we want to determine if log is above threshold IF NOT EXISTS(SELECT name from sysobjects where name='SQLISV_LogSpace' and type='P') BEGIN SET @CMD='CREATE Proc SQLISV_LogSpace AS DBCC SQLPERF(logspace)' print @Cmd EXEC (@Cmd) END CREATE TABLE #LogSpace ( DBName sysname, logSize decimal(18,5), logUsed decimal(18,5), status int ) INSERT INTO #LogSpace exec SQLISV_LogSpace -- Always do a full backup on a new file IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id AND is_copy_only = 0 -- Is NOT COPY ONLY WHERE database_name = @DBName AND [type]='D' -- Is FULL BACKUP AND @BackupFileName=physical_device_name -- to Right Location AND DateDiff(hh,backup_start_date,GetDate()) < 7 * 24 +1 -- Full backups must occur at least once every 7 days. ) OR -- At specified time and day of week (@FullBackupHour=@Hour AND @FullBackupDayOffset = @DayOffset) BEGIN IF @CheckDB > 0 BEGIN SET @CMD='DBCC CHECKDB (['+@DBName + ']) WITH NO_INFOMSGS' print @Cmd EXEC (@CMD) END SET @CMD='BACKUP DATABASE ['+@DBName + '] TO DISK = ''' + @BackupFileName+''' WITH '+@WithOptions print @Cmd EXEC (@CMD) IF @DBName in ('Master','model','msdb') OR @SimpleRecoverModel =1 -- Can't backup LOG on SIMPLE return SET @CMD='BACKUP LOG ['+@DBName + '] TO DISK = ''' + @BackupFileName+''' WITH '+@WithOptions Print @Cmd EXEC (@CMD) RETURN END IF @DBName in ('Master','model','msdb') BEGIN Print @DBName Print 'Master,model,msdb has been backedup this week' RETURN END -- DIFFERENTIAL IS DONE AT 1 AM (or next opportunity if machine is down at 1AM) IF not exists(SELECT 1 FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id AND is_copy_only = 0 WHERE [type]='I' -- Differential Backup AND database_name = @DBName AND @BackupFileName=physical_device_name -- No more than one every 12 hrs AND DateDiff(hh,backup_start_date,GetDate()) < 12 -- Should happen at 1AM but if machine offline, then next time AND @hour BETWEEN 1 AND 7 -- Between 1AM and 7AM ) BEGIN IF @CheckDB > 1 BEGIN SET @CMD='DBCC CHECKDB (['+@DBName + ']) WITH NO_INFOMSGS' print @Cmd EXEC (@CMD) END SET @CMD='BACKUP DATABASE ['+@DBName + '] TO DISK = ''' + @BackupFileName+''' WITH DIFFERENTIAL, ' +@WithOptions Print @Cmd EXEC (@CMD) RETURN END ELSE IF EXISTS(SELECT 1 FROM #LogSpace WHERE Cast(logSize* logUsed/100 as decimal(18,2)) > @MinLogSize AND DBName=@DBName) BEGIN IF not exists(SELECT 1 FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id AND is_copy_only = 0 WHERE [type]='L' AND DateDiff(mm,GetDate(),backup_start_date) > 50 AND database_name = @DBName AND @BackupFileName=physical_device_name) AND @SimpleRecoverModel =0 -- Can't backup LOG on SIMPLE BEGIN SET @CMD='BACKUP LOG ['+@DBName + '] TO DISK = ''' + @BackupFileName+''' WITH '+@WithOptions Print @Cmd EXEC (@CMD) RETURN END ELSE BEGIN Print 'Log is too small - delaying' RETURN END END Print 'Nothing happened' GO CREATE PROC [dbo].[SQLISV_BackupAllDatabases] @BackupPath nvarchar(255)='P:\Backups\', @MinLogSize int =1000 AS DECLARE @DBID int=1, @DBName Sysname DECLARE @DBTable table (DBID int identity(1,1),DBName sysname) INSERT INTO @DBTable (DBName) SELECT Name FROM sys.databases WHILE 1=1 BEGIN SELECT @DBName=DBName FROM @DBTable WHERE @DBID=DBID IF @@ROWCOUNT > 0 BEGIN TRY SET @DBID +=1 EXEC [SQLTools].dbo.[SQLISV_DoBackup] @DBName=@DBName, @BackupPath=@BackupPath, @MinLogSize=@MinLogSize END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH ELSE BREAK END GO

When this executes, I print out the backup TSQL commands executed to facilitate debugging.

BackupMessages

SQLISV_BackupAllDatabases Script

This script walks the databases and calls the above script for each one. I assumed @Overwrite is defaulted to the appropriate value above and retained only two of the parameters.

USE [SQLTools] GO CREATE PROC [dbo].[SQLISV_BackupAllDatabases] @BackupPath nvarchar(255)='P:\Backups\', @MinLogSize int =1000 AS DECLARE @DBID int=1, @DBName Sysname DECLARE @DBTable table (DBID int identity(1,1),DBName sysname) INSERT INTO @DBTable (DBName) SELECT Name FROM sys.databases WHILE 1=1 BEGIN SELECT @DBName=DBName FROM @DBTable WHERE @DBID=DBID IF @@ROWCOUNT > 0 BEGIN TRY SET @DBID +=1 EXEC [SQLTools].dbo.[SQLISV_DoBackup] @DBName=@DBName, @BackupPath=@BackupPath, @MinLogSize=@MinLogSize END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH ELSE BREAK END GO

I would suggest changing the Catch block to send a notification out, for example an email.

SQLISV_ScheduleBackupAllDatabases Script

The script below will add jobs to call the above script if they do not exist and replace jobs if they do exist.

USE [SQLTools] GO
CREATE
PROC [dbo].[SQLISV_ScheduleBackupAllDatabases] AS
BEGIN
IF Exists(SELECT 1 FROM msdb.dbo.sysjobs WHERE name='SQLISV_BackupAllDatabases') Exec msdb.dbo.sp_delete_job @job_name='SQLISV_BackupAllDatabases' Exec msdb.dbo.sp_add_job @job_name='SQLISV_BackupAllDatabases', @notify_level_eventlog=3 Exec msdb.dbo.sp_add_jobstep @job_name='SQLISV_BackupAllDatabases', @Step_Name='SQLISV_BackupAllDatabases_1', @Command='EXEC [SQLTools].[dbo].[SQLISV_BackupAllDatabases]' IF Exists(Select 1 from msdb.dbo.sysschedules WHERE name='SQLISV_BackupAllDatabases') EXEC msdb.dbo.sp_delete_schedule @schedule_name = 'SQLISV_BackupAllDatabases' Exec msdb.dbo.sp_add_schedule @schedule_name = 'SQLISV_BackupAllDatabases', @freq_type = 4, --Daily @freq_interval=1, @freq_subday_type=0x8, -- Hour @freq_subday_interval=1 EXEC msdb.dbo.sp_attach_schedule @job_name = N'SQLISV_BackupAllDatabases', @schedule_name = N'SQLISV_BackupAllDatabases' ;
END
GO
EXEC [dbo].[SQLISV_ScheduleBackupAllDatabases]

Did it work?

In the SQLISV_ScheduleBackupAllDatabases code above, I specified  @notify_level_eventlog=3. This setting means that successful and unsuccessful backups are recorded in the Event Log as shown below.

SQLAgentJobEventLog

Some of the backups failed, so I needed to investigate why. I can manually execute the backup and attempt to identify what the problem is. An example of this process is shown below.

SQLAgentJobEventLogRepro

The code above tests for this condition and omits the log backups for databases with a SIMPLE recovery model. This was a contrived failure. If you encounter any failures, please comment below.

Technical Issues

The following are some technical points that may be of some interest.

  • The code includes the Server Instance name in the backup name. This is needed to prevent namespace collision if there are two instances on the same machine, or if one backup location is used by several servers. Each instance would have the following standard databases: [msdb], [model] and [master]
    NameCollideBackups
  • I have kept the use of backup options lean so we can focus on the process. You may wish to review these options in help and modify the scripts to your needs.

Bottom Line

My experience is that getting backups configured is tedious work, prone to errors. This post shows you how you can automate the entire process by just downloading the TSQL script here and executing it once on each server. The backup pattern may not be perfect, but it is better than what I have often seen.  The scripts above should be robust, and best of all, if you add a database to SQL Server it will be included in the backups automatically.

SQLTools: Where to Save these Utilities?

I had a discussion with Shaun Tinline-Jones of the SQL Server Customer Advisory Team - SQL Server Best Practices team(SQLCAT) about where to locate this procedure and other like it. Should it be in [msdb], [model],[master], an existing ISV related database or a new database. Our conclusion was a new database named [SQLTools] was best (instead of [SQLISV] or [SQLCAT]).

To create this database



 

Create Database [SQLTools] Go
 
Comments
  • Some corrections:

    Diff backup is all data extents that have changed since last full backup - nothing to do with prior diff backups.

    Log backup is all log generated since last log backup - nothing to do with prior diff backups.

    [Ken: Many thanks Paul, I have corrected my parity error.  I would recommend readers to visit Paul's site, SQLSkills.com for more useful tips!]

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment