1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Monthly out-of-cycle full backup

Discussion in 'Microsoft SQL Server' started by JensD, Feb 2, 2012.

  1. JensD

    JensD New Member

    Joined:
    Aug 2, 2005
    Messages:
    58
    Likes Received:
    2
    Occupation:
    Sysadm
    Location:
    Denmark
    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
     
  2.  
  3. chad_small

    chad_small Moderator

    Joined:
    Dec 17, 2002
    Messages:
    2,205
    Likes Received:
    44
    Occupation:
    AIX/SAN/TSM
    Location:
    Gilbert, AZ
    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.
     
  4. JensD

    JensD New Member

    Joined:
    Aug 2, 2005
    Messages:
    58
    Likes Received:
    2
    Occupation:
    Sysadm
    Location:
    Denmark
    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.
     
  5. GregE

    GregE Senior Member

    Joined:
    May 12, 2006
    Messages:
    2,100
    Likes Received:
    31
    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: Dec 17, 2012
  6. JensD

    JensD New Member

    Joined:
    Aug 2, 2005
    Messages:
    58
    Likes Received:
    2
    Occupation:
    Sysadm
    Location:
    Denmark
    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.. :)
     
: lsn, monthly, sql, tdp

Share This Page