Tiered SQL Server Backups Using Task Scheduler

Sometimes using the Task Scheduler to execute tiered (read: Grandfather Father Son) SQL Server backups to disk is preferred to creating a maintenance plan.  While maintenance plans are powerful and easy, they require Integration Services to be installed, and in the case of standalone SQL Server Express installations that can be troublesome.

I use the following script as the foundation for my Task Scheduler jobs.

DECLARE @backupType NVARCHAR(3) -- BAK, DIF, or TRN
DECLARE @deleteDays INT

SET @backupType = N'$(backupType)'
SET @deleteDays = $(deleteDays)
DECLARE @dateDeleteBefore SMALLDATETIME
SET @dateDeleteBefore = DATEADD(dd, 0-@deleteDays, CURRENT_TIMESTAMP)

DECLARE @strDate NVARCHAR(20)

SET @strDate = CONVERT(VARCHAR(20),CURRENT_TIMESTAMP,120)
SET @strDate = REPLACE(@strDate, N'-', N'')
SET @strDate = REPLACE(@strDate, N':', N'')
SET @strDate = REPLACE(@strdate, N' ', N'')

DECLARE @backupDir NVARCHAR(4000)

EXEC master.dbo.xp_instance_regread
  N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory',
  @backupDir OUTPUT,
  N'no_output'

DECLARE cursorDb CURSOR FOR
SELECT name
  FROM master.dbo.sysdatabases
  WHERE name NOT IN (N'tempdb')

DECLARE @name NVARCHAR(50)
DECLARE @path NVARCHAR(256)
DECLARE @model NVARCHAR(32)

OPEN cursorDb
FETCH NEXT FROM cursorDb INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @model = recovery_model_desc FROM master.sys.databases WHERE name = @name

  IF @backupType = N'BAK' OR (@backupType = N'DIF' and @name = N'master')
  BEGIN
    SET @path = @backupDir + N'\' + @name + N'_' + @strDate + N'.BAK'
    BACKUP DATABASE @name TO DISK = @path
  END
  ELSE IF @backupType = N'DIF'
  BEGIN
    SET @path = @backupDir + N'\' + @name + N'_' + @strDate + N'.DIF'
    BACKUP DATABASE @name TO DISK = @path WITH DIFFERENTIAL
  END
  ELSE IF @backupType = N'TRN' AND @model = N'FULL'
  BEGIN
    SET @path = @backupDir + N'\' + @name + N'_' + @strDate + N'.TRN'
    BACKUP LOG @name TO DISK = @path
  END
  FETCH NEXT FROM cursorDb INTO @name
END

CLOSE cursorDb
DEALLOCATE cursorDb
EXEC master.dbo.xp_delete_file 0, @backupDir, @backupType, @dateDeleteBefore, 0

GO

Call the script using SQLCMD like in the following examples and schedule as appropriate.
 
SQLCMD.EXE -S .\INSTANCE -i C:\yourpathtoscript\BACKUP.SQL -v BACKUPTYPE = BAK -v DELETEDAYS = 14
 
SQLCMD.EXE -S .\INSTANCE -i C:\yourpathtoscript\BACKUP.SQL -v BACKUPTYPE = DIF -v DELETEDAYS = 7
 
SQLCMD.EXE -S .\INSTANCE -i C:\yourpathtoscript\BACKUP.SQL -v BACKUPTYPE = TRN -v DELETEDAYS = 3

The BACKUPTYPE variable controls the type of SQL Server backup.  All databases (except TempDb) in the instance are included, and attempts to execute transaction log (TRN) backups against databases that are set to SIMPLE or BULK LOGGED are skipped.  Since the master database can not get a differential backup, using DIF produces a full backup instead with the BAK extension.  All backups are stored in the default backup directory for the SQL Server instance, as found in the registry and specified during installation of the instance.  The DELETEDAYS variables indicates how to purge old backups of the same type from the directory.

This script runs great under the SYSTEM context when run from the machine on which the instance is installed, which means you don't have to store passwords.