Backup all SQL Server Databases at once

Spread the love

sql

There are those times that you need to backup all you MS SQL databases quickly. If you need to do it all at once here is a script that will help you accomplish that.

Step 1: Create the path specified in the path variable or choose your own

Step 2: Decide the Filename format. Here for a Database with the name DotNetNukeDemo the script will produce DotNetNukeDemo_20180513.BAK

Step 3: Decide which databases to exclude in the backup process

Step 4: Run the script

DECLARE @name VARCHAR(50) -- database name variable
DECLARE @path VARCHAR(256) -- path for backup files 
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) 
 
DECLARE db_cursor CURSOR READ_ONLY 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

 

Leave a Reply

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