Follow

How to make a full backup of All Databases in SQL

by Tomas Blomqvist

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:\Backup\' 

 

-- specify filename format

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

 

DECLARE db_cursor CURSOR FOR 

       SELECT name

       FROM master.dbo.sysdatabases

       WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

         AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')

 

       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 WITH COMPRESSION, CHECKSUM, STOP_ON_ERROR

                     FETCH NEXT FROM db_cursor INTO @name  

              END  

       CLOSE db_cursor  

DEALLOCATE db_cursor

 

*Note:  This script includes options for Compression and Checksum (WITH COMPRESSION, CHECKSUM, STOP_ON_ERROR)  and also includes transaction logs (AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')). 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk