Thursday, February 7, 2013

Simple script to backup all SQL Server databases

Working as developer or DBA, we often required to take the backup of database. This is not a big deal if you have a handful of databases, but this task become very hectic if the there is number of databases (ex. 50+) on the same instance of SQL Server. You could use SQL Server Management Studio to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Here is a  simple script to backup all SQL Server databases.

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


-- 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

Happy reading!! :)

No comments:

Post a Comment

^ Scroll to Top