Simple Talk is now part of the Redgate Community hub - find out why

Pop Rivett’s SQL Server FAQ No2: Pop Backs Up

Pop leaps into action to safeguard a young girls's data...

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
The Backup file formats are

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

SET nocount ON
–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)
       CREATE TABLE DatabaseBackup
               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

— Get all database names
       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 ,
SELECT #DBName.Name ,
       ‘Y’ ,
       ‘N’ ,
       ‘2 jan 1900’ , — default 2 days
       ‘1 jan 1900’
      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
       SELECT *
       FROM #DBName
       WHERE #DBName.Name = DatabaseBackup.Name


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

— loop through databases
       @RetentionPeriod datetime ,
       @LastBackupToKeep VARCHAR(8) ,
       @ID INT ,
       @MaxID INT
       (Name, RetentionPeriod)
       SELECT Name,
               CASE WHEN @Type = ‘Full’
                       THEN RetentionPeriodFull
                       ELSE RetentionPeriodLog
       FROM DatabaseBackup
       WHERE (@Type = ‘Full’ AND BackupFlagFull = ‘Y’)
       OR (@Type = ‘Log’ AND BackupFlagLog = ‘Y’)
       — get next database to backup
       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
               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
       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
               BACKUP LOG @Name
                       TO DISK = @sql

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!!

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.


Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.