SQL Server: SQL Build post script or Script to be executed after SQL SERVER Installation

Post scripts to implement after successful SQl Server installation in a new server:

1. Listing the advanced configuration options and RUN the reconfigure statement to install.

 

use master go sp_configure 'show advanced options',1 go reconfigure with override go

2. Enable the Database Mail XPs.

sp_configure 'Database Mail XPs',1 --go --sp_configure 'SQL Mail XPs',0 go reconfigure go

3. Mail account settings

a).Create an account for the notifications(set the email account, mail server and user credentials as per your environment) EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQL', @description = 'DBA Email account', @email_address = ' sqlalerts@gmail.com', @display_name = ' SQL DBA Alerts', @username='', @password='',   @mailserver_name = 'smtp.gmail.com'

b).Create a new mail profile. EXECUTE msdb.dbo.sysmail_add_profile_sp            @profile_name = 'mssql',            @description = 'Profile used for  emails in database mail'  c).Setting the profile the default public profile. This script sets the profile mssql as default public profile for users in the msdb database. EXECUTE msdb.dbo.sysmail_add_principalprofile_sp     @profile_name = 'mssql',     @principal_name = 'public',     @is_default = 1 ;  d).Add the mapping of profile ‘mssql’ and account ‘SQL’. EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'mssql', @account_name = 'SQL', @sequence_number = '1' --------------------------

4. This script sets the operator information for DBA and operator is enabled; SQL server agent sends notifications to the mentioned email address by pager from Monday 9AM to Friday 6PM, Saturday 9AM to 6PM and Sunday 9AM to 6PM.

USE [msdb] GO /****** Object:  Operator [DBA]    Script Date: 12/21/2011 10:39:14 ******/ EXEC msdb.dbo.sp_add_operator @name=N'DBA',         @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=127,         @email_address=N'sqlalerts@gmail.com',         @category_name=N'[Uncategorized]' GO

That’s all. Database mail is enabled and ready to use by the new SQL server.

5. This script will put a copy of the email in the sent folder for the sending email address you have provided if it is set to true or 1

USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1 GO

6. This script will enable the SQL Server Agent to use the Database Mail Profile by updating the settings in the registry as shown below.

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1 GO EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'mssql' GO

7. This script will enable the CLR integration; Sql Server stops executing all CLR routines and loads all application domains.

sp_configure 'clr enabled', 1 go reconfigure go

8. Check the mail profile and mail account is created successfully or not by the following script.

SELECT * FROM msdb.dbo.sysmail_profile SELECT * FROM msdb.dbo.sysmail_account

Have a Happy post script implementation in a new SQL Server. Then consider Maintenance plan setup in this new SQL server environment.