2925total visits.
When we have more databases on a Server to backup, it is very time to consume to backup all database 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 backup them one by one.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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:\SQL_Server_Backup\' -- specify filename format -- SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 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') -- 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 |
The above T-SQL will backup each database within the instance of SQL Server.
Note @path to be replaced by the backup directory. In my case backup directory is ‘C:\SQL_Server_Backup\’
File Naming Format
DBname_YYYYDDMM.BAK
|
1 2 |
-- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) |
DBname_YYYYDDMM_HHMMSS.BAK
|
1 2 |
-- specify filename format SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') |
Screenshot:
All database backed up with single T-SQL script.
