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:

  Cursors -  http://msdn.microsoft.com/en-us/library/ms180169(v=sql.110).aspx

  Backup -  http://msdn.microsoft.com/en-us/library/ms186865(v=sql.110).aspx