Backups: Doing it right so you can recover quickly!– Automating the backups - Enterprise Applications on Microsoft SQL Server - Site Home

[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.

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.

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.

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]
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