Maintenace Plan...database first then logs or vice versa

  • What order should the maintenace plan backup the data? I currently have it doing databases then logs. I found out today that to get the point in time I needed, I had to restore 3 files versus 2. For example, I needed a point in time recovery for date/time 200809021500 and had to restore these 3 files.

    Restore database_200809010100.bak

    Restore database_log_200809010100.trn

    Restore database_log_200809020100.trn

    with STOPAT = N'09/02/2008 03:00:00 PM'

    If I leve out the log file from 200809010100.trn it throws up an error saying some logs are missing. I was successful with the three files. I'm thinking about reversing the maintenance plan to backup the logs first then database. What do you all think? Thanks.

  • You can't really do logs first. You have to restore the database and then a series of logs and the backup has to be in the same order.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • What I'm saying is if I would have done logs first then database, my POT restore would have only required 2 files.

    database_200809010100.bak

    database_log_200809021245.trn

  • Maybe I'm confused (hardly a new case), but however many log files you've backed up, you'll have to traverse them unitl you get to the log where the end time is located. It can stink if you run a backup once a day and you have to do a point in time recovery to five minutes BEFORE the backup and that means walking across 40 different log files from the previous backup, but that's how it works since the logs aren't cumulative.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Guess my main question is what is best practice on maintenance plan? I currently have it database then logs and thinking about reversing it. Thanks.

  • It sounds like your maintenance plan is set up right. The scenario you described is not because of a database or log first or second, but because you have to restore from the database first and then each log backup in order until you arrive at the last one.

    I have a point-in-time example here (old, but still applicable) and a set of backup examples here[/url]. They don't use the maintenance plan because that thing just gets in the way of doing the same stuff as outlined in the two articles. Reading through those might clarify things a bit.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I cannot access the first link with the error below.

    "Sorry, an error has occurred

    You do not appear to be the author of this content. You can find your contributions in the Contribution Center.

    If problems persist, please contact us to let us know."

    I will check out the second link. Thanks!

  • I am such a dope sometimes.

    http://www.sqlservercentral.com/articles/Backup/recoverytoapointintime/1914/[/url]

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Are you saying you are currently backing up the data file, then taking a transaction log backup immediately thereafter? If so, I think you'd be better served by reversing the order. My thinking is as follows:

    Let's say you take a nightly DB backup, and hourly trn backups. If you take the trn backup immediately prior to the DB backup, you'll preserve the ability to do a point-in-time restore to any point prior to the DB backup (you'd take the previous night's backup and walk down 23 trn backups to the time you need). If you take the DB backup first, however, I don't think you'll be able to do a point-in-time restore to a time between the previous trn backup and the full DB backup, since I don't believe you can identify a point in time in a full DB backup.

    So if you take a trn backup at 2100 and a full backup at 2200 followed by a trn backup at 2205, you won't be able to restore to 2130. You will be able to restore between 2200 and 2205, but you'd also have that ability from your 2300 trn backup.

    Basically, I believe there to be a value to the trn backup immediately prior to the data file backup. I do not believe there to be a value in a trn backup immediately following a data file backup. All it will do, as you've already indicated, is insert another file into the backup chain you will need to include in restore.

    ______
    Twitter: @Control_Group

  • Full backups don't truncate the log and hence don't break the log chain. In your example, it's possible to take the previous day's full backup and restore all the logs up to and including the 2205 log backup or even the 2300 trn backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Matt, you hit the nail on the head with what I'm asking. Thank you to you and Grant for the help.

  • GilaMonster (9/3/2008)


    Full backups don't truncate the log and hence don't break the log chain. In your example, it's possible to take the previous day's full backup and restore all the logs up to and including the 2205 log backup or even the 2300 trn backup.

    Ah, you're right, of course.

    So it doesn't matter which side of the full you do the trn. It will just be one more file in the chain on whichever side of the full you take it.

    ______
    Twitter: @Control_Group

  • Grant Fritchey (9/3/2008)


    I am such a dope sometimes.

    http://www.sqlservercentral.com/articles/Backup/recoverytoapointintime/1914/[/url]

    Grant,

    Just read your two articles and would like to say they were well written and cleared some things up for me. Thanks!!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply