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.