SQL 2005 offsite restore

I'm looking for the easiest way to restore my SQL farm from one server to another. Right now, I have a DR SQL box running. We're testing DR scenarios and right now this is our method:
- locate automoated backups of all our production databases
- restore masterdb using safe mode command prompts
- restore other sys databases over the old ones
- restore all other databases over old ones

a couple of probs I have with this method:
- it's time consuming
- for new databases that have been created we get the orphaned users record which we then have to fix.

these aren't deal breakers, but I'm wondering if there's a way that this could be script. Does anyone have any examples of best practices put into place?

Who is Participating?
David ToddConnect With a Mentor Senior DBACommented:

Top down:
A SQL job with step 1 beign to clear the restore directory(s)
Step 2 is call the exe that copies the backup from main to standby. I pass these parameters - path to backup, name of database, path to restore, whether backup plan uses subdirectories.
step 3 is to restore database.
Given that database isn't native to DR server, then need to use the with replace, and possibly the move to specify where the data files go. To do this I look at the existing database, look at the restore database filelistonly output. You might have it easier in a DR setting if your DR server is speced and built exactly the same as production.

Repeat steps 2 & 3 for each database

David ToddSenior DBACommented:

If you are restoring the system databases, how can orphaned users occur?

If there haven't been significant changes, why the need to restore master and system databases?

That is, if you make it a rule that system changes are scripted and applied to both systems, and occassionaly - every month or so - restore the system databases, then it should be simple to automate restore of user databases once a day from most recent production backup.

I do something similar to test servers.

For the latest test server, I wrote a small utility to do the backup file copy between servers - take parameters, find the latest full backup, copy the file, rename as generic database backup ie remove the datetime that gets mangled into the filename ...

Butterfield_CaymanAuthor Commented:
i think orphaned users appear only on new databases - from my tests that what I've found so far anyway.

potentially there could be big significant changes, that's why the restore needs to accomodate all databases.

any code snippets or direction on how to automate the restore of user databases?
Butterfield_CaymanAuthor Commented:
sorry for the delay. help much appreciated and plan is working like a charm
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.