I have a SQL Server database that is running these scripts to create backups. It's running full backups once a week, daily differentials, and hourly logs. These are being FTPd to another box where I would like to be able to restore them for verification purposes. Right now I'm doing this simply hand picking the appropriate backups using the names of the files, which have the date/time encoded into them. That seems pretty hacky to me.
On the main SQL Server box where the backups are being created, I can open SMS, right click on the database, and select
Tasks -> Restore -> Database
I get a nice grid listing of options including the full, diffs, and logs that I can restore.
I can't seem to recreate this on the remote box though. I figure that the other SQL Server probably doesn't have the metadata required to create this list since the backups didn't originate from that box. Is there anyway to restore that meta data as well? I tried restoring msdb but it doesn't seem to help.
Here are the steps I'm taking:
(1) Restore the master database on the new server.
(2) Restore the msdb database on the new server.
(3) Restore the full backup of the database on the new server with
RESTORE DATABASE AwesomeDatabase99 FROM DISK = N'C:\Path\To\FULL_20150226_111927.bak' WITH MOVE N'AwesomeDatabase99' TO N'C:\Path\To\AwesomeDatabase99.mdf', MOVE N'AwesomeDatabase99_log' TO N'C:\Path\To\AwesomeDatabase99_1.ldf', NORECOVERY GO
I was hoping at this point I would be able to get the full grid of restore options containing the diffs and logs inside MSM but it still doesn't want to show up. The paths to the backup file are the exact same on the new server as they were on the server where they were created.
It seems like the system should be able to take all the backup files and tell me which ones go together. Is that possible?