exec master..sqlbackup

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 = ''[email protected]''"', @exitcode OUTPUT, @sqlerrorcode OUTPUT

Thank you for your support,

Louis
Tagged:

Best Answer

  • Dan BDan B Posts: 287 Gold 5
    Accepted Answer
    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 = ''[email protected]''" '

    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
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
Sign In or Register to comment.