Wednesday, July 30, 2014

Script to backup all SQL Server databases

This post covers two topics --  First, it is a very simple method to backup all of the user databases. Secondly, it is an example of a cursor.  I typically stay away from cursors within any real data manipulations, but I don't mind using them for simple functions such as this. The tSQL is a very simple BACKUP command, and the cursor just allows us to walk through all of the databases with that command, very quickly and very efficiently.  

/*
Use cursor to backup all user databases.  */

DECLARE 
@DBName VARCHAR(25),   -- database name  
@BkupPath VARCHAR(255),   -- the path for backup files  
@BkupFileName VARCHAR(255),   -- the backup filename
@FileDate VARCHAR(10)   -- to put date into filename
 
-- Where is the backup directory?
SET @BkupPath = 'C:\MSSQL\Backup\'  
 
-- Construct the BkupFileName
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112
 
-- Declare your cursor
DECLARE DBCursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude the system databases
 
-- Open your cursor
OPEN DBCursor   
FETCH NEXT FROM DBCursor INTO @DBName   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
  SET @BkupFileName = @BkupPath + @DBName + '_' + @FileDate + '.BAK'  
  BACKUP DATABASE @DBName TO DISK = @BkupFileName  
 
  FETCH NEXT FROM DBCursor INTO @DBName   
END   
 
-- Close your cursor
CLOSE DBCursor   
DEALLOCATE DBCursor

Note, you can revise the resulting .bak filename, if you'd like something different than 'DatabaseName_YYYYMMDD.bak'.  Also note the WHERE clause on the cursor declaration. You could adjust this to IN or NOT IN, and customize the database list to whatever databases you'd like to target.  This is the clause that I used in my test:     WHERE name IN ('master','model','msdb')

And this is my output:


Take a look at that run time.  All three sys dbs backed up to in 5 seconds.  Not bad.

That's pretty much it.  Like I said, you should modify that WHERE clause to suit your needs.  This is a very flexible, easily customized routine for backing up multiple databases via cursor. 

See here for more information on cursors and the BACKUP command:



No comments:

Post a Comment