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
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)
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
-- 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
SELECT #DBName.Name ,
'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
WHERE NOT EXISTS
WHERE #DBName.Name = DatabaseBackup.Name
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 ,
CASE WHEN @Type = 'Full'
WHERE (@Type = 'Full' AND BackupFlagFull = 'Y')
OR (@Type = 'Log' AND BackupFlagLog = 'Y')
SELECT @MaxID = MAX(ID) ,@ID = 0 FROM #DBName
WHILE @ID < @MaxID
-- get next database to backup
SELECT @ID = MIN(ID) FROM #DBName WHERE ID > @ID
SELECT @Name = Name ,
@RetentionPeriod = RetentionPeriod
WHERE ID = @ID
-- Delete old backups
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%')
= CONVERT(VARCHAR(8),GETDATE() - @RetentionPeriod,112)
WHERE Name > @Name + '_' + @Type + '_' + @LastBackupToKeep
DECLARE @eID INT ,
@eMaxID INT ,
-- loop round all the out of date backups
SELECT @eID = 0 ,
@eMaxID = COALESCE(MAX(ID), 0)
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
-- now do the backup
SELECT @sql = @Path + @Name + '_' + @Type + '_'
+ CONVERT(VARCHAR(8),GETDATE(),112) + '_'
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!!