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.

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

use master;

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
set @base='D:\SQLBackup\'

-- Iterate through all online databases
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases
open rs_cursor 
Fetch next from rs_cursor into @dbname
   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
CLOSE rs_cursor
deallocate rs_cursor
Am I missing an obvious solution, or?