Results 1 to 5 of 5
  1. #1
    Member
    Join Date
    Aug 2005
    Location
    Denmark
    Posts
    50
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Default Monthly out-of-cycle full backup

    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.

    Code:
    -- 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
    Regards,
    Jens Dueholm

  2. #2
    Moderator chad_small's Avatar
    Join Date
    Dec 2002
    Location
    Gilbert, AZ
    Posts
    2,194
    Thanks
    1
    Thanked 26 Times in 25 Posts

    Default

    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.
    Chad Small
    IBM Certified Deployment Professional
    chadsmal@gmail.com
    http://www.tsmadmin.com

  3. #3
    Member
    Join Date
    Aug 2005
    Location
    Denmark
    Posts
    50
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Default

    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.
    Regards,
    Jens Dueholm

  4. #4
    Senior Member GregE's Avatar
    Join Date
    May 2006
    Posts
    2,100
    Thanks
    9
    Thanked 17 Times in 16 Posts

    Default

    Quote Originally Posted by JensD View Post
    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?
    Last edited by GregE; 12-17-2012 at 11:33 AM.

  5. #5
    Member
    Join Date
    Aug 2005
    Location
    Denmark
    Posts
    50
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Default

    You might want to take a look at http://msdn.microsoft.com/en-us/libr...ql.105%29.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..
    Regards,
    Jens Dueholm

Similar Threads

  1. Monthly Backup to Tape
    By Drex in forum Backup / Archive Discussion
    Replies: 4
    Last Post: 07-22-2011, 11:11 AM
  2. Incremental Diary, Full Weekly, Full Monthly
    By CarlosJ in forum Backup / Archive Discussion
    Replies: 2
    Last Post: 02-20-2009, 10:56 AM
  3. peak periods in backup cycle
    By influx in forum TSM Reporting & Monitoring
    Replies: 1
    Last Post: 02-26-2008, 05:32 PM
  4. Monthly backup??
    By Tyrael in forum Backup / Archive Discussion
    Replies: 3
    Last Post: 11-19-2007, 06:18 AM
  5. Offsite DB Backup Cycle
    By jordan in forum Backup / Archive Discussion
    Replies: 0
    Last Post: 01-30-2006, 12:39 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •