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
sp_configure 'show advanced options',1
reconfigure with override

2. Enable the Database Mail XPs.

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

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',
  @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]
/****** Object:  Operator [DBA]    Script Date: 12/21/2011 10:39:14 ******/
EXEC msdb.dbo.sp_add_operator @name=N'DBA',

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]
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1

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
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'mssql'

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

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.


Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)