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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

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