Identify the Backup used to Restore the Database

Question

Post reply

Identify the Backup used to Restore the Database

Tushar Kanti

Ten Centuries

Points: 1143
More actions
February 27, 2014 at 12:03 am

#282950

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

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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: 1143 More actions · Answer 2

Will definitely put this information into action the next time.

Bill Talada SSChampion Points: 11954 More actions · Answer 3

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 More actions · Answer 4

--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: 7031 More actions · Answer 5

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: 281334 More actions · Answer 6

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: 1143 More actions · Answer 7

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: 7031 More actions · Answer 8

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.