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!! :)