Backup All databases

twlichty

Is there a way I can use the extended stored proc to backup ALL of the databases on the server?

dmacie

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

dmacie

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