Backup multiple databases, excluding specific databases

stratburststratburst Posts: 7
edited May 27, 2005 5:05AM in SQL Backup Previous Versions
In order to streamline creation of an automated backup plan on a sql server with many databases I wish to use the script provided to backup multiple databases but there are several databases on the server that I wish to exclude from this process. The data bases that I wish to backup all are set to full recovery mode, the databases I wish to exclude are all configured simple recovery mode. any way to query the recovery mode of the db in the script and backup only db's set to full recovery?

Script provided from documentation:

set nocount on
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 = 'F:\MSSQL_BACKUP\'
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

Comments

  • peteypetey Posts: 2,358 New member
    Use the DATABASEPROPERTEX function e.g.

    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.
    --
    --

    declare @recoverymodel varchar(12)
    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
    SELECT @recoverymodel = CAST(DATABASEPROPERTYEX(@databasename, 'RECOVERY') AS VARCHAR(12))
    IF @recoverymodel <> 'SIMPLE'
    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
    END

    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Script provided fails to backup. In Q-Analyzer It completes quickly with only a display of databases where recovery = full. Is it possible that the first db selected has recovery to simple so it fails there?

    Thanks in advance ~Chris
  • peteypetey Posts: 2,358 New member
    Yes, there was an error in the original script, but I modified it a couple of minutes later so it should work now.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    There is an undocumented stored procedure in the master database of SQL Server that you could also use called sp_MSForEachDb. This works similar to a DOS for command to execute the same SQL against all of the databases on the system:
    EXEC master..sp_MSforeachdb "master..sqlbackup '-SQL ""BACKUP DATABASE &#91;?&#93; TO DISK=''&lt;AUTO&gt;''""'"
    
  • How can I add the encryption password to this script?

    Thanks
  • When you want to add an encrypted password you will need to add the option
    PASSWORD = 'password'
    Daniel Handley
    Red Gate Software Ltd
Sign In or Register to comment.