Monthly out-of-cycle full backup

[JensD]

Hi

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
WHILE @@FETCH_STATUS = 0
BEGIN
   select @status=CAST(DATABASEPROPERTYEX(@dbname,'Status') as varchar(256))
   IF @dbname <> 'tempdb' and @status = 'ONLINE' 
   BEGIN
      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
   END
   FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor

Am I missing an obvious solution, or?

Regards,
JensD

 

[chad_small]

Have you tried creating a secondary node something like NODE-SQL-MONTHLY and defining the tdpopt file for it. If you want to use the copy-only function in SQL then you need to dump the backup to a file and have the MONTHLY node act like a normal client and do a backup of the directory where the backup is stored.

 

[JensD]

Sorry for the lack of answer - I'm been away on holiday for the last few weeks.

No I havn't tried to create a new node with the monthly schedule, as I'm 99,9% certain that it would break the log chain.

I guess I'll just stick to the SQL-script and place those files under a new policy that fits my retention policy for those monthly backups.

 

[GregE]

My main concern is that this monthly backup screws up the LSN that the diff and log backups depend on.

This is an older thread but hopefully someone can answer this.

I know LOG backups depend on the Log Seqence Number, but was unaware that DIFF backups do. Are we SURE that's the case for DIFF?

I have this same situation, where I am being required to implement a 3 Month and 1 Year backup for SQL dbs, in addition to their daily. The DBs will be in SIMPLE recovery mode, but I would like to utilize DIFF backup most days to avoid a FULL everyday for Dbs that see very little change.

Does DIFF backup absolutely break the LSN if the DB is in SIMPLE recovery mode?

 

[JensD]

You might want to take a look at http://msdn.microsoft.com/en-us/library/ms190729(v=sql.105).aspx (possible choose another version than SQL 2008 R2) to be sure.

DIFF backups relate to the latest full backup - and a log backup relates to the latest full, diff or log backup if a full, diff or another log backup exists.

If you do not use a "Full Recovery Model" and stick to Simple (as you say you intend to) you loose all access to diff and log backups.

All backups of Simple databases are full backups - there is no log available, and thus no way of knowing what data has changed since the last backup, so a full backup with all data is the only way of ensuring a valid backup.

But please - don't take my word for it - read up on MSDN and the online MS SQL help for truely valid answers to your questions..