Backing up all databases on a server using a stored procedure

Pop: Well then, young lassie. Not leaving the party already, are we?

Q: Oh, hello Pop. I have to I’m afraid. Timmie Ponsonby asked me how I backed up the data in all the databases on my database server. Well, I didn’t know the answer, and now I’m not even sure that they get backed up at all! I felt so nervous I couldn’t enjoy myself any more. It seems so silly when we have a man who does our SysAdmin for us. Shouldn’t he do it?

Pop: Oh no, my dear, everyone who uses SQL Server should know how to back up their data, and should make sure that it is being done.

Q: I suppose you’re right…[sulking slightly]…so anyway, I’ve simply got to go home early and check that my data is safe.

Pop: Well, maybe I can help you out this time. I think I have a stored procedure somewhere that does it for you. It maintains a table called DatabaseBackup in the master database (or an admin database) that allows you to choose whether you want full backups, and log backups, for each database on the server. All you need to do is to put a task on the SQL Server Scheduler to run the stored procedure, to do the full backups, and run a second and more frequent task to do the log backups

Q: So I just run this stored procedure and everything will be OK?

Pop: Well, you’ll need to work out which databases require log backups, because, as you rightly know, the simple recovery model isn’t much use for a rapidly-changing database, and you wouldn’t be able to do log backups from databases using the simple recovery model. So all you need to do is to edit the table in the master database accordingly. It also detects when you add or delete a database and modifies the row in the table.

Q: Sounds great! Can I see it?

Pop: Let me see…it goes something like this. You’ll need to compile and run it in the same database that houses the DatabaseBackup table:

ALTER PROCEDURE s_BackupAllDatabases
@Path VARCHAR(128) ,
@Type VARCHAR(4)= ‘FULL’ — Full / Log
AS
/*
The Backup file formats are
       DatabaseName_Full_yyyymmdd_hhmmss.bak
       DatabaseName_Log_yyyymmdd_hhmmss.bak

Example:
exec s_BackupAllDatabases ‘G:\database’, ‘LOG’

*/
SET nocount ON
DECLARE @sql VARCHAR(1000)
–create the Database Backup table if it doesn’t exist
IF NOT EXISTS (SELECT * FROM dbo.sysobjects
       WHERE id = OBJECT_ID(N'[dbo].[DatabaseBackup]’)
       AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
       BEGIN
       CREATE TABLE DatabaseBackup
               (
               Name VARCHAR(128) PRIMARY KEY NONCLUSTERED ,
               BackupFlagFull VARCHAR(1) NOT NULL
                       CHECK (BackupFlagFull IN (‘Y’,’N’)) ,
               BackupFlagLog VARCHAR(1) NOT NULL
                       CHECK (BackupFlagLog IN (‘Y’,’N’)) ,
               RetentionPeriodFull datetime NOT NULL ,
               RetentionPeriodLog datetime NOT NULL
               )
       END

— Get all database names
CREATE TABLE #DBName
       (
       ID INT IDENTITY (1,1) ,
       Name VARCHAR(128) NOT NULL ,
       RetentionPeriod datetime NULL
       )

INSERT #DBName (Name)
       SELECT name FROM master..sysdatabases

— Include any new databases in the backup
INSERT DatabaseBackup
       (
       Name ,
       BackupFlagFull ,
       BackupFlagLog ,
       RetentionPeriodFull ,
       RetentionPeriodLog
       )
SELECT #DBName.Name ,
       ‘Y’ ,
       ‘N’ ,
       ‘2 jan 1900’ , — default 2 days
       ‘1 jan 1900’
FROM #DBName
      LEFT OUTER JOIN DatabaseBackup
               ON DatabaseBackup.Name = #DBName.Name
WHERE DatabaseBackup.Name IS NULL
AND LOWER(#DBName.Name) <> ‘tempdb’

— and Remove any non-existent databases
DELETE DatabaseBackup
WHERE NOT EXISTS
       (
       SELECT *
       FROM #DBName
       WHERE #DBName.Name = DatabaseBackup.Name
       )

DELETE #DBName

CREATE TABLE #ExistingBackups
       (
       Name VARCHAR(128) ,
       ID INT IDENTITY (1,1)
       )

— loop through databases
DECLARE @Name VARCHAR(128) ,
       @RetentionPeriod datetime ,
       @LastBackupToKeep VARCHAR(8) ,
       @ID INT ,
       @MaxID INT
      
INSERT #DBName
       (Name, RetentionPeriod)
       SELECT Name,
               CASE WHEN @Type = ‘Full’
                       THEN RetentionPeriodFull
                       ELSE RetentionPeriodLog
               END
       FROM DatabaseBackup
       WHERE (@Type = ‘Full’ AND BackupFlagFull = ‘Y’)
       OR (@Type = ‘Log’ AND BackupFlagLog = ‘Y’)
      
SELECT @MaxID = MAX(ID) ,@ID = 0 FROM #DBName
      
WHILE @ID < @MaxID
BEGIN
       — get next database to backup
       SELECT @ID = MIN(ID) FROM #DBName WHERE ID > @ID
      
       SELECT @Name = Name ,
               @RetentionPeriod = RetentionPeriod
       FROM #DBName
       WHERE ID = @ID
      
       — Delete old backups
       DELETE #ExistingBackups
       SELECT @sql = ‘dir /B ‘ + @Path
       SELECT @sql = @sql + ‘”‘ + @Name + ‘_’ + @Type + ‘*.*”‘
      
       INSERT #ExistingBackups EXEC master..xp_cmdshell @sql
      
       IF EXISTS (SELECT * FROM #ExistingBackups
               WHERE Name LIKE ‘%File Not Found%’)
               DELETE #ExistingBackups
      
       SELECT @LastBackupToKeep
               = CONVERT(VARCHAR(8),GETDATE() – @RetentionPeriod,112)
       DELETE #ExistingBackups
               WHERE Name > @Name + ‘_’ + @Type + ‘_’ + @LastBackupToKeep
      
       DECLARE @eID INT ,
               @eMaxID INT ,
               @eName VARCHAR(128)
      
       — loop round all the out of date backups
       SELECT  @eID = 0 ,
               @eMaxID = COALESCE(MAX(ID), 0)
       FROM    #ExistingBackups
      
       WHILE @eID < @eMaxID
      BEGIN
               SELECT @eID = MIN(ID) FROM #ExistingBackups
                                               WHERE ID > @eID
               SELECT @eName = Name FROM #ExistingBackups
                                               WHERE ID = @eID
              
               SELECT @sql = ‘del ‘ + @Path + ‘”‘ + @eName + ‘”‘
               EXEC master..xp_cmdshell @sql, no_output
       END
       DELETE #ExistingBackups
      
       – now do the backup
       SELECT @sql = @Path + @Name + ‘_’ + @Type + ‘_’
                       + CONVERT(VARCHAR(8),GETDATE(),112) + ‘_’
                       + REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),’:’,”)
                       + ‘.bak’
       IF @Type = ‘Full’
               BACKUP DATABASE @Name
                       TO DISK = @sql
       ELSE
               BACKUP LOG @Name
                       TO DISK = @sql
END

Q: Wow, Pop, it looks impressive but there’s a lot to take in…

Pop: Tell you what, I’ll pop it on your database right away, and here’s a copy of the script for you to study. Notice that the procedure deletes any backup files that are older than the retention period held in the Databasebackup table (the default is 2 days for full, 1 day for log). A common complaint about SQL server maintenance plans is that they stop deleting out of date backups. Also, keeping each backup in a different file means that hopefully you won’t lose them all if your backup device gets corrupted.

So, young lady, there you have it. Next time, you’ll be able to enjoy the party without worrying whether or not your data is safe and sound. Now get yourself back in there…I think they’re serving ice cream!

Q: Thanks, Pop!

Pop: [calling after her]…but don’t forget that a backup should only be relied upon after you’ve performed a successful test restore!!