Click here to monitor SSC
  • Av rating:
  • Total votes: 30
  • Total comments: 4
Pop Rivett

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

22 September 2006

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

Pop Rivett

Author profile:

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.

Search for other articles by Pop Rivett

Rate this article:   Avg rating: from a total of 30 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Pop Rivett teaches in the University of Life
Posted by: Anonymous (not signed in)
Posted on: Friday, September 22, 2006 at 11:57 AM
Message: Oooh! I love it. Now please Mr Rivett Sir, tell us how to do the test Restore, please please!

Subject: Thanks!
Posted by: Anonymous (not signed in)
Posted on: Thursday, September 28, 2006 at 9:38 AM
Message: 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)

Subject: Preserving line breaks
Posted by: Tony Davis (view profile)
Posted on: Thursday, September 28, 2006 at 11:58 AM
Message: 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.

Best,

Tony.

Subject: anonymous comments have been disabled
Posted by: Nadine (view profile)
Posted on: Wednesday, January 9, 2008 at 7:55 AM
Message: Due to a high volume of spam, anonymous comments have been disabled.

 
Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Documenting your SQL Server Database

One of the shocks that a developer can get when starting to program in T-SQL is that there is no simple way of... Read more...

 View the blog

Top Rated

Using the T-SQL PERCENTILE Analytic Functions in SQL Server 2000, 2005 and 2008
 Percentiles give meaning to measurements by telling you the percentage of the population being measured... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.