4

One of our devs backed up a dev database, and then restored it in production. It's a new database for a new app that was deployed last night intentionally to prod.

Now in the backupset table (msdb.dbo.backupset) on prod, I can see a record for the dev database, with a backup start date time of when the restore was done.

Record from prod backupset table:

name:         DatabaseName_UAT-Full Database Backup
server_name:  COMPNAME-SQLDEV02
machine_name: COMPNAME-SQLDEV02

I would not expect to see this record. Can anyone explain why restore would insert into the backupset table on prod?

Can I delete this record from the msdb.dbo.backupset table? Or not such a good idea?

1 Answer 1

5

"It just does". Its just meta data about the backup.

The Books Online article http://msdn.microsoft.com/en-us/library/ms186299.aspx hints at it, albeit not very well in the "Remarks" section. From that article you'll see a reference to sp_delete_backuphistory. So yes you can safely remove it if you need to.

The info that goes into that backupset table, from a restore, is pulled out of the backup itself. Run:

RESTORE HEADERONLY FROM DISK = 'Path to backup file';

and you'll find the information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.