Click here to Skip to main content
13,510,146 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


7 bookmarked
Posted 13 Sep 2012

Backup Stored Procedures in SQL Server

, 13 Sep 2012
Rate this:
Please Sign up or sign in to vote.
Backup Stored Procedures in SQL Server.


You're doing development, using SQL Server, and the database needs to be restored. Gasp! I have 75 SP's sitting there, half of which I have enhanced in the last 12 hours. Now what? I don't want to lose these stored procedures, older versions of SQL don't have easy ways of saving them, so, lets grab them and put them on my network drive so I have an independant backup. Also, allows for extactraction for easy scripting in other databases.


I had just that nightmare a couple of weeks back. I found the general idea on another site, but the implimentation was poor, so I redid it so it would actually work. This is a VB program that calls stored procedures. One that I created, the other is a system SP.

Using the code

This is so very simple: First is the SQL SP that provides the name and then the VB10 code that leverages it.

ALTER PROCEDURE [dbo].[ReadProcs]




    Name as SPName 
FROM sys.Procedures 


This is the first bit of VB10 that kicks off the process:

Private Sub btnSaveProc_Click(sender As System.Object, e As System.EventArgs) Handles btnSaveProc.Click

    Dim cData As New cData
    Dim dt As New DataTable
    Dim iIdx As Int16
    Dim cGeneral As New cGeneralFunctions

    dt = cData.GetDataTable("ReadProcs", "Y")

    For iIdx = 0 To (dt.Rows.Count - 1) Step 1

End Sub

Finally, the guts. This step gets the name, finds it on the database, and then extracts and saves it to a location on, in this case, my C: drive. After, I can copy to a network location:

Public Function SaveProcs(ByVal SPName As String)

    Dim FilePath As String
    Dim cmd As New SqlClient.SqlCommand
    Dim dat As New SqlClient.SqlDataAdapter
    Dim dt As New DataTable
    Dim cn As New SqlClient.SqlConnection

    FilePath = "C:\SQL Procs\" & SPName & ".sql"

    cn = New SqlClient.SqlConnection(cGlobals.ConnectionString)

    cmd.Connection = cn
    cmd.CommandText = "sp_HelpText"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandTimeout = 120

    cmd.Parameters.Add("@objname", SqlDbType.NVarChar).Value = SPName


        Dim sqlrdr As SqlClient.SqlDataReader = cmd.ExecuteReader
        Dim wrtr As New System.IO.StreamWriter(FilePath)

        Do While sqlrdr.Read


    Catch ex As Exception


    End Try

End Function

The trick is using sys.Procedures to get the names of the SP and sp_HelpText to actually GET the proc data. The rest of this reads the proc, and then stores it to a local drive location.

This is also a great way of making copies of complex SP's for your own portfolilo.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
United States United States
I have been coding for over 25 years.

Started with Apple II BASIC, then in college dabbled in all sorts of languages:


My career started with VAX BASIC and then went backwards with WANG MVP2200 BASIC!

From there, I transisitoned into mainframes, using mostly ADABAS/Natural and some COBOL. I was fully engulfed in mainframes for 15 years - TSO, JCL, syncsort, even subbing as an operator during special mass updates!

After 10 years in mainframes, I taught myself VB6. My next four jobs were a mixture of VB6 and mainframe.

My last job gave me some exposure to .NET, but since my VB6 was so good, I was left supporting the legacy system.

I left that job in 2010 to work for my wife and started to teach myself VB 2008. Which led to where I am now, working with VB 2010 and SQL 2012.

You may also be interested in...


Comments and Discussions

QuestionReponse Pin
CorvetteGuru6-Oct-12 4:20
memberCorvetteGuru6-Oct-12 4:20 
GeneralMy vote of 5 Pin
Polinia6-Oct-12 3:56
memberPolinia6-Oct-12 3:56 
SuggestionSQL Works Best in Batches Pin
BCantor18-Sep-12 8:54
memberBCantor18-Sep-12 8:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180417.1 | Last Updated 13 Sep 2012
Article Copyright 2012 by CorvetteGuru
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid