Answer

Five steps for a successful backup of a stored procedure in SQL Server

How can I back up a stored procedure in SQL Server?

    Requires Free Membership to View

Community member WillC suggests using Management Studio for SQL 2005 for backing up a SQL Server stored procedure. Follow these five steps:

  1. Drill to the database that has the stored procedures and right-click the database name.
  2. From the Task menu option, select Generate Scripts to launch the wizard.
  3. Click Next. Make sure the database you want is selected and click Next, then Next again. You should be on the Choose Object Types page.
  4. Select Stored Procedures, which brings you to the Choose Stored Procedures page, where you can select the procedures that you want.
  5. Click Finish to begin the process; when it finishes, you will have a new query window with a Create script that you can save.

You can also navigate to Database>Programming>Storage Procedures, and in the Object Explorer Details page, select all the procedures; then right-click and select Script Stored Procedure As>Create To>New Query Window (or select Script Stored Procedure As>Create To>File, if you like).

According to SearchSQLServer.com expert Greg Robidoux, you might want to consider SQL Server Maintenance Plans to help with backups, particularly if you're new to SQL Server. As Greg explains, Microsoft developed this easy interface to set up backups for all your databases.

For more information on SQL Server stored procedures and backups:

Note: This tip is a compilation of advice from various experts on our site.

This was first published in November 2013

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Expert Discussion

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest