Post reply

Automate the Transaction Log Restore

  • July 12, 2010 at 1:00 pm

    #221200

    Hello Everyone

    I am trying to come up with a complete process, end to end, to restore the differential backup and log backups in order.

    Given that I have a database in Full Recovery mode, I take a Full backup, then twice a day I take a Differential backup, and every 5 minutes, I take Transaction Log backups.

    I would like to come up with a means to restore Full Backup, the Differential backups, and the Trans Log backups in order. I would like to use either SSIS or T-SQL.

    Please Advise on the steps required, and what needs to be done in what order. Is there even a way to code this process?

    Thank you in advance

    Andrew SQLDBA

  • July 12, 2010 at 2:01 pm

    #1191674

    I've written some T-SQL that more or less does the same thing.

    I use msdb.dbo.backupmediafamily joined with msdb.dbo.backupset as the basis for my code..

    The 'type' column in backupset will tell you if its a full, diff or log backup, and the 'physical_device_name' should tell you the file.

    From that you should be able to put together a script that gets you what you need.

  • July 12, 2010 at 2:09 pm

    #1191680

    Very nice, I will give that a look

    Thank you for your advice.

    Andrew SQLDBA

  • July 12, 2010 at 2:13 pm

    #1191684

    OK, neither of those tables exist in SQL 2008. Do you happen to know the table names for SQL 2008?

    Thanks

    Andrew SQLDBA

  • July 12, 2010 at 2:15 pm

    #1191685

    Ok, is there an IFCode shortcut for <Totally Embarrassed>

    I misspelled the database name.

    It is beginning to feel more like a monday now.

    Andrew SQLDBA

  • July 12, 2010 at 2:17 pm

    #1191687

    Are you sure?

    http://msdn.microsoft.com/en-us/library/ms188062%28v=SQL.100%29.aspx

Viewing 6 posts - 1 through 5 (of 5 total)

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