1

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?

Improve this question
9
  • the backup information is in msdb, specifically backupfile, backupfilegroup, backupmediafamily, backupmediaset, backupset. Commented Feb 26, 2015 at 16:46
  • A similar question was recently asked and answered, though not exactly as you ask. See: dba.stackexchange.com/questions/91223/… Commented Feb 26, 2015 at 16:48
  • Oh crap. Maybe that's the deal. Thanks, I'll try restoring msdb and see how that works. Commented Feb 26, 2015 at 16:51
  • Restore it to another name, e.g. msdbII, unless the current msdb is 100% useless to you. Commented Feb 26, 2015 at 16:56
  • If the server you are doing the restores on has agent jobs, log shipping, maintenance plans, etc then you probably do not want to restore msdb as you will lose all the config on store within. A better solution would be to restore as a different name (ie msdb_restore) and write your own queries to figure out the restore sequence you would like to perform. Commented Feb 26, 2015 at 16:59

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • Any pointers on how you are using the data inside msdb to create the text file would be great. I would really prefer not to have rely on file names. Commented Feb 26, 2015 at 17:57

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.