SQL full database BACKUP through SQL SCRIPT

We can take multiple database backup in one script execution. Script as Below:

SQL Backup SCRIPT:

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

In above script CURSOR will help us to select multiple database one by one. After execution of query Output as below:-