Post reply

Verify Database was restored

  • June 13, 2013 at 12:04 pm

    #278572

    Hello

    I need to verify that a database was restored from a particular backup.

    I used RESTORE HEADERONLY on the BAK an can see FirstLSN, LastLSN.

    Is there a way to match the LSN to the database that was already restored?

    Thanks

    Dave

  • June 13, 2013 at 12:21 pm

    #1623852

    or you could check the restore history.......

    --returns info on when databases were restored and from which backup file

    SELECT [rs].[destination_database_name],

    [rs].[restore_date],

    [bs].[backup_start_date],

    [bs].[backup_finish_date],

    [bs].[database_name] as [source_database_name],

    [bmf].[physical_device_name] as [backup_file_used_for_restore]

    FROM msdb..restorehistory rs

    INNER JOIN msdb..backupset bs

    ON [rs].[backup_set_id] = [bs].[backup_set_id]

    INNER JOIN msdb..backupmediafamily bmf

    ON [bs].[media_set_id] = [bmf].[media_set_id]

    ORDER BY [rs].[restore_date] DESC

    ---------------------------------------------------------------------

  • June 13, 2013 at 12:40 pm

    #1623859

    Thanks - but I overwrite the BAK file with a more recent one and always have it in the same directory.

    Is there anything in the database that shows what BAK I restored from?

  • June 13, 2013 at 1:20 pm

    #1623872

    NJDave (6/13/2013)

    Thanks - but I overwrite the BAK file with a more recent one and always have it in the same directory.

    Is there anything in the database that shows what BAK I restored from?

    Are you saying that each .BAK file has the same name every day?

  • June 13, 2013 at 1:41 pm

    #1623879

    Lynn Pettis (6/13/2013)

    NJDave (6/13/2013)

    Thanks - but I overwrite the BAK file with a more recent one and always have it in the same directory.

    Is there anything in the database that shows what BAK I restored from?

    Are you saying that each .BAK file has the same name every day?

    If this is the case then I don't see how you would have more than one day's backup unless you had them in different directories. I'd recommend that you modify your backup process to include the date the backup was performed. You can keep only n days worth of backups, but only having one day is dangerous. What if a user makes a mistake today, the backup runs tonight and they tell you about it tomorrow? You won't be able to restore to a good state because the backup overwrote the good one.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 13, 2013 at 1:47 pm

    #1623881

    LSN information is also listed in the backupset table referenced in the query above

    ---------------------------------------------------------------------

  • June 13, 2013 at 1:54 pm

    #1623886

    The BAK files are delivered on an external drive. They are large so when I copy them over, I have to overwrite.

    The BAK files always have the same name.

    This time - an older BAK was in a directory and newer versions were in a subdirectory

    I restored the older version. I saw BackupStartDate from my RESTORE HEADERONLY command.

    I still don't have a way to match what BAK is being used in a scenario that I have multiple BAK files with the same name. I thought there would have to be.

    Thanks for your help

    Dave

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

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