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 / comments
- Community
- SQL Backup Previous Versions
- Backup All databases
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 cDatabase...
0 votes
