Click here to Skip to main content
Click here to Skip to main content

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.

Introduction

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.

Background

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]

AS

BEGIN

  
SET NOCOUNT ON;

   
SELECT
    Name as SPName 
FROM sys.Procedures 
ORDER BY Name

END

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
        cGeneral.SaveProcs(dt.Rows(iIdx).Item("SPName"))
    Next

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

    Try
        cn.Open()
        cmd.ExecuteNonQuery()

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

        Do While sqlrdr.Read
            wrtr.WriteLine(sqlrdr.GetString(0))
        Loop

        sqlrdr.Close()
        wrtr.Dispose()

    Catch ex As Exception

        cn.Close()

    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.

License

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

About the Author

CorvetteGuru
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:
 
COBOL
RPGII
Pascal
Fortran
GWBASIC
 
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.

Comments and Discussions

 
QuestionReponse PinmemberCorvetteGuru6-Oct-12 4:20 
GeneralMy vote of 5 PinmemberPolinia6-Oct-12 3:56 
SuggestionSQL Works Best in Batches PinmemberBCantor18-Sep-12 8:54 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 13 Sep 2012
Article Copyright 2012 by CorvetteGuru
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid