SQL Server - T-SQL Script to Backup All Databases

Submitted by Mike Rodey on March 28, 2012 - 10:27pm

The SQL Server Agent and database maintenance plan wizards are unavailable in SQL Server Express, so a quick and easy way to backup all databases on a schedule is to call a T-SQL script using Windows Task Scheduler. The sqlcmd program can be called from a task to execute the script on SQL Server 2005 or greater and osql can be called on SQL Server 2000. To get to Windows Task Scheduler open the Run dialog box in Windows and enter "control schedtasks".

The following T-SQL script will backup all databases except for tempdb to @BackupLocation (path must already exist and include a trailing slash) on SQL Server 2005 or greater. It does not delete old backups so you may need to include logic for that depending on your situation (perhaps by using a PowerShell or VBScript script with Windows Task Scheduler):

/* Change the @BackupLocation as needed */
DECLARE @BackupLocation AS NVARCHAR(255)
SET @BackupLocation = 'c:\dbbackups\'

/* Set @CurrentDate to a timestamp in mmddyyyy form */
DECLARE @CurrentDate AS NVARCHAR(255)
SET @CurrentDate = REPLICATE('0', 2 - LEN(CAST(MONTH(GETDATE()) AS NVARCHAR))) + 
CAST(MONTH(GETDATE()) AS NVARCHAR) +
REPLICATE('0', 2 - LEN(CAST(DAY(GETDATE()) AS NVARCHAR))) + 
CAST(DAY(GETDATE()) AS NVARCHAR) + CAST(YEAR(GETDATE()) AS NVARCHAR)

DECLARE @DatabaseName AS NVARCHAR(255)

/* Get all databases except for the temporary database */
DECLARE Databases CURSOR FOR
SELECT name
FROM sys.databases
WHERE name != 'tempdb'

OPEN Databases

FETCH NEXT FROM Databases
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @Command AS NVARCHAR(MAX)
	SET @Command = 'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + ' TO DISK = ''' + 
		@BackupLocation + @DatabaseName + @CurrentDate + '.BAK'''
	EXEC(@Command)
	FETCH NEXT FROM Databases
	INTO @DatabaseName
END

CLOSE Databases
DEALLOCATE Databases

If you want to run the script on SQL Server 2000 change "sys.databases" to "master..sysdatabases" since the system views were not included before SQL Server 2005.

Tags: