Backing Up All Microsoft SQL Server Databases

Published November 17th, 2012


I found the following query SQL which will back up all databases on a Microsoft SQL Server instance a while back. I wish I remembered where I found it so that I could give credit where credit is due but I do not. Anyways I figured I would post it here for anyone interested.

This SQL query will back up all databases within a Microsoft SQL Server instance excluding the system databases. You can easily exclude more databases by editing the following line and adding the databases to exclude to it.

WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

You can also specify the folder in which to save the back up files by modifying the following line.

SET @path = 'C:\Backup\'

Here is the SQL query in it’s entirety.

--
-- Backup all databases except the system databases.
-- Backup files will be saved in the format DBname_YYYDDMM_HHMMSS.bak.
--
 
DECLARE @name VARCHAR(50) -- Database name.
DECLARE @path VARCHAR(256) -- Path to backup folder.
DECLARE @fileName VARCHAR(256) -- Filename for backup.
DECLARE @fileDate VARCHAR(20) -- Used for file name.
 
-- Specify database backup directory.
SET @path = 'C:\Backup\'
 
-- Specify filename format.
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
 
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these 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