How to Backup All Databases in SQL Server

This is a SQL script of get all Databases Backup in particular folder.
backup file name will be "DataBaseName_{Month}_{Date}_{Year}.bak"

Notes:

  1. Current user have full access of backup database.
  2. Remote drive setup is extra step you have to perform in sql server in order to backup your dbs to remote drive.
  3. You have to change you sql server account to a network account and add that user to have full access to the network drive you are backing up to.

SQL Script

  1. DECLARE @DBName VARCHAR(MAX)  
  2. DECLARE @FileName VARCHAR(MAX)  
  3. DECLARE @FileDate VARCHAR(20)  
  4. DECLARE @Path VARCHAR(MAX)  
  5. SET @Path='C:\DBBackup\'  
  6. SET @FileDate=REPLACE((SELECT CONVERT(VARCHAR(20),GETDATE(),101)),'/','_')  
  7. DECLARE db_cursor CURSOR FOR  
  8. SELECT name  
  9. FROM master.dbo.sysdatabases  
  10. WHERE name NOT IN ('master','tempdb','model','msdb')  
  11. OPEN db_cursor  
  12. FETCH NEXT FROM db_cursor into @DBName  
  13. WHILE @@FETCH_STATUS = 0  
  14. BEGIN  
  15. SET @FileName= @Path + @DBName + '_' + @FileDate + '.BAK'  
  16. BACKUP DATABASE @DBName TO DISK =@FileName  
  17. FETCH NEXT FROM db_cursor into @DBName  
  18. END  
  19. CLOSE db_cursor  
  20. DEALLOCATE db_cursor 

Ebook Download
View all
Learn
View all