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

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

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 

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 + ''
FETCH NEXT FROM backup_cursor INTO @database 

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

Data Platform Virtual Summit 2020

Leave a Reply

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