Backing up all databases on SQL Server

I was reading a blog post by Paresh over on LinkedIn (see his post here) and his recommendation for automagically backing up all databases within a SQL Server instance.  It inspired me to share my backup stored procedure.

What does mine do that Paresh (and many others) do not do?  Here’s what I generally do with backups:

  • I want one .BAK file per database per day.
  • I want the first backup to be a full backup, then all subsequent backups to be either Log backups or Differential backups.
  • I want the first full backup to be configurable.  There are many overnight processes businesses run and I want the flexibility to either have backups run before or after that overnight processing.
  • I want the .BAK file to have the following name:
  • [server_name]_[instance_name]_[database_name]_[yyyymmdd].bak

Why put all backups into a single file?  Backing up data isn’t the point:  it’s making the data restorable that’s really important.  If I have a bunch of .BAK and .TRN files scattered around, I have to find all of them, then restore them in order.  By putting all of the backups into a single file, should I need to restore the data, all the backups are in a single place… and very easy to train even the greenest of DBAs to do a restore.  It also makes replicating the backups to other storage (like offsite backup) much easier.

So what does my script look like?  Here you go:

USE master
DROP PROCEDURE dbo.usp_BackupAllDatabases
CREATE PROCEDURE dbo.usp_BackupAllDatabases
     @start_of_cycle int = 0,
     @backup_path nvarchar(max),
     @status_messages nvarchar(max) = N'' OUTPUT
     Intended use:
     DECLARE @status_messages nvarchar(max) = N''

     EXEC master.dbo.usp_BackupAllDatabases
          @backup_path = 'G:\Backup\',
          @status_messages = @status_messages OUTPUT

     PRINT @status_messages
     Returns 0 if successful without errors
     Returns 1 if unsuccessful with errors
     DECLARE @start_of_cycle_flag bit = 0
     DECLARE @sqlcmd nvarchar(max)
     DECLARE @databases TABLE
               id int,
               name sysname,
               recovery_model int,
               backup_file_name nvarchar(max)
     DECLARE @current_id int
     DECLARE @current_db sysname
     DECLARE @current_recovery_model int
     DECLARE @current_backup_file_name nvarchar(max)
     DECLARE @stop bit = 0
     DECLARE @full_backup_exists bit = 0
     DECLARE @backup_header TABLE
               BackupName    nvarchar(128),
               BackupDescription  nvarchar(255),
               BackupType    smallint,
               ExpirationDate   datetime,
               Compressed    tinyint,
               Position    smallint,
               DeviceType    tinyint,
               UserName    nvarchar(128),
               ServerName    nvarchar(128),
               DatabaseName   nvarchar(128),
               DatabaseVersion   int,
               DatabaseCreationDate datetime,
               BackupSize    numeric(20, 0),
               FirstLSN    numeric(25, 0),
               LastLSN     numeric(25, 0),
               CheckpointLSN   numeric(25, 0),
               DatabaseBackupLSN  numeric(25, 0),
               BackupStartDate   datetime,
               BackupFinishDate  datetime,
               SortOrder    smallint,
               CodePage    smallint,
               UnicodeLocaleId   int,
               UnicodeComparisonStyle int,
               CompatibilityLevel  tinyint,
               SoftwareVendorId  int,
               SoftwareVersionMajor int,
               SoftwareVersionMinor int,
               SoftwareVersionBuild int,
               MachineName    nvarchar(128),
               Flags     int,
               BindingID    uniqueidentifier,
               RecoveryForkID   uniqueidentifier,
               Collation    nvarchar(128),
               FamilyGUID    uniqueidentifier,
               HasBulkLoggedData  bit,
               IsSnapshot    bit,
               IsReadOnly    bit,
               IsSingleUser   bit,
               HasBackupChecksums  bit,
               IsDamaged    bit,
               BeginsLogChain   bit,
               HasIncompleteMetaData bit,
               IsForceOffline   bit,
               IsCopyOnly    bit,
               FirstRecoveryForkID  uniqueidentifier,
               ForkPointLSN   numeric(25,0),
               RecoveryModel   nvarchar(60),
               DifferentialBaseLSN  numeric(25,0),
               DifferentialBaseGUID uniqueidentifier,
               BackupTypeDescription nvarchar(60),
               BackupSetGUID   uniqueidentifier,
               CompressedBackupSize bigint
     DECLARE @backup_name nvarchar(max) = N''
     DECLARE @backup_desc nvarchar(max) = N''
          online databases only
          recovery model = 1 = full recovery    (full backup, differential backup and transaction log backup available)
          recovery model = 3 = simply recovery model  (full backup, differential backup available)
     INSERT INTO @databases (id, name, recovery_model, backup_file_name)
               + N'[' + CAST(SERVERPROPERTY('MachineName') as nvarchar(max)) + N']_'                        -- server name
               + N'[' + CAST(ISNULL(SERVERPROPERTY('InstanceName'), 'DEFAULT') as nvarchar(max)) + N']_'    -- instance name
               + N'[' + CAST( as nvarchar(max)) + N']_'
               + CAST(DATEPART(year, GETDATE()) as nvarchar(max))
               + RIGHT(N'0' + CAST(DATEPART(month, GETDATE()) as nvarchar(max)), 2)
                    WHEN DATEPART(hour, GETDATE()) < @start_of_cycle
                    THEN RIGHT(N'0' + CAST(DATEPART(day, GETDATE() - 1) as nvarchar(max)), 2)
                    ELSE RIGHT(N'0' + CAST(DATEPART(day, GETDATE()) as nvarchar(max)), 2)
               + N'].bak'
     FROM master.sys.databases dbs
          dbs.state = 0    AND  -- online databases only
 NOT IN ('tempdb')   -- can't backup tempdb

     IF DATEPART(hour, GETDATE()) = @start_of_cycle
               SET @start_of_cycle_flag = 1

     SELECT @current_id = MIN( FROM @databases dbs
     WHILE @current_id IS NOT NULL AND @stop <> 1
                    @current_recovery_model = dbs.recovery_model,
                    @current_db =,
                    @current_backup_file_name = dbs.backup_file_name
                    @databases dbs
           = @current_id

               IF @start_of_cycle_flag = 1 OR @current_db = 'master'
                    BEGIN -- start the cycle with a full backup.  Master database can only have a full backup.
                         SET @sqlcmd = N'BACKUP DATABASE [' + @current_db + N'] TO DISK = ''' + @current_backup_file_name + N''' WITH NAME = ''FULL BACKUP [' + @current_db + ']'''
                         -- check that the backup file exists and it contains a full backup
                         DELETE FROM @backup_header

                         SET @sqlcmd = N'RESTORE HEADERONLY FROM DISK=''' + @current_backup_file_name + ''''

                         BEGIN TRY
                              INSERT INTO @backup_header
                              EXEC (@sqlcmd)
                         END TRY
                         BEGIN CATCH
                              -- generally means the backup file doesn't exist, just move on.
                              DELETE FROM @backup_header
                              SET @status_messages += 'Error reading backup file ' + @current_backup_file_name + CHAR(13) + CHAR(10)
                                   + CHAR(9) + 'It is likely the file does not exist.  File will be created with a full backup' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
                         END CATCH

                         IF EXISTS (SELECT * FROM @backup_header bh WHERE bh.DatabaseName = @current_db AND bh.BackupType = 1)
                                   -- if the database is FULL or BULK LOGGED recovery, take a transaction log backup
                                   -- if the database is SIMPLE recovery, take a differential backup
                                   IF @current_recovery_model IN (1, 2) SET @sqlcmd = N'BACKUP LOG [' + @current_db + N'] TO DISK = ''' + @current_backup_file_name + N''' WITH NAME = ''LOG BACKUP [' + @current_db + ']'''

                                   IF @current_recovery_model = 3 SET @sqlcmd = N'BACKUP DATABASE [' + @current_db + N'] TO DISK = ''' + @current_backup_file_name + N''' WITH DIFFERENTIAL, NAME = ''DIFF BACKUP [' + @current_db + ']'''
                                   -- A full backup doesn't exist, take a full backup
                                   SET @sqlcmd = N'BACKUP DATABASE [' + @current_db + N'] TO DISK = ''' + @current_backup_file_name + N''' WITH NAME = ''FULL BACKUP [' + @current_db + ']'''

               BEGIN TRY
                    EXEC (@sqlcmd)
                    SET @status_messages += N'Successfully backed up database [' + @current_db + ']' + CHAR(13) + CHAR(10)
               END TRY
               BEGIN CATCH
                    SET @status_messages += N'Error Backing Up Database [' + @current_db + N']' + CHAR(13) + CHAR(10)
                         + CHAR(9) + 'This will generally return 3013, check the SQL Server Error Log or Windows Application Event Log for details' + CHAR(13) + CHAR(10)
                         + CHAR(9) + N'Error Number: ' + CAST(ERROR_NUMBER() as nvarchar(max)) + CHAR(13) + CHAR(10)
                         + CHAR(9) + N'Error Message: ' + CAST(ERROR_MESSAGE() as nvarchar(max)) + CHAR(13) + CHAR(10)
                         + CHAR(9) + N'Backup Command: ' + @sqlcmd + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
                    SET @stop = 1
               END CATCH

               SET @sqlcmd = N''

               SELECT @current_id = MIN( FROM @databases dbs WHERE > @current_id
     RETURN @stop

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: