Backup multiple databases, excluding specific databases
stratburst
Posts: 7
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
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
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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Thanks in advance ~Chris
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
Thanks
PASSWORD = 'password'
Red Gate Software Ltd