Backups: Doing it right so you can recover quickly!– Automating the backups
[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]
ASBEGIN
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' ;
ENDGOEXEC [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