exec master..sqlbackup

flouis

Good morning,
I want to pass only the online databases dynamically in the following query. Can you beeing helped please?

Exec master..sqlbackup '-SQL "BACKUP DATABASES [db1,db2,db3] TO DISK = ''\\NetworkLocation'' WITH ERASEFILES = 1b, MAXTRANSFERSIZE = 262144, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 4, VERIFY,MAILTO_ONERROR = ''admin@yahoo.com''"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

Thank you for your support,

Louis

Dan B

Hi flouis,

Apologies for the delayed reply.

You should be able to tweak the below script to get what you need here. This will backup everything but the MASTER,MODEL,MSDB database that is online. Please let me know if you need anything further.

--This script will restore multiple databases where each database has it's own Full Backup to be restored.
--If you have more than one Full Backup file per database, the restore WILL fail.
USE master
declare @dbname nvarchar(260)
declare cDatabases CURSOR FOR SELECT [name] from sys.databases where [name]!='tempdb' AND [name]!='master' AND [name]!='msdb' AND [name] !='model' AND [state] = 0
declare @datestamp varchar(30)
declare @backuppath varchar(500)
--@filename will need to be modified depending how the backup files are named
declare @filename varchar(500)
declare @backupstring varchar(1000)
declare @exitcode int
declare @sqlerrorcode int

--Set @restorepath to be the path of where your backups are located, in my example this is 'D:\Backup\'
set @backuppath = 'D:\SQLSERVER\BACKUP\'

OPEN cDatabases
FETCH NEXT FROM cDatabases INTO @dbname

@FETCH_STATUS=0
BEGIN
set @filename = @backuppath + 'FULL_' + @dbname + '.sqb' -- @filename will need to be modified depending how you want the backup files to be named

SELECT @filename

set @backupstring = '-SQL "BACKUP DATABASE TO DISK = ''' + @filename + '''WITH ERASEFILES = 1b,MAXTRANSFERSIZE = 262144,CHECKSUM,DISKRETRYINTERVAL = 30,DISKRETRYCOUNT = 10,COMPRESSION = 4,VERIFY,MAILTO_ONERROR = ''admin@yahoo.com''" '

exec master..sqlbackup @backupstring, @exitcode OUTPUT, @sqlerrorcode OUTPUT

IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

FETCH NEXT FROM cDatabases INTO @dbname
END

CLOSE cDatabases
DEALLOCATE cDatabases