SQL 2005 offsite restore

Posted on 2009-12-18
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
    LVL 35

    Expert Comment

    by:David Todd

    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

    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


    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now