1

Let's say I was monkeying around on SQL Server and I wanted to reset everything to factory defaults, is that currently possible?

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'stupid dangerous thingz', 1

I want to do something like

EXEC sp_factory_reset, 1
Improve this question
4
  • AFAIK there isn't anything to reset to defaults. Your best option, currently, is to take the output of sys.configurations on a new install and create a cheat sheet. Obviously this can be made into your own reset stored procedure. Note that some options are also controlled through alter server configuration. In general, though, there shouldn't be a need to change most options as their default is 0 which is to change dynamically.
    – Sean Gallardy
    Dec 18, 2017 at 20:37
  • 3
    sp_blitz will tell you if you have non-default configurations and what the default value is in the details. brentozar.com/blitz
    – Bob Klimes
    Dec 18, 2017 at 20:54
  • 1
    Microsoft documentation contains a complete list of config options and their default values learn.microsoft.com/en-us/sql/database-engine/configure-windows/… You could also use the Export/Import funcationality of the opensource DBATools.io project to set and reset them dbatools.io/functions/export-dbaspconfigure
    – Nic
    Dec 18, 2017 at 21:51
  • 1
    @BobKlimes seriously that's the best suggestion I've seen. If you want to answer with that, I'll mark it as chosen. That's the best tool for the job I see being offered here.
    – Evan Carroll
    Dec 18, 2017 at 22:40

2 Answers 2

Reset to default
4

sp_blitz will show you all non-default configuration settings and the details show what the default is.

enter image description here

Another option would be to use dbatools to export the config on a fresh install, using export-dbaspconfigure. Then import when you want to reset, using import-dbaspconfigure.

Export-DbaSpConfigure -ServerInstance . -path c:\temp\sp_configure.sql

This will create a file with all sp_configure options in this format

EXEC sp_configure 'show advanced options' , 1;  RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'recovery interval (min)' , 0;
EXEC sp_configure 'allow updates' , 0;
EXEC sp_configure 'user connections' , 0;
EXEC sp_configure 'locks' , 0;
EXEC sp_configure 'open objects' , 0;
EXEC sp_configure 'fill factor (%)' , 0;
EXEC sp_configure 'disallow results from triggers' , 0;

Then you can either run this file or the import-spdbaconfigure command, which will run the script for you

import-DbaSpConfigure -ServerInstance . -path c:\temp\sp_configure.sql
Improve this answer
1
  • I wanted to actually download, install, and run this before I marked this as chosen. I do like this sp_blitz tool though.
    – Evan Carroll
    Dec 23, 2017 at 20:13
1

eg this will emit a script based on the configuration of an instance that can be applied on another instance.

select 'exec sp_configure ''show advanced options'', 1;'
union all
select concat('exec sp_configure ',' ''', name, ''', ', cast(value as varchar(200) ), ';' )
from sys.configurations
union all 
select 'exec sp_configure ''show advanced options'', 0;'
union all
select 'reconfigure;'
Improve this answer
1
  • 2
    My problem is I changed all kinds of crap. It's how I run. Now I want to know if I broke it or if it was born this way. ;)
    – Evan Carroll
    Dec 18, 2017 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.