Results 1 to 5 of 5
Thread: Monthly out-of-cycle full backup
02-02-2012, 10:19 AM #1
- Join Date
- Aug 2005
- Thanked 2 Times in 2 Posts
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.
-- 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
By Drex in forum Backup / Archive DiscussionReplies: 4Last Post: 07-22-2011, 11:11 AM
By CarlosJ in forum Backup / Archive DiscussionReplies: 2Last Post: 02-20-2009, 10:56 AM
By influx in forum TSM Reporting & MonitoringReplies: 1Last Post: 02-26-2008, 05:32 PM
By Tyrael in forum Backup / Archive DiscussionReplies: 3Last Post: 11-19-2007, 06:18 AM
By jordan in forum Backup / Archive DiscussionReplies: 0Last Post: 01-30-2006, 12:39 PM