Startup Stored Procedure -SQL-Server 2005

Startup Stored Procedure -SQL-Server 2005

All of you may aware with Stored Procedure but you may not aware with startup stored procedures which
suppose to run every time SQL-Server’s services restart.

This task may be useful for some administrative task, i.e.: you may know that TempDB will be recreated from scratch whenever server restarts. You may want some user to grant permission of TempDB every time it is being created.

Before you start generating startup stored procedure, have a look at some basic ground regarding that.

You have to make sure that ‘Scan for Startup Proc’ option is set to 1. You can see that option and make it disable and enable with following queries.

--this will show you all advance option

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE

--this will enable startup procedure

EXEC sp_configure 'scan for startup procs', '1';

RECONFIGURE

Now, you can create stored procedure and it will be set as startup proc, before you create it, you have to follow some strict guideline.

--Procedure should be reside in MASTER database
--Its owner should be DBO
--Mark your stored procedure as startup stored procedure with sp_procoption
--Only sysAdmin can set sp_procoption
--Your procedure shouldn’t have any input parameter or return any output parameter

Now, we will create one stored procedure and will mark it as startup procedure.

--Creating strored procedure

CREATE PROC spCreateDatabaseAtStartup

AS

EXEC('CREATE database StartupDatabase')

GO

--Marking SP as startup

exec sp_procoption N'spCreateDatabaseAtStartup', 'startup', 'on'

In sp_procoption first parameter is our stored procedure name. Second parameter is “OptionName” but fortunately or unfortunately there is only one option name “startup” J and third parameter is “OptionValue” that should be “on” or “off”.

Reference: Ritesh Shah