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.