Advanced Search

Microsoft SQL Server: Back up all databases.

/ Published in: SQL
Save to your folder(s)
Backs up all databases contained in a Microsoft SQL Server instance.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. --
  2. -- Backup all databases except the system databases.
  3. -- Backup files will be saved in the format DBname_YYYDDMM_HHMMSS.bak.
  4. --
  5.  
  6. DECLARE @name VARCHAR(50) -- Database name.
  7. DECLARE @path VARCHAR(256) -- Path to backup folder.
  8. DECLARE @fileName VARCHAR(256) -- Filename for backup.
  9. DECLARE @fileDate VARCHAR(20) -- Used for file name.
  10.  
  11. -- Specify database backup directory.
  12. SET @path = 'C:\Backup\'
  13.  
  14. -- Specify filename format.
  15. SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
  16.  
  17.  
  18. DECLARE db_cursor CURSOR FOR
  19. SELECT name
  20. FROM master.dbo.sysdatabases
  21. WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
  22.  
  23. OPEN db_cursor
  24. FETCH NEXT FROM db_cursor INTO @name
  25.  
  26. WHILE @@FETCH_STATUS = 0
  27. BEGIN
  28.        SET @fileName = @path + @name + '_' + @fileDate + '.bak'
  29.        BACKUP DATABASE @name TO DISK = @fileName
  30.  
  31.        FETCH NEXT FROM db_cursor INTO @name
  32. END
  33.  
  34. CLOSE db_cursor   
  35. DEALLOCATE db_cursor

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.