22 September 2006

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

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


Tags: , , , ,


  • Rate
    [Total: 30    Average: 4.6/5]

Pop spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server since the old days of v4.2 to v2005. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

View all articles by Pop Rivett

  • Anonymous

    Pop Rivett teaches in the University of Life
    Oooh! I love it. Now please Mr Rivett Sir, tell us how to do the test Restore, please please!

  • Anonymous

    I’m going to try these scripts out. (One side note, to get the SQL to copy to SSMS, I had to copy from MSIE to VS2005 to SSMS to preserve the line breaks)

  • Tony Davis

    Preserving line breaks
    Hi Anon,

    Were you copying and pasting directly from the article? Or were you having this trouble with the source code file (obtained from the “Code” link in the bubble to the right of the article title)?

    I was able to open the source code file directly in SSMS with all line breaks in-tact, so I’m assuming you tried the former.



  • Nadine

    anonymous comments have been disabled
    Due to a high volume of spam, anonymous comments have been disabled.