SQL 2005 offsite restore

Posted on 2009-12-18
Medium Priority
Last Modified: 2013-11-30
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?

Question by:Butterfield_Cayman
  • 2
  • 2
LVL 35

Expert Comment

by:David Todd
ID: 26093338

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 ...


Author Comment

ID: 26095794
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?
LVL 35

Accepted Solution

David Todd earned 1000 total points
ID: 26097991

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


Author Closing Comment

ID: 31667783
sorry for the delay. help much appreciated and plan is working like a charm

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question