Home / server / Questions / 988391
Accepted
userSteve
Asked: 2019-10-18 05:26:43 +0800 CST

Copying SQL Server settings to new server

  • 772

We are a small business using a single SQL Server 2014 instance as our main data platform.

We now need to set up a second instance (2017) to hold additional data - this is to be standalone, ie not replicated or actively linked to the main server.

Disclaimer: I am a developer not a DBA. Using a trained DBA for this task is out of our budget.

I can happily install the software and try my best at setting it up correctly but ideally it would be great if there is a way to copy the majority of the settings from the main server to the new one.

We are happy with the way the main SQL instance is set up, is it possible to export and import these settings? Is there an easy to follow guide for creating a new SQL instance based on similar configuration of an existing one?

sql-server

3 Answers

  1. Best Answer

    The dbatools PowerShell module can do this for you. The module has a long list of commands, which can copy configurations, logins, databases--essentially an entire instance from one machine to another. You can choose to copy everything, or just a subset.

    You probably want to at least copy configurations, DB Mail config, and probably some or all of your SQL Agent Jobs. Jobs may be an important thing to copy because they likely contain backup & other maintenance jobs that are going to be critical to your server health.

    From the PowerShell prompt, you would do something like this--Note, I've included a few different examples for copying SQL Agent jobs:

    # Install the module
Install-Module dbatools

# Import the module
Import-Module dbatools

# Copy configurations
Copy-DbaSpConfigure -Source "OldServer" -Destination "NewServer"

# Copy DB Mail
Copy-DbaDbMail -Source "OldServer" -Destination "NewServer"

# Copy All SQL Agent Jobs
Copy-DbaAgentJob -Source "OldServer" -Destination "NewServer"

# Copy a single SQL Agent Job
Copy-DbaAgentJob -Source "OldServer" -Destination "NewServer" -Job "Weekly Backups"

# Copy everything except two SQL Agent Jobs
Copy-DbaAgentJob -Source "OldServer" -Destination "NewServer" -ExcludeJob "Job1", "Job2"
    • 3

  2. There is no real way to automate setting instance configuration settings that I am aware of without maybe some 3rd party tools, however, you could use this query, capture the results, then make the configuration changes on the new instance once installed to match manually via SSMS or T-Sql using those same settings.

    Select * from sys.configurations order by name;
    • 1

  3. No, there is none. Not really. There are not that many settings to start with.

    The problem is that there likely never will be any solution either because anyone doing multi computer installations will have those totally scripted, so you do not COPY settings over, you configure all machines via script using the same settings. You install, i.e. SQL Server using DRSC / Chocolatey and then run a script to set the settings.

    • 0