How to create backup of a stored procedure for MS SQL Server

Products

• IT Management Suite

Issue/Introduction

How to create backup of a stored procedure for MS SQL Server 2008 database.

Environment

ITMS 8.x

Resolution

Before making a change to a stored procedure please use the following steps to get a backup copy of the code.

In all cases, it is recommended to have a FULL backup of your DB.

1. Run SQL management Stuido > connect DB instance & Right Click DB
2. Select Tasks > Generate Scripts
    This launches Generate SQL Script Wizard launches.

3. Select DB & Continue to the next screen
4. Select Store Procedures
5. From the list, select the stored procedure you require a backup.
6. select Script to new query window
7. Copy the code
8. To re-create this stored procedure simply replace the line
CREATE PROCEDURE [dbo].[spEXAMPLE] with ALTER PROCEDURE [dbo].[spEXAMPLE]