Home » Database, Windows

Backup All SQL Server Databases

12 February 2011 No Comment
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Backup All SQL Server DatabasesIf you do Microsoft SQL Server Administration, you know how easy it is to back up your databases using the Enterprise Manager. Using the GUI comes with a price though. You sacrifice the ability to script what you are doing to other servers. What if you have other servers you want to backup all your SQL Server databases? How would you do it in the most effective way?

The solution is to do it in T-SQL. Copy the script below into the Query Analyzer and then execute. It will backup your databases into the C:\Backup folder.

Each database will have the file format {NAME}_{DATE}.BAK. The databases called master, model, msdb, and tempdb will not be backed up. If you wish to backup these databases, remove one or more of the database names from the WHERE name NOT IN statement below.

DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ”C:\Backup\”

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (”master”,”model”,”msdb”,”tempdb”)

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

You can schedule this script to run nightly by using osql from the Command Line and schedule it as a Scheduled Task. This way, you can bypass the GUI completely.

Incoming search terms:

  • sqlcmd backup ALL DATABASES
  • osql backup all databases

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.