Monthly out-of-cycle full backup
I've been searching and reading up on a lot of threads, but none seem to describe what I'm pondering..
I've been tasked - to my regret - with keeping a monthly full backup of our SQL 2005 (soon 2008R2) databases.
At the moment I've got this scheduled:
- Full backup 1 time each week (saturday night)
- Daily diff backup (each evening)
- Hourly log backup (24/7/365 unless a diff or full backup is processing)
We are doing regular restores of all types of backups as part of development, testing with live data and debugging, so I know everything works and that I can restore to a point-in-time without problems and with no LSN (Log Sequence Number) issues when restoring.
My problem is this monthly full backup.
I want the monthly full to be detached from my normal cycle as much as possible.
I know it will have implications to the log backups as I cannot perform log backup while a full backup is running, but I can live with that. Using a different managementclass is of course self-explanatory, and I have no problem using a different node name if it helps (will it?) to seperate things.
I cannot use VSS as I need the data stored on the TSM server.
My main concern is that this monthly backup screws up the LSN that the diff and log backups depend on. How can I be absolutely sure that it wont change or mess up the LSN of the regular backups?
So far, the best option I've come up with involves a T-SQL (below) script that dumps the databases to flat files and then use a BA to store the files. That way I can be absolutely sure that LSN numbers are not affected as I know the backups are taken with the option COPY_ONLY.
Am I missing an obvious solution, or?
-- revision: $Id: backup-flat.sql,v 1.3 2012/02/02 09:39:15 jedc Exp $
-- MSSQL backup script
-- Backups all online databases to flat files that gets overwritten on
-- every backup (and not appended).
declare @base as varchar(512)
declare @dbname as varchar(40)
declare @status as varchar(256)
declare @dbbkpname as varchar(256)
-- Place to put backups - must exist or the script will fail
-- Iterate through all online databases
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases
Fetch next from rs_cursor into @dbname
WHILE @@FETCH_STATUS = 0
select @status=CAST(DATABASEPROPERTYEX(@dbname,'Status') as varchar(256))
IF @dbname <> 'tempdb' and @status = 'ONLINE'
set @dbbkpname = @base + @dbname + '-COMPLETE-sqlagent.sqlbackup'
-- backup databases 'with format' to replace existing files and 'copy_only' so LSNs are not affected
backup database @dbname to disk=@dbbkpname with format, copy_only
FETCH NEXT FROM rs_cursor INTO @dbname