When we have several databases on a Server to backup, it is very time-consuming to backup all databases manually. In such case, we can use T-SQL to automate these tasks. We can use SQL Server Management Studio to back up the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.
Using T-SQL, we can generate backup commands. With the use of cursors, we can cursor through all of the databases to back them up one by one.
- DECLARE @name VARCHAR(50)
- DECLARE @path VARCHAR(256)
- DECLARE @fileName VARCHAR(256)
- DECLARE @fileDate VARCHAR(20)
-
-
- SET @path = 'C:\SQL_Server_Backup\'
-
-
-
- SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
-
- DECLARE db_cursor CURSOR READ_ONLY FOR
- SELECT name
- FROM master.dbo.sysdatabases
- WHERE name NOT IN ('master','model','msdb','tempdb')
-
- 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
The above T-SQL will back up each database within the instance of SQL Server. Note that the @path is to be replaced by the backup directory. In my case, the backup directory is ‘C:\SQL_Server_Backup\’.
File Naming Format
DBname_YYYYDDMM.BAK
-
- SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DBname_YYYYDDMM_HHMMSS.BAK
-
- SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SCREENSHOT
All databases are backed up with single T-SQL script.
This script will allow you to back up all the databases on the fly. The script looks for the .mdf file in your SQL instance and backs all of the files it finds except the system database.