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.
  1. DECLARE @name VARCHAR(50) -- database name
  2. DECLARE @path VARCHAR(256) -- path for backup files
  3. DECLARE @fileName VARCHAR(256) -- filename for backup
  4. DECLARE @fileDate VARCHAR(20) -- used for file name
  6. -- specify database backup directory
  7. SET @path = 'C:\SQL_Server_Backup\'
  9. -- specify filename format
  10. -- SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
  11. SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
  13. DECLARE db_cursor CURSOR READ_ONLY FOR
  14. SELECT name
  15. FROM master.dbo.sysdatabases
  16. WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
  18. OPEN db_cursor
  19. FETCH NEXT FROM db_cursor INTO @name
  21. WHILE @@FETCH_STATUS = 0
  22. BEGIN
  23. SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
  24. BACKUP DATABASE @name TO DISK = @fileName
  26. FETCH NEXT FROM db_cursor INTO @name
  27. END
  29. CLOSE db_cursor
  30. 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
  1. -- specify filename format
  2. SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DBname_YYYYDDMM_HHMMSS.BAK
  1. -- specify filename format
  2. SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SCREENSHOT

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. 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.

All databases are backed up with single T-SQL script.

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. 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.
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.