This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Copying SQL Server settings to new server
Question
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?
asked 2019-10-17 by userSteve
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"
answered 2019-10-29 by Andy Mallon