SQL Server General Setup Script

This script is an example of how to setup some of the repetitive settings most database administrators change on their SQL Servers. Database administrators should change the values in this script to meet their needs. File paths, drive letters, email addresses, and many other settings will need to be changed. This script is a good starting point that a database administrator can build on.

This script sets up:

  • Max Degree of Parallelism based on number of CPU’s
  • Cost Threshold for Parallelism
  • Max and Min Memory based on RAM installed
  • Remote Administrator is turned on
  • Alerts
  • Operators
  • Database Mail
  • Alert Notifications
  • Changes SQL Agent Service User Account (This could be tweaked to change the SQL Server Service account as well)
  • Restarts SQL Agent Service so that database mail works
  • Sets Backup Compression default to true.
  • Sets up Temdb multiple files, initial file size, and file growth.
  • Sets up model database
  • Sets default Data, Log, and Backup folders

 

/*************************************************************************
Purpose			:  General SQL Server Setup Example Script to change SQL Defaults to your own recommended defaults
				   MAXDOP (based on number of CPU's),Cost Threshold for Parallelism, MAX Memory (Based on installed RAM),
				   Min Memory (Based on installed RAM), Alerts, Operators, Fail Safe Operator, Tempdb setup, Model Database defaults

COMMENT			:  Review this script and change any settings to your desired defaults ,file paths, and email addresses.
*************************************************************************/

EXEC dbo.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO	

DECLARE @CostThresholdForParallelism NVARCHAR(10)
		,@memForApplicationsGB DECIMAL(9,2)
/*************************
--Configuration Settings Please Change as Desired*/
--Please set Cost Threshold for Parallelism to your desired amount 20 - 50 is usually a good starting point for transactional databases
SET @CostThresholdForParallelism='50'
--Please Set your Own Custom Value in Gigabytes for applications that run on SQL Server for example reporting services
SET @memForApplicationsGB = 0





/*************************************************************************
Author (For Max Dop Settings)         :   Kin Shah 
Purpose         :   Recommend MaxDop settings for the server instance
Tested RDBMS    :   SQL Server 2008R2
--http://dba.stackexchange.com/questions/36522/what-is-a-good-repeatable-way-to-calculate-maxdop-on-sql-server
**************************************************************************/
declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int
DECLARE @MAXDOP INT

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case 
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
group by parent_node_id
option (recompile);

select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes 
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64

-- Report the recommendations ....
select
    --- 8 or less processors and NO HT enabled
    case 
        when @logicalCPUs < 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
                --- 8 or more processors and NO HT enabled
        when @logicalCPUs >= 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : 8'
                --- 8 or more processors and HT enabled and NO NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA = 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
                --- 8 or more processors and HT enabled and NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA > 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
        else ''
        end as Recommendations
		
-- Assign the recommendations ....
select
    --- 8 or less processors and NO HT enabled
    @MAXDOP = case 
        when @logicalCPUs < 8
            and @HTEnabled = 0
            then @logicalCPUs--'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
                --- 8 or more processors and NO HT enabled
        when @logicalCPUs >= 8
            and @HTEnabled = 0
            then 8 --'MAXDOP setting should be : 8'
                --- 8 or more processors and HT enabled and NO NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA = 1
            then CAST(@logicalCPUPerNuma / @physicalCPU AS INT)--'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
                --- 8 or more processors and HT enabled and NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA > 1
            then CAST(@logicalCPUPerNuma / @physicalCPU AS INT) --'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
        else ''
        end 
		
	
-- ***Set Max Degree of Parallelism based on Number of CPUs***
EXEC dbo.sp_configure 'max degree of parallelism', @MAXDOP;
--***Set cost threshold for parallelism***
EXEC sp_configure 'cost threshold for parallelism', @CostThresholdForParallelism;
GO
RECONFIGURE;
GO







--Sets SQL Server Max Memory
--Make sure and Modify @memForApplicationsGB below
/*************************************************************************
Author (For Max Server Memory Settings)         :   Tibor Karaszi
Purpose         :   Recommend Max/Min Memory settings for the server instance
Tested RDBMS    :   SQL Server 2008R2
Modified by		: Preston  
					-Added @memForApplicationsGB variable and logic
--http://sqlblog.com/blogs/tibor_karaszi/archive/2014/03/06/setting-max-server-memory.aspx
**************************************************************************/
IF OBJECT_ID('tempdb..#mem') IS NOT NULL DROP TABLE #mem
GO
DECLARE 
	@memInMachine DECIMAL(9,2)
	,@memOsBase DECIMAL(9,2)
	,@memOs4_16GB DECIMAL(9,2)
	,@memOsOver_16GB DECIMAL(9,2)
	,@memOsTot DECIMAL(9,2)
	,@memForSql DECIMAL(9,2)
	,@CurrentMem DECIMAL(9,2)
	,@CurrentMin DECIMAL(9,2)
	,@sql VARCHAR(1000)
	,@mem_final VARCHAR(10)
	,@memMinForSQL DECIMAL(9,2)
	,@memMin_final VARCHAR(10)
	,@memForApplicationsGB DECIMAL(9,2)

--Please Set your Own Custom Value in Gigabytes for applications that run on SQL Server for example reporting services
SET @memForApplicationsGB = 0

CREATE TABLE #mem(mem DECIMAL(9,2))

--Get current mem setting----------------------------------------------------------------------------------------------
SET @CurrentMem = (SELECT TOP 1 CAST(value AS INT)/1024. FROM sys.configurations WHERE name = 'max server memory (MB)')
SET @CurrentMin = (SELECT TOP 1 CAST(value AS INT)/1024. FROM sys.configurations WHERE name = 'min server memory (MB)')
--Get memory in machine------------------------------------------------------------------------------------------------
IF CAST(LEFT(CAST(SERVERPROPERTY('ResourceVersion') AS VARCHAR(20)), 1) AS INT) = 9
  SET @sql = 'SELECT physical_memory_in_bytes/(1024*1024*1024.) FROM sys.dm_os_sys_info'
ELSE 
   IF CAST(LEFT(CAST(SERVERPROPERTY('ResourceVersion') AS VARCHAR(20)), 2) AS INT) >= 11
     SET @sql = 'SELECT physical_memory_kb/(1024*1024.) FROM sys.dm_os_sys_info'
   ELSE
     SET @sql = 'SELECT physical_memory_in_bytes/(1024*1024*1024.) FROM sys.dm_os_sys_info'

SET @sql = 'DECLARE @mem decimal(9,2) SET @mem = (' + @sql + ') INSERT INTO #mem(mem) VALUES(@mem)'
PRINT @sql
EXEC(@sql)
SET @memInMachine = (SELECT MAX(mem) FROM #mem)

--Calculate recommended memory setting---------------------------------------------------------------------------------
SET @memOsBase = 1

SET @memOs4_16GB = 
  CASE 
    WHEN @memInMachine <= 4 THEN 0
   WHEN @memInMachine > 4 AND @memInMachine <= 16 THEN (@memInMachine - 4) / 4
    WHEN @memInMachine >= 16 THEN 3
  END

SET @memOsOver_16GB = 
  CASE 
    WHEN @memInMachine <= 16 THEN 0
   ELSE (@memInMachine - 16) / 8
  END

SET @memOsTot = @memOsBase + @memOs4_16GB + @memOsOver_16GB
SET @memForSql = @memInMachine - (@memOsTot + @memForApplicationsGB)
SET @mem_final = CAST(CAST(@memForSql * 1024 AS INT) AS VARCHAR(10))

SET @memMinForSQL = @memForSql * 0.4
SET @memMin_final = CAST(CAST(@memMinForSQL * 1024 AS INT) AS VARCHAR(10))
--Output findings------------------------------------------------------------------------------------------------------
SELECT
@CurrentMem AS CurrentMemConfig
,@CurrentMin AS CurrentMin
, @memInMachine AS MemInMachine
, @memOsTot AS MemForOS
, @memForSql AS memForSql
,@memMinForSQL AS memMinForSQL
,@mem_final AS mem_final
,@memMin_final AS memMin_final
,'Assumes dedicated instance. Only use the value after you verify it is reasonable.' AS Comment

--***Set Max and Min Server Memory Based on Available Memory***
EXEC sp_configure 'max server memory (MB)',@mem_final
EXEC sp_configure 'min server memory (MB)',@memMin_final
GO
RECONFIGURE
GO



--Set Default Compression to True for Backups
EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE WITH OVERRIDE;
GO






--Enable Remote Admin Connection
EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO









--Setup Database Mail
USE [master]
GO
EXEC sp_configure 'Database Mail XPs',1
GO
RECONFIGURE 
GO
-- Create a New Mail Profile for Notifications
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'DBA_Notifications',
       @description = 'Profile for sending Automated DBA Notifications'
GO
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'DBA_Notifications',
    @principal_name = 'public',
    @is_default = 1 ;
GO
-- Create an Account for the Notifications (Change @[EmailDomain] to desired email domain name below, and [SMTPServerHostName] to desired SMTP server and remove the brackets below)
DECLARE @dbmailEmailAddress NVARCHAR(128)
SET @dbmailEmailAddress = @@SERVERNAME + '@[EmailDomain]'

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQLMonitor',
    @description = 'Account for Automated DBA Notifications',
    @email_address = @dbmailEmailAddress,  -- Change This
    @display_name = @@SERVERNAME,
	@replyto_address= 'noreply@[EmailDomain]',
	@port = 25,
	@mailserver_name = '[SMTPServerHostName]'  -- Change This
GO
-- Add the Account to the Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'DBA_Notifications',
    @account_name = 'SQLMonitor',
    @sequence_number = 1
GO


--Add Alert Operator  (Change [databaseadministrator@EmailDomain] to desired email address and remove the brackets)
USE [msdb]
GO
IF  EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'Database Administrator')
EXEC msdb.dbo.sp_delete_operator @name=N'Database Administrator'
GO
EXEC msdb.dbo.sp_add_operator @name=N'Database Administrator', 
		@enabled=1, 
		@weekday_pager_start_time=90000, 
		@weekday_pager_end_time=180000, 
		@saturday_pager_start_time=90000, 
		@saturday_pager_end_time=180000, 
		@sunday_pager_start_time=90000, 
		@sunday_pager_end_time=180000, 
		@pager_days=0, 
		@email_address=N'[databaseadministrator@EmailDomain]', 
		@category_name=N'[Uncategorized]'
GO


--Create Alerts (regarding major sql server errors)
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 25 Error - Fatal Error', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 24 Error - Hardware Error', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 23 Error - Database Integrity Suspect', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 22 Error - Table Integrity Suspect', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 21 Error - Fatal Error in Database Processes', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 20 Error - Fatal Error in Current Process', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 19 Error - Fatal Error in Resource', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 18 Errors - Non Fatal Internal Error', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev. 17 Error - Insufficient Resources', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error - 9100 (Index Corruption)', 
		@message_id=9100, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=7, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Corruption: Read/Retry 825', 
		@message_id=825, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=5, 
		@notification_message=N'This is where either an 823 or 824 occured, SQL server retried the IO automatically and it succeeded. This error is written to the errorlog only - you need to be aware of these as they''re a sign of your IO subsystem going awry. There''s no way to turn off read-retry and force SQL Server to ''fail-fast'' - whether this behavior is a good or bad thing can be argued both ways - personally I don''t like it', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'824 - Soft I/O Corruption', 
		@message_id=824, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=5, 
		@notification_message=N'This is where the OS could read the page but SQL Server decided that the page was corrupt - for example with a page checksum failure', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'823 - Hard I/O Corruption', 
		@message_id=823, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=900, 
		@include_event_description_in=5, 
		@notification_message=N'This is where SQL Server has asked the OS to read the page but it just cant', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO


--Add Notifications to Alerts
USE msdb ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'823 - Hard I/O Corruption',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'824 - Soft I/O Corruption',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Corruption: Read/Retry 825',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Error - 9100 (Index Corruption)',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 17 Error - Insufficient Resources',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 18 Errors - Non Fatal Internal Error',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 19 Error - Fatal Error in Resource',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 20 Error - Fatal Error in Current Process',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 21 Error - Fatal Error in Database Processes',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 22 Error - Table Integrity Suspect',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 23 Error - Database Integrity Suspect',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 24 Error - Hardware Error',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO
EXEC dbo.sp_add_notification
 @alert_name = N'Sev. 25 Error - Fatal Error',
 @operator_name = N'Database Administrator',
 @notification_method = 1 ;
GO


  
--Set SQL Agent Fail Safe Operator  
EXEC master.dbo.sp_MSsetalertinfo
@failsafeoperator=N'Database Administrator', -- modify to your operator here
@notificationmethod = 1; -- notify using email

--Turn on cmd shell in SQL Server for use in this script it is turned off later  
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
  
--Set Domain Accounts for SQL Server Service and SQL Server Agent Service (Remove Brackets Once you change the values)
EXEC xp_cmdshell 'cmd /C sc.exe config "SQLSERVERAGENT" obj= "[DOMAIN\DomainSQLServiceAccount]" password= "[PasswordForDomainAccount]" '  

--Restart Agent Service Account for Database Mail Settings to Take Effect and for Domain Account as well
EXEC xp_cmdshell 'cmd /C sc.exe stop "SQLSERVERAGENT" ' 
WAITFOR DELAY '00:00:20'
EXEC xp_cmdshell 'cmd /C sc.exe start "SQLSERVERAGENT" ' 
WAITFOR DELAY '00:00:15'


--Test Database Mail by sending an email (Change [databaseadministrator@EmailDomain] to your email address and remove the brackets )
DECLARE @EmailBody NVARCHAR(MAX),@EmailSubject NVARCHAR(255)
SET @EmailBody = 'This is a test e-mail sent from Database Mail on ' + @@SERVERNAME
SET @EmailSubject = @@SERVERNAME + ' Database Mail Test'
 USE [msdb]
    EXEC sp_send_dbmail
      @profile_name = 'DBA_Notifications',
      @recipients = '[databaseadministrator@EmailDomain]',
      @subject = 'Database Mail Test on',
      @body = @EmailBody



	  


	  
	  
--TEMPDB Setup (correctly size tempdb and add additional file per cpu up to 8)
--Make Sure all files are setup the same size and growth
--Change the File Paths as desired
--Change the MAXSIZE as desired using the max size for the smallest drive used for any/all of the tempdb files
ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', SIZE = 1024MB, FILEGROWTH = 256MB, MAXSIZE = UNLIMITED);

--Example how to move Tempdb to another drive letter (SQL Instance Restart Required)
--ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev',FILENAME = 'D:\Data\tempdb.mdf' SIZE = 1024MB, FILEGROWTH = 256MB, MAXSIZE = UNLIMITED);

--Example on how to move the Tempdb log file (SQL Server Service restart required)
--ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = '[D:\Data\templog.ldf]' SIZE = 1024MB, FILEGROWTH = 256MB, MAXSIZE = UNLIMITED)
GO

--This adds new tempdb files for each CPU change paths/drive letters as desired (SQL Server Service restart required)
--SET Max Size as desired
ALTER DATABASE tempdb ADD FILE (NAME = N'tempdev2', FILENAME = N'[D:\Data\tempdev2.ndf]', SIZE = 1024MB, FILEGROWTH = 256MB, MAXSIZE = UNLIMITED)
GO
ALTER DATABASE tempdb ADD FILE (NAME = N'tempdev3', FILENAME = N'[D:\Data\tempdev3.ndf]', SIZE = 1024MB , FILEGROWTH = 256MB, MAXSIZE = UNLIMITED)
GO
ALTER DATABASE tempdb ADD FILE (NAME = N'tempdev4', FILENAME = N'[D:\Data\tempdev4.ndf]', SIZE = 1024MB , FILEGROWTH = 256MB, MAXSIZE = UNLIMITED)
GO






--Model Database Setup
ALTER DATABASE model MODIFY FILE (NAME ='modeldev',SIZE = 1024MB, FILEGROWTH = 256MB, MAXSIZE = UNLIMITED);
--Change the model database to the desired default recovery model SIMPLE or FULL
ALTER DATABASE model SET RECOVERY SIMPLE WITH NO_WAIT

 

 
 --Set Database Default Folder Paths (Change the folders below to default desired drive letters and folders)
USE [master]
GO
DECLARE
		@DefaultDataFolder NVARCHAR(512)
		,@DefaultLogFolder NVARCHAR(512)
		,@DefaultBackupFolder NVARCHAR(512)
SET @DefaultDataFolder = '[C:\SQLData]'
SET @DefaultLogFolder = '[C:\SQLLog]'
SET @DefaultBackupFolder = '[C:\SQLBackup]'


--Data file
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, @DefaultDataFolder
-- Log file
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, @DefaultLogFolder

/*
--SQL Server 2008 R2 Set Default Backup Folder
EXEC master..xp_regwrite 
     @rootkey='HKEY_LOCAL_MACHINE', 
     @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer', 
     @value_name='BackupDirectory', 
     @type='REG_SZ', 
     @value=@DefaultBackupFolder
*/
/*
--SQL Server 2012 Set Default Backup Folder
EXEC master..xp_regwrite 
     @rootkey='HKEY_LOCAL_MACHINE', 
     @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer', 
     @value_name='BackupDirectory', 
     @type='REG_SZ', 
     @value=@DefaultBackupFolder
*/

--Verify Folders were Changed
declare @DefaultData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData output

declare @DefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog output

declare @DefaultBackup nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup output

declare @MasterData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output
select @MasterData=substring(@MasterData, 3, 255)
select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))

declare @MasterLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output
select @MasterLog=substring(@MasterLog, 3, 255)
select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))
select 
    isnull(@DefaultData, @MasterData) DefaultData, 
    isnull(@DefaultLog, @MasterLog) DefaultLog,
    isnull(@DefaultBackup, @MasterLog) DefaultBackup
	  



	  
	  
	  
--Turn off cmd shell in SQL Server	  
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

One thought on “SQL Server General Setup Script

  1. Pingback: Microsoft SQL Server DBA Setup Checklist and Recommendations | Question Driven

Leave a Reply

%d bloggers like this: