February 7, 2017 at 8:27 am
Hi,
I have a need to completely uninstall and re-install SQL Server from several servers but I'd like to preserve as many of the instance-specific settings as possible. I've done some research and I know there are a handful of scripts out there whose purpose is just that--to extract settings. However, many were written a few years ago. I'm wondering what's the latest and greatest (if there is one). Is there a script that you often use, is it dbatools (I've heard a lot of good things about this), is it a third-party solution (I know Redgate and probably others have something for this)?
Again, it's nothing too complicated that I'm looking to do. Save settings, uninstall SQL Server, re-install SQL Server, re-apply settings. 2016 Standard btw.
Thanks in advance.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 7, 2017 at 10:16 am
script out the sys.configurations table using some dynamic sql will get the server level settings. Depends on what you want to keep
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2017 at 9:58 am
Thanks, Perry. I did a combination of scripting the sys.configurations table and scripting the logins. I think that should do the trick!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 8, 2017 at 12:57 pm
Some other things to consider:
- are there any SQL Agent jobs? You can script those out from the Object Explorer Detail window.
- are there any linked servers? You can script those out from the same window
- are there any stored credentials?
- are there any Database Mail profiles and accounts setup?
- are there any server level triggers?
- are there any special permissions granted in MSDB database to people, such as DatabaseMailUserRole, SQLAgentOperatorRole, etc...
February 8, 2017 at 1:25 pm
Thanks, Chris. Fortunately, only a few of those applied in this scenario. I was able to script most of what I needed. Thanks again.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 9, 2017 at 6:47 am
Hey Mike,
It sounds like you have what you need, but I was wondering about virtualization. Does your shop virtualize SQL Servers?
February 9, 2017 at 6:57 am
Yes, for the most part we do.
Mike Scalise, PMP
https://www.michaelscalise.com
February 14, 2017 at 6:23 am
From the OP it sounds like you are uninstalling and reinstalling SQL Server on the same OS. This just seems a weird thing to do as it is hard to see any benefits outweighing the risks. Can you say what you hope to achieve by doing this.
For me, I would want to do any SQL installation on a fresh install of Windows. I would also want to get everything that connects to SQL to use a DNS alias, so that the SQL instance can be moved to a new server (and possibly upgraded) without impacting any applications.
I am sure you have reasons for what you plan to do, but there may be other solutions that you can offer to the business that provide more benefits for lower risks.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 22, 2017 at 4:08 pm
The dbatools module is becoming a popular tool to use. [Caveat as I'm a major contributor to that module.]
Your need to script out things for a reinstall would also apply to keeping a "backup" of those objects and information as well. Claudio (another contributor) actually published an article today on using dbatools to backup the logins on a given instance.
There is also the Export-DbaSpConfigure to export the sys.configurations data. Some commands at this time I think support piping to "Export-DbaScript" but not all yet. This command just takes the output of a command, that outputs the SMO object, and will just script it to a file. Think along the lines of using the Script Database functionality in SSMS, but with other objects like Logins or Linked Servers. Obviously some objects cannot be fully scripts, like linked servers as they contain a password. So those you would have to treat appropriately on how you back them up.
If you find items that would be beneficial to add into dbatools, by all means, submit an issue to request it or contribute a new function.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 26, 2017 at 3:49 pm
Shawn Melton - Thursday, June 22, 2017 4:08 PMThe dbatools module is becoming a popular tool to use. [Caveat as I'm a major contributor to that module.]Your need to script out things for a reinstall would also apply to keeping a "backup" of those objects and information as well. Claudio (another contributor) actually published an article today on using dbatools to backup the logins on a given instance.
There is also the Export-DbaSpConfigure to export the sys.configurations data. Some commands at this time I think support piping to "Export-DbaScript" but not all yet. This command just takes the output of a command, that outputs the SMO object, and will just script it to a file. Think along the lines of using the Script Database functionality in SSMS, but with other objects like Logins or Linked Servers. Obviously some objects cannot be fully scripts, like linked servers as they contain a password. So those you would have to treat appropriately on how you back them up.
If you find items that would be beneficial to add into dbatools, by all means, submit an issue to request it or contribute a new function.
Shawn,
Thank you for the reply. I'm actually very familiar with dbatools and I've seen a lot of the discussion in the slack channel. My main holdup with dbatools right at this second is that the module isn't digitally signed yet. Once it is, it should adhere to my organization's execution policy and will make a lot of administrative tasks easier--so I'm looking forward to that.
Again, thanks for the suggestion!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply