Post reply

How to find out what file(s) the last successful backup was from?

  • July 8, 2008 at 7:04 pm

    #187932

    Hello,

    I am implementing a daily job that sends a copy of the most current database backup file from server A to server B, and restore on server B. I need to know whether the backup file from server A has ever been restored on server B before, and if so, the job won't bother restoring again. How can I find out the source backup file on server B that the last successful restore of the database was from?

    For example, if I copied SampleDB_onA_db_20080708.bak to server B at c:\temp, I want to know whether c:\temp\SampleDB_onA_db_20080708.bak was restored before.

    Thanks a lot,

    Viv

  • July 9, 2008 at 4:08 am

    #839960

    Hi

    My 2 cents...

    When u send the backup from server A to server B you create a entry in a table. when the backup is restored you can update the entry marking it that the backup has been restored. When the job runs it can check this table to see if backup has been restored or not.

    Another way would be to use the system table related backup and restore specificaly backupset and restorehistory table that reside in msd. Iam not sure abt this though since the backup/restore operation occurs at 2 diff servers.

    "Keep Trying"

  • July 9, 2008 at 4:26 am

    #839966

    The script below will list all the full backups restored:-

    select physical_device_name

    from msdb.dbo.backupset b

    inner join msdb.dbo.restorehistory r

    on b.backup_set_id = r.backup_set_id

    inner join msdb.dbo.backupmediafamily mf

    on b.media_set_id = mf.media_set_id

    where type = 'D'

    and destination_database_name = ???

    Change the query to select * and you will see backup dates etc, if you want to narrow down the list returned etc.

  • July 9, 2008 at 5:44 am

    #840001

    As a quick verification of log shipping I check the properties of the source database and the destination database. The date and time of the last database backup and the last log backup are the same on both servers when the log shipping is in sync. You could do the same to verify the last backup has been restored to the destination.

  • July 9, 2008 at 5:45 am

    #840004

    Good post. This SQL will come in very handy!

  • July 9, 2008 at 10:45 am

    #840367

    Ian's script is exactly what I am looking for. It saves the efforts to record the restore history myself.

    Dunnjoe's suggestion also makes sense. I will try that.

    Thanks everybody.

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

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