Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Answered: Automate Daily Restores To Dev SQL Instance

    We have a production database where the developers are constantly requesting me to perform ad-hoc queries to resolve application issues. The develops are in the UK and the DBA team is in FL so we lose a day or more often w/ requests due to our time differences. My question is how can I configure the easiest / most practical way to have nightly full bak's taken from SERVER-A and restore them to a SERVER-A-DEV?

    Right now I'm simply doing a COPY ONLY backup of the prod DB from SERVER-A and then sharing the backup folder to a specific restore user on SERVER-A-DEV and restoring it manually that way.

    Thanks for any help with suggestions!

  2. Best Answer
    Posted by Pat Phelan

    "Without knowing precisely what your server topography, developer permissions, etc. are I can't tell you the "best" solution. The PowerShell script is certainly a very good, definitely adequate, and not materially different than the optimum solution.

    Unless there is a compelling reason to do things another way, I would use a PowerShell script.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would solve this using a single PowerShell script:
    1. Log into source SQL Server
    2. Backup database
    3. Copy file from Source computer to destination computer
    4. Restore file on destination SQL Server
    5. Start researching better ways to fix this

    Note that this is brute force, it may violate your firm's data migration rules (US to UK), and it is just plain nasty for many reasons. There HAS to be a better answer, from a systemic standpoint.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Sorry I didn't clarify but the servers above are all in the UK. It's just the UK dev's don't have access and the US DBA's do. They just suggested we have a nighly restore to a test DB they have sysadmin privileges on. In this scenario, do you still feel the PS script is the best course of action?

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing precisely what your server topography, developer permissions, etc. are I can't tell you the "best" solution. The PowerShell script is certainly a very good, definitely adequate, and not materially different than the optimum solution.

    Unless there is a compelling reason to do things another way, I would use a PowerShell script.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •