Cloning live servers to create 'pre-production' SQL servers RRS feed

  • Question

  • Hi,

    We've been having some trouble setting up pre-production environments based on clones of our live environments. The thought was that we could use a copy of the live server, rename it and bring it online as a pre-production server and rename anything on the SQL side to re-establish any links. I believe it would follow the below process;


    The issue is that after the host rename, the SQL services all stop running and cannot be started, so the above cannot be completed as there's nothing to connect to and run the T-SQL. Are we missing something in the order we are completed this or our understanding of creating these copies? Or could it be the username against the service since we've changed host names? We use windows authentication if that makes a difference to the process/settings.

    I know we could create a server from scratch, install SQL server, and use replication to copy and maintain the data as this is pretty much how we have been creating a Developer environment - however the goal is to make sure the pre-production environment as (ideally) an identical copy to live in every way - including the build/install and not just the data.
    We were concerned this method might leave us having used different install options or components to the live version that could cause issues later on when work is deployed to live as there could be more variables. If this is the correct/only method then could there be a report that can be run on live to establish these options and components to allow us to manually build an accurate copy?
    As you may guess, there was no previous management of this and we have no documentation for any environments.
    Thank you for any help and advice.
    • Edited by lemming3k Monday, April 27, 2020 1:30 AM
    Friday, April 24, 2020 4:39 PM

All replies

  • Hi lemming3k,

    You can try to use Windocks to clone live product data for testing. Please refer to Windocks; Database cloning for SQL Server dev/test on “live” production data to get more information.

    Hope this could help you.

    Best regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, April 27, 2020 8:20 AM
  • The SQL Server engine should be running after the rename.  It does not depend on the server name.

    Look at the SQL Server log file to see why it is not starting.

    Monday, April 27, 2020 1:48 PM
  • The SQL Server engine should be running after the rename.  It does not depend on the server name.

    Look at the SQL Server log file to see why it is not starting.

    Thanks, that's what I had assumed.

    Looks like we had a missing drive on the clone. The SQL DBs and logs (including the server log file) are set to store on the secondary drive. Although SQL itself was installed on C: Shouldn't that allow the service to start even if it can't load any data?

    Restoring the drive allowed one instance to start but not the other. We now have error logs though;

    "Error 5(failed to retrieve text for this error. Reason:15100) occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options and correct or remove them if necessary."
    Monday, May 4, 2020 2:26 PM
  • Error 5 is access denied.  The SQL Server service account must have full rights to the directories where the data and logs are stored.

    Monday, May 4, 2020 4:08 PM
  • Shouldn't those rights have copied with the clone? Are there any reasons why only one instance permissions would be copied?
    Tuesday, May 5, 2020 2:13 PM