Backing up lots of SQL databases

Sometimes you might need to back up databases in a SQL instance, perhaps to keep a checkpoint or to restore them to another SQL server or instance. Its easy to do this manually if you only have a couple of databases in the instance, but what if you have lots of them? That would be time consuming and tedious in the extreme… That’s where this handy little SQL script will come in:

BEGIN
SET NOCOUNT ON;

DECLARE @name VARCHAR(50) -- Holds the database name
DECLARE @path VARCHAR(256) -- Holds the path for the backup
DECLARE @fileName VARCHAR(256) -- Filename for the backup
DECLARE @fileDate VARCHAR(20) -- Used to date stamp the filename
SET @path = 'D:\Backups\' -- Change this to your backup location
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
DECLARE db_cursor CURSOR FOR
  SELECT name
  FROM MASTER.dbo.sysdatabases
  WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer$INSTANCE','ReportServer$INSTANCETempDB') -- Modify to exclude databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
  BACKUP DATABASE @name TO DISK = @fileName
  FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END

You’ll need to change the list of excluded databases so it will skip ones that you don’t want to back up, and change the names of the ReportServer databases to reflect the correct name based on the name of your SQL instance.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email
Bookmark the permalink. Follow any comments here with the RSS feed for this post.
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.