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