Advertisements

SQL-BOX.COM

–SQL Server Storage

Script to backup all databases in an instance – SQL Server

I have this script for a while and used it as many times in my daily routine. The below script back’s up all databases excludes the system databases. Except ‘tempdb’ you can backup all other system databases too by editing the script where NOT IN Clause.

DECLARE @databasename 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\'  -- Set to your path
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')  --excluding system databases
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @databasename  
WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @databasename + '_' + @fileDate + '.BAK' 
 BACKUP DATABASE @databasename TO DISK = @fileName  --,compression     
 Include compression if you want to use
       FETCH NEXT FROM db_cursor INTO @databasename  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

Please comment with your query if you have similar or even easier way to do it.  

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: