December 17, 2008 at 10:36 am
Ok.. I have a maint plan with 4 subplans
1. Optimization (checkdb, reorganize index, etc) Runs Sunday
2. Full Backup Runs Friday
3. Differential Backup Runs every day except Friday
4. Tlog Backup Runs every hour
The problem I'm running into is that an automated process will attach SQL databases to the server instance. When this happens, the Tlog and Differential backups will fail all week until either a full backup is made, or the full backup job runs on Friday night.
What I'd like to do is customize the database backup task to do a differential if a full exists or a full if there is not current one for the database.
What would be better would be a DDL trigger that backs up new databases... But while I can create one for CREATE_DATABASE, it doesn't fire off when I attach or restore a DB, only when I make a new one via CREATE DATABASE (without the FOR ATTACH). So I was hoping there was a way to make my maint plan intelligently pick which kind of backup to perform.
Any thoughts?
December 17, 2008 at 10:47 am
I don't think thats possible with a standard maintenance plan, but you could use the Execute Sql Script task to execute a script for the differential and tlog backups. In the script do a check if a backup for the database exists in the backuphistory table in msdb.
Of course if it's possible I would try to edit the autmated process which attaches the new databases to include a full backup. Without that you will always risk the loss of data for a couple of days.
[font="Verdana"]Markus Bohse[/font]
December 17, 2008 at 2:38 pm
Yah that's pretty much what I did...
Put this in the maint plan before the tlog and differentials...
declare @cmd nvarchar(max)
declare @dbs sysname
declare csr_nobackup cursor for
select
a.[name]
from sys.databases a left join msdb.dbo.backupset b on
a.[name] = b.[database_name]
where
b.[database_name] is null and a.[name] not in ('master','model','msdb','tempdb')
open csr_nobackup
fetch next from csr_nobackup into @dbs
while @@fetch_status = 0
BEGIN
set @cmd = 'BACKUP DATABASE [' + @dbs + '] TO DISK =''U:\SQLBackup\' + @dbs + '_' + cast(year(getdate()) as nvarchar(4)) + cast(month(getdate()) as nvarchar(2)) + cast(day(getdate()) as nvarchar(2)) + '.bak'''
exec sp_executesql @cmd
fetch next from csr_nobackup into @dbs
END
close csr_nobackup
deallocate csr_nobackup
I guess this is RBAR code here... But I'd almost rather it be a bit slow... not sure I want to build a huge dynamic sql command to back them all up at once and run it... I'm sure it would work... but it makes me feel queasy..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply