SQL full database BACKUP through SQL SCRIPT

Sandeepraturi
Posted by Sandeepraturi under Sql Server category on | Points: 40 | Views : 1573
We can take multiple database backup in one script execution. And we will use cursor to select multiple database name one by one.

SQL Backup SCRIPT as below:

DECLARE @DBname VARCHAR(50) -- DB database name

DECLARE @DBpath VARCHAR(256) -- DB backup files path

DECLARE @DBfileName VARCHAR(256) -- DB filename for backup

DECLARE @DBfileDate VARCHAR(20) -- ADD datetime with filename

-- set database backup path

SET @DBpath = 'C:\Backup\'

-- Set file name with datetime

Select @DBfileDate=CONVERT(varchar(20),GETDATE(),112) + REPLACE(CONVERT(varchar(5),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name IN ('data','Test') -- select database name for backup

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DBname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DBfileName = @DBpath + @DBname + '_' + @DBfileDate + '.BAK'

BACKUP DATABASE @DBname TO DISK = @DBfileName

FETCH NEXT FROM db_cursor INTO @DBname

END

CLOSE db_cursor

DEALLOCATE db_cursor

Comments or Responses

Posted by: Sandeepraturi on: 8/7/2018 Level:Starter | Status: [Member] | Points: 10
Thanks Rachel

Login to post response