restore to point in time between LOG and FULL backup

jchertudijchertudi Posts: 13 Bronze 2
edited June 16, 2010 4:16PM in SQL Backup Previous Versions
We schedule our Log backups to run each weeknight at 6pm, then run a full backup at 2am on Sunday. Possible to then restore to 1pm on Saturday, which falls between the LOG and FULL backup?

If not, how could I plan backups to have FULL done weekly but also have LOG backups for any point in time?
· Share on Twitter

Comments

  • ChrisAVWoodChrisAVWood Posts: 361
    Why do you take so few backups if you want point in time recovery?

    You need to create a better backup strategy. If your database is very large then you can take differential backups during the week. If you have vert little in the way of updates then taking more log backups will not hurt as they would be small and take little time.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • jchertudijchertudi Posts: 13 Bronze 2
    The backup strategy has worked very well for us, we have 3000 relatively small databases, and one TLOG backup has all the transactions.

    I was hoping you might offer some ideas relative to the window between a TLOG backup and a FULL backup. There must be some way to full backup without losing those transactions.
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    If your Databases and disks are okay you can always take a special log backup, after your normal one and before your full backup. Now if you have corruption you may not be so lucky. Maybe you should schedule a log backup just before your full backup so if it runs you can use that for point of time recovery.

    Again I ask why so few backups if you are expecting to restore to a point in time? Even though you may have many small DB's you run the risk of losing many hours/days of data if something goes wrong. Are you prepared for that? Also if you have some large updates then your logs are probably sized much larger than they need to be as you are rarely offloading them. If you are on SQL2005 and beyond only a log backup will offload the committed transactions. Sounds like you should have tuned in to Paul Randals backup discussion on PASS 2 days ago.

    Our scheme is a full backup every day and hourly log backups and alerts to fire a log backup if the log gets fuller than 65% before the scheduled log backup runs.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • jchertudijchertudi Posts: 13 Bronze 2
    For the last few years we have had 0 issues of backup corruption, though I would not argue with you that these things do happen. We may have to reconsider the size of our backup directory in order to accommodate a full every day and transaction logs backups hourly. the other downside to that is that it already takes the SQB UI a few minutes to load all of the backup history from our systems, multiplying the number of files by 24 would really over do it.

    Still back to original question... there is no method to restore to a point in time between a Log and Full Backup, correct? I already started doing a log backup right ahead of our weekend full backup.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    We schedule our Log backups to run each weeknight at 6pm, then run a full backup at 2am on Sunday. Possible to then restore to 1pm on Saturday, which falls between the LOG and FULL backup?

    Yes, restore the last full backup for the previous Sunday, then all log backups made after that from Monday to Friday, and finally restore the log backup made on the following Monday with the STOPAT option.

    A full backup does not break the transaction log restore chain. See http://www.sqlbackuprestore.com/trxlogrestoresequence.htm for details.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jchertudijchertudi Posts: 13 Bronze 2
    Thanks for the response Peter! I had thought this would be the case and tried to do such a restore, but got an error message.

    Also, the SQB UI doesn't seem to support such a restore, when I select a transaction log, it only selects files back to the prior Full backup. Do these sort of restores need to be run via SSMS??
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    What was the error message when you tried to restore to a point-in-time using Monday's transaction log?

    Could you also please post the LSN's of the previous Friday's transaction log backup, Sunday's full backup, and that Monday's transaction log backup? You can do this using the RESTORE SQBHEADERONLY command e.g.
    EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [g:\backups\AdventureWorks_full.sqb]" '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • jerryhungjerryhung Posts: 138
    jchertudi wrote:
    Thanks for the response Peter! I had thought this would be the case and tried to do such a restore, but got an error message.

    Also, the SQB UI doesn't seem to support such a restore, when I select a transaction log, it only selects files back to the prior Full backup. Do these sort of restores need to be run via SSMS??

    Yes, you need to do this in scripts, not via UI

    Restore last sunday's FULL backup, in NONRECOVERY mode

    Restore all remaining LOG backups, all in NONRECOVERY as well
    Monday, Tuesday, Wed, Thurs, Friday
    then restore Saturday's LOG backup
    RESTORE FROM DISK = '....' WITH STOPAT = 'Saturday 1 pm'
    RESTORE LOG { database_name }
    < file_or_filegroup_or_pages > [ ,...n ]
    [ FROM { DISK } = { 'physical_backup_device_name' | 'file_search_pattern' } ] [ ,...n ]
    [ WITH
    ....
    [ [ , ] { STOPAT = { 'date_time' | @date_time_var } ]
    | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
    [ AFTER 'datetime' ]
    | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
    [ AFTER 'datetime']
    } ]
    Jerry Hung
    DBA, MCITP
    · Share on Twitter
  • ChrisAVWoodChrisAVWood Posts: 361
    Now that you have a way of recovering data from the log just before the full is run, have you looked to see how big your transaction log files are compared to the data in your databases? Are your logs large compared to the actual amount of data and do your log backups take a long time?

    If you have individual log backup jobs rather than one that backs up all your database transaction logs at the same time, you might want to add percent log full alerts to fire off extra log backups. If you have just the one job then you might want to run it more aften than once a day for recovery benefits as well as database size savings. If you have large logs you could have lots of VLF's (variable log fragments) that hinder recovery.

    Chris
    English DBA living in CANADA
    · Share on Twitter
Sign In or Register to comment.