Use sp_procoption to execute a stored procedure automatically at the time of starting SQL server. The stored procedure that needs to be executed should be created in the master database and owned by a member of the sysadmin role. You can use this procedure to automate tasks like backups and index recreation and any other database maintenance tasks. To make a STORED PROCEDURE auto executable at startup, first login to the master database as SA. Then create a stored procedure (say ‘xxx’) and run this SQL:
sp_procoption 'xxx','startup',true
To check whether a procedure is automatically set to execute, run this SQL:
sp_procoption 'xxx','startup'
To get the status of all stored procedure in the master database for automatic execution, run this SQL:
sp_procoption null,'startup'