Identify the Backup used to Restore the Database

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    Comments posted to this topic are about the item Identify the Backup used to Restore the Database

  • SQLRNNR

    SSC Guru

    Points: 281243

    Interesting article.

    You could also take advantage of sys.database_recovery_status

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    Will definitely put this information into action the next time.

  • Bill Talada

    SSChampion

    Points: 11956

    Customers generally don't remember how their own backup scheme is set up. Jumping into the history files has helped me know the chain of full and log backups and how I can go about restoring them.

    A more common problem for me is getting tiny backup files from customers and thinking I can restore them on my laptop. Wrong! They might have allocated 50 GB but only have 1 GB of data used.

    I've also had the situation where a full 1 GB database restored but as soon as I did an update it tried to allocate 20 GB of disk according to the database settings for AutoGrowth.

  • RK Mandava

    SSCarpal Tunnel

    Points: 4153

    --this script will give you more details like who restored and backedup....

    SELECT

    DatabaseRestoredTo = RH.destination_database_name,

    TimeOfRestore = RH.restore_date,

    UserImplimentingRestore = RH.user_name,

    RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore'

    WHEN 'F' THEN 'File Restore'

    WHEN 'G' THEN 'Filegroup Restore'

    WHEN 'I' THEN 'Differential Restore'

    WHEN 'L' THEN 'Log Restore'

    WHEN 'V' THEN 'Verify Only'

    END,

    ServerWhereBackupTaken = BS.server_name,

    UserWhoBackedUpTheDatabase = BS.user_name,

    BackupOfDatabase = BS.database_name,

    DateOfBackup = BS.backup_start_date,

    RestoredFromPath = BMF.physical_device_name

    FROM

    msdb.dbo.restorehistory RH

    INNER JOIN

    msdb.dbo.backupset BS

    ON

    RH.backup_set_id = BS.backup_set_id

    INNER JOIN

    msdb.dbo.backupmediafamily BMF

    ON

    BS.media_set_id = BMF.media_set_id

    ORDER BY

    RH.restore_history_id

  • shaun.stuart

    SSCertifiable

    Points: 6750

    If the backup history tables have not yet been cleared, there is an easier way to find out if the restored backup came from a different server that doesn't involve mucking about with LSNs. This information is in the backupset table in msdb in the server_name field. Yes, this table is populated during a restore and yes, when restoring a backup from another server, this column contains the name of the server the backup was from.

    http://shaunjstuart.com/archive/2010/12/msdb-backupset-table-peculiarity/

  • SQLRNNR

    SSC Guru

    Points: 281243

    shaun.stuart (2/27/2014)


    If the backup history tables have not yet been cleared, there is an easier way to find out if the restored backup came from a different server that doesn't involve mucking about with LSNs. This information is in the backupset table in msdb in the server_name field. Yes, this table is populated during a restore and yes, when restoring a backup from another server, this column contains the name of the server the backup was from.

    http://shaunjstuart.com/archive/2010/12/msdb-backupset-table-peculiarity/

    Thanks for that additional info Shaun.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Tushar Kanti

    Ten Centuries

    Points: 1142

    Hi Shaun,

    I tried it out myself this morning to check if the restore entry goes in backupset table but could not succeed in that. I have been using backupset for quite sometime now and have not seen a restore putting an entry there. I went through you blog on this but could find a restore/backup script to replicate the senario. If you could help us with the script to push a restore entry in backupset that will be helpful.

    Thanks,

    Tushar Kanti

  • shaun.stuart

    SSCertifiable

    Points: 6750

    Tushar Kanti (2/27/2014)


    Hi Shaun,

    I tried it out myself this morning to check if the restore entry goes in backupset table but could not succeed in that. I have been using backupset for quite sometime now and have not seen a restore putting an entry there. I went through you blog on this but could find a restore/backup script to replicate the senario. If you could help us with the script to push a restore entry in backupset that will be helpful.

    Thanks,

    Tushar Kanti

    It's very simple - just do a basic backup and restore:

    On Server A:

    BACKUP DATABASE MyDatabase

    TO DISK = '<backup path>\MyDatabase.bak'

    On Server B:

    RESTORE DATABASE MyDatabase

    FROM DISK='<backup path>\MyDatabase.bak'

    Then, on Server B, run this:

    USE msdb

    SELECT server_name,machine_name,database_name,backup_start_date

    FROM backupset

    ORDER BY backup_finish_date DESC

    You will see an entry for MyDatabase with "Server A" in both the server_name and machine_name fields.

    I've just verified this behavior on SQL 2000, 2005, 2008 R2, and 2012. Note that this was done using native T-SQL backup commands. If you are using third party backup software, this behavior may be different.

Viewing 9 posts - 1 through 9 (of 9 total)

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