How To Backup All Databases In SQL Server

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  
  5.    
  6. -- specify database backup directory  
  7. SET @path = 'C:\SQL_Server_Backup\'    
  8.    
  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),':','')  
  12.    
  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  
  17.    
  18. OPEN db_cursor     
  19. FETCH NEXT FROM db_cursor INTO @name     
  20.    
  21. WHILE @@FETCH_STATUS = 0     
  22. BEGIN     
  23.    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'    
  24.    BACKUP DATABASE @name TO DISK = @fileName    
  25.    
  26.    FETCH NEXT FROM db_cursor INTO @name     
  27. END    
  28.    
  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.