Backup All databases

twlichtytwlichty Posts: 2
edited April 25, 2005 8:22AM in SQL Backup Previous Versions
Is there a way I can use the extended stored proc to backup ALL of the databases on the server?

Comments

  • Red_Gate created the script below for me and it works great. You can easily change the backup path (@backuppath) and set some other options, such as the Compression level in the @backupstring line. I want to add the mirroring option, but not sure how to setup the 2nd backuppath, being that I'm not a SQL script writer. So, any ideas on this please let me know. -dmacie


    set nocount on
    --
    --
    -- Description: Backs up all databases on the server via a call to the
    -- extended stored procedure for SQLBackup. Script will
    -- raise an error if the word "error" appears any where in
    -- the message string returned by the SQLBackup extended
    -- stored procedure call.
    --
    -- PreReq: The backup directory specified in @backuppath must already
    -- exist.
    --
    -- History:
    --
    -- 03/21/2005 (Rob Alexander) Created.
    --
    --
    declare @datestamp varchar(30)
    declare @databasename varchar(200)
    declare @backuppath varchar(500)
    declare @filename varchar(500)
    declare @backupstring varchar(1000)
    declare @failcount int
    declare @failflag int

    create table #resultstring (message varchar (2500))
    create table #jobresults (message varchar(2500))

    set @failflag = 0
    set @backuppath = 'E:\DBBACKUP\SQLBackup\'

    declare dbs cursor for
    select NAME from sysdatabases
    where name != 'tempdb'
    order by dbid

    open dbs
    fetch dbs into @databasename
    @fetch_status != -1 begin

    set @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12)
    set @filename = @backuppath + @databasename + '_db_(full)_' + @datestamp + '.sqb'
    set @backupstring = '-SQL "BACKUP DATABASE TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @databasename + ') Full'', ERASEFILES_ATSTART = 1, COMPRESSION = 1" -E'

    insert into #resultstring
    exec master..sqlbackup @backupstring

    select @failcount = count(*) from #resultstring
    where patindex('%error%', message) > 0

    if @failcount > 0 begin
    insert into #jobresults (message) select message from #resultstring
    set @failflag = 1
    end
    else begin
    insert into #jobresults (message) values ('Backup succeeded: ' + @databasename)
    end

    delete from #resultstring

    fetch dbs into @databasename

    end

    close dbs
    deallocate dbs

    select * from #jobresults

    drop table #resultstring
    drop table #jobresults

    if @failflag = 1 begin
    RAISERROR ('Backup failed to complete successfully for all databases.', 16, 1)
    end

    set nocount off
  • Here is a similar script with backup file mirroring and it only picks up new databases since last backup. Very nice!

    USE master
    DECLARE @dbname nvarchar(260)
    DECLARE @reccount int
    DECLARE cDatabases CURSOR FOR SELECT name from sysdatabases where name!='tempdb'
    declare @datestamp varchar(30)
    declare @backuppath varchar(500)
    declare @mirrorpath varchar (500)
    declare @filename varchar(500)
    declare @mirrorname varchar(500)
    declare @backupstring varchar(1000)
    declare @failcount int
    declare @failflag int

    create table #resultstring (message varchar (2500))
    create table #jobresults (message varchar(2500))

    set @failflag = 0
    set @backuppath = 'D:\DBBACKUP\SQLBackup\'
    set @mirrorpath = 'E:\DBBACKUP\SQLBackup\'

    OPEN cDatabases
    FETCH NEXT FROM cDatabases INTO @dbname

    @FETCH_STATUS=0
    BEGIN
    SELECT @reccount=COUNT(database_name) FROM msdb..backupset where database_name LIKE '%' + @dbname +'%'
    IF @reccount = 0
    BEGIN
    set @datestamp = left(replace(replace(replace(convert(varchar(30), getdate(), 120), '-', ''), ' ', ''), ':', ''), 12)
    set @filename = @backuppath + @dbname + '_db_(full)_' + @datestamp + '.sqb'
    set @mirrorname = @mirrorpath + @dbname + '_db_(fullMirror)_' + @datestamp + '.sqb'
    set @backupstring = '-SQL "BACKUP DATABASE TO DISK = ''' + @filename + ''' WITH NAME = ''Database (' + @dbname + ') Full'', MIRRORFILE = ''' + @mirrorname + ''', ERASEFILES_ATSTART = 1, COMPRESSION = 1" -E'

    insert into #resultstring
    exec master..sqlbackup @backupstring

    select @failcount = count(*) from #resultstring
    where patindex('%error%', message) > 0

    if @failcount > 0
    begin
    insert into #jobresults (message) select message from #resultstring
    set @failflag = 1
    end
    else
    begin
    insert into #jobresults (message) values ('Backup succeeded: ' + @dbname)
    end

    delete from #resultstring
    END


    FETCH NEXT FROM cDatabases INTO @dbname
    END

    CLOSE cDatabases
    DEALLOCATE cDatabases

    select * from #jobresults

    drop table #resultstring
    drop table #jobresults

    if @failflag = 1 begin
    RAISERROR ('Backup failed to complete successfully for all databases.', 16, 1)
    end

    set nocount off
Sign In or Register to comment.