backup set id not same in backupset and restorehistory

25112
25112 used Ask the Experts™
on
when I do a backup from the server and do a restore as another db name, i do not see the backupset ids matching between the above 2 views.. how then can we verify that the right version of backup was used for the restore?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
It is your responsibility to make sure you used the correct backup to do a restore.  

[When you do a restore, SQL doesn't attempt to identify in the system tables which backup is being restored based on the original backupset id (as you've discovered).]

You can include a description in your backups, and generate a unique description for every backup.  That would give you another way to verify that you're getting the backup you want.

Author

Commented:
OK- thanks for confirming..

but i do not see any description field in restorehistory?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You can add a description to the *backups*.  You can then check that description before deciding which backup to restore.

Keep in mind, you can restore a backup that was not made on that server or that was deleted from the msdb history long ago.  So SQL really can't match up the restore with a specific backup in some cases.

[I guess they could add a uniqueidentifier value to a backup and match on that during restore, but they chose not to.]
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Author

Commented:
actually the tape backup team has to do the restore.. since we don't do it, there is no way to easily confirm that they did the right job.. that is the reason i was exploring this..
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Hmm.  You could create a dummy table with a datetime that is autoupdated every (n[n]) hour(s), but has no other updates.  Then, upon restore, you could look in the row(s) in that table to determine what time range the backup that was restored was made in.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
For example, say you updated the row every hour with the new datetime.

You wanted a restore from 5PM but the table shows 2PM.

You know they restored a backup from before 5PM.

Author

Commented:
helped.. thanks Scott.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial