Butterfield_Cayman
asked on
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?
- 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?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry for the delay. help much appreciated and plan is working like a charm
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 ...
HTH
David