Backup is one off the most important things that need to be arranged after a SQL Server database is created, restored or attached.
The advantages of the T-SQL scripts presented here over a SSIS task:
1. Newly created db's are automatically included, no additional steps are required;
2. Databases that are in recovery mode 'Full' or 'Bulk Recovery' are automatically selected when transaction log backups are sheduled;
3. The checksum option that was introduced with SQL 2005 is included;
4. After a backup is completed a verify is executed;
5. Expired backups are automatically deleted;
6. Each set of dumps are stored in their own folder: less clutter
7. Databases can be set off-line without the job-step to fail
8. Databases in standby mode are skipped
The actual backup is made in the 'usp_MakeBackup' stored proc that is not called directly,
but is executed from the parent sp's: 'usp_MakeFullBackup', 'usp_MakeDifferentialBackup' and 'usp_MakeLogBackup' all scheduled from SQL Agent.
I usally make full backup around 2300hr and schedule log backups every 2hrs or so between 0800 until 2000hr.
The differential backup sp was only used in an OLTP config with a high volume of log backups (every 25 minutes), To avoid a very long list if individual log files we scheduled a differential backup every 4hrs. So the restore sequence was: full backup, last differential backup after the full backup and one or more transaction log backup that were made after the differential.
Code to initiate a full backup (store in CodeCatalog)
-- =========================================================================
-- Author: SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a full backup
-- The actual backup is made in sp 'usp_MakeBackup'
-- ========================================================================
CREATE PROCEDURE [dbo].[usp_MakeFullBackup]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DbName NVARCHAR(100)
DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('tempdb','AdventureWorks') -- skip the unwanted DBs
AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
ORDER BY name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC usp_MakeBackup @dbname, 'F' -- backup db here..
FETCH NEXT FROM dbnames_cursor INTO @DbName
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
END
Code to initiate a differential backup (store in CodeCatalog)
-- ======================================================================
-- Author: SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a differential backup
-- The actual backup is made in sp 'usp_MakeBackup'
-- ======================================================================
CREATE PROCEDURE [dbo].[usp_MakeDifferentialBackup]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DbName NVARCHAR(100)
--get all the on-line databases
DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sys.databases-- skip unwanted DBs
WHERE name NOT IN ('master','model','tempdb','AdventureWorks')
AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
ORDER BY name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC usp_MakeBackup @dbname, 'D' -- backup db here..
FETCH NEXT FROM dbnames_cursor INTO @DbName
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
END
Code to initiate a transaction log backup (store in CodeCatalog)
-- ==============================================================
-- Author: SQL Server Advisor
-- Create date: jan/2009
-- Description: Selects candidates for a log backup
-- The actual backup is made in sp 'usp_MakeBackup'
-- ==============================================================
CREATE PROCEDURE [dbo].[usp_MakeLogBackup]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DbName NVARCHAR(100)
--get all the on-line databases
DECLARE dbnames_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','model','tempdb','AdventureWorks')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0
AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
ORDER BY name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC usp_MakeBackup @dbname, 'L' -- backup log here..
FETCH NEXT FROM dbnames_cursor INTO @DbName
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
END
The actual backup to disk is made here (to store in CodeCatalog). Looks bit odd, still fighting the code editors:
-- ================================================================================
-- Author: SQL Server advisor
-- Create date: jan/2009
-- Description: makes full or logbackup based on passed parameter @BackupType
-- databases are selected in 'usp_MakeFullBackup',
-- 'usp_MakeLogBackup' and usp_MakeDifferentialBackup
--=================================================================================
--called from usp_MakeFullBackup, usp_MakeLogBackup or usp_MakeDifferentialBackup
CREATE PROCEDURE [dbo].[usp_MakeBackup] @DbName NVARCHAR(100), -- Database name
@BackupType CHAR(1)
-- 'F' = full backup, 'L' = log backup, 'D' = differential backup
AS
BEGIN
DECLARE @FileName NVARCHAR(300), @Directory NVARCHAR(256)
DECLARE @BackupRetention SMALLINT -- backup retention level, days
DECLARE @DateToDelete DATETIME,@DeleteDateStr NVARCHAR(30),@FileExtension NCHAR(10)
SET @Directory = 'D:\backup\' + @DbName -- set the backup path here
SET @BackupRetention = 4 -- set retention –in days- of backup files here
EXECUTE master.dbo.xp_create_subdir @Directory
SET @FileName=@Directory + '\' + @DbName+'.'+Convert(nvarchar(10),GetDate(),104) + '_' + LTrim(Str(DatePart(hour,GetDate())))+LTrim(Str(DatePart(minute,GetDate())))
SET @DateToDelete=DateAdd(day,-ABS(@BackupRetention),GetDate())
SET @DeleteDateStr=Convert(nvarchar(30),@DateToDelete,1) + ' 00:00'
IF @BackupType = 'F' -- make a full backup
BEGIN
SET @FileExtension = 'BAK'
SET @FileName = @FileName + '.' + @FileExtension
BACKUP DATABASE @DbName TO DISK = @FileName WITH CHECKSUM, INIT, STOP_ON_ERROR
END
IF @BackupType = 'D' -- make a differential backup
BEGIN
SET @FileExtension = 'DIF'
SET @FileName = @FileName + '.' + @FileExtension
BACKUP DATABASE @DbName TO DISK = @FileName WITH DIFFERENTIAL,
CHECKSUM, INIT, STOP_ON_ERROR
END
IF @BackupType = 'L' -- make a log backup
BEGIN
SET @FileExtension = 'TRN'
SET @FileName = @FileName + '.' + @FileExtension
BACKUP LOG @DbName TO DISK = @FileName WITH CHECKSUM, INIT, STOP_ON_ERROR
END
-- check if backup was successfull
RESTORE VERIFYONLY FROM DISK = @FileName WITH CHECKSUM, STOP_ON_ERROR
-- remove the backup files older then @BackupRetention days
EXECUTE master.dbo.xp_delete_file 0,@Directory,@FileExtension,@DeleteDateStr,1;
END
