Sql-server – Restoring SQL Server database on another box

backuprestoresql server

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.

enter image description here

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 NORECOVERY 

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?

Best Answer

What Bob said.

Couple of ways to get round this.

Firstly, you could copy the files from the FTP server to your test server and then use xp_cmdshell and the DIR command to read in the contents of that folder (full, diff and log backups) and order them accordingly (you need the date/time in the filename to achieve this). Once you have this information, you can dynamically build your restore statements.

I did this once, but, I have xp_cmdshell disabled in my environment here, so it's not an option.

The way I currently do it. I build the restore statements based on the metadata in msdb and export that out to a text file which is stored on the FTP server. I simply read that text file on along with my backup files on my test server and away I go.

My solution performs a point in time on a daily basis to my test box. What it can't guarantee is if the subsequent log backups are valid and I can restore from those.

I also perform manual restores to my test box (another instance) just to keep myself fresh when it comes to a disaster - I vary the restores too.

Related Question