T-SQL Script to backup all databases

Database backup is a must know task for a DBA. In this blog we’ll look at T-SQL script to backup all databases at once.

The logic is to iterate through all the online/active user databases and construct dynamic backup query. The query provides option to specify the backup folder and the backup options. The complete backup path is made up off @backuppath  + @database + timestamp.   The T-SQL for the same is given below

DECLARE 
	@database NVARCHAR(100),
	@backupoptions NVARCHAR(MAX),
	@dynamic_sql NVARCHAR(MAX),
	@backuppath NVARCHAR(MAX)

DECLARE backup_cursor CURSOR FAST_FORWARD FOR 
SELECT name 
	FROM sys.databases 
	where database_id>4
	AND state_desc='ONLINE'

SET @backupoptions = ' WITH STATS=10,INIT '
SET @backuppath='E:\Ahmad\'
OPEN backup_cursor 
FETCH NEXT FROM backup_cursor INTO @database 

WHILE @@FETCH_STATUS=0
BEGIN
DECLARE @fullbackuppath nvarchar(max)=''
SET @fullbackuppath= @backuppath + @database + '_' + REPLACE(convert(varchar(12),getdate(),110),'-','_') + '.bak'
SET @dynamic_sql=' BACKUP database ' + @database + ' TO DISK= ''' + @fullbackuppath + '' + '''' + @backupoptions + ''
EXEC(@dynamic_sql)
FETCH NEXT FROM backup_cursor INTO @database 
END

CLOSE backup_cursor
DEALLOCATE backup_cursor

The cursor backup_cursor iterates through the online user databases and constructs dynamic backup query by concatenating backup command, database,@ fullbackuppath(@backuppath+@database) and the @backupoptions.

The above query can be scheduled as a SQL Server Agent job to automatically take backup as per the backup strategy.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook