Stored Procedure Backups
-
Hi All,
I need to take the backup of all my stored procedures.I need to take each stored procedure individually in a separate file with the stored procedure names as the file name.
How do i do it?Is there any command or options or routine available ?Is there any option that i can code something in any of the programming languages and get those things ?
Thanks in Advance.
Think Ahead, Raj.D
-
Are you wanting just YOUR stored procedures or all stored procedured?
When I create my own sp, I save it to a script directory on my hard drive (I have folders for: Stored Procedures, Temporary Scripts, Test Scripts, Jobs, Old scripts). Then I backup those files.
The only other way I know of to get scripts of SPs is to open Enterprise Manager, find the database, expand to Stored Procedures, right click on the sp, select All Tasks>Generate SQL Script. Then save that to a folder for backing up.
-SQLBill
-
If you have selected a database in EM ( or level below database )
You have the GenerateSqlScript in the tools menu.
/rockmoose
You must unlearn what You have learnt -
You can use vb to automate the process:
Public Function WriteViews_SP() As Boolean
'
' Description: Write all the views and stored procedures in a database to files
' Date 08/19/2003
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Rs0 As ADODB.Recordset
Dim strViewName As String
Dim strViewText As String
Dim strFileLocation As String
Dim strFileTail As String
Dim strConn0 As String
Dim strQuery0 As String
Dim strDatabase As String
Dim Conn0 As ADODB.Connection
strDatabase = "CHRIS_Summary"
strFileLocation = "C:\My Documents\SQL_ORIG_" & strDatabase & "\"
strFileTail = ".sql"
' Connection string to database
strConn0 = "driver={SQL Server};server=dhssql2000d1;uid=;pwd=;database=" & strDatabase
' Query to data in CurrCHRISRptData security files
strQuery0 = "SELECT sysobjects.id, syscomments.colid, CAST(sysobjects.name AS VARCHAR(60)) AS [name], sysobjects.crdate, syscomments.text FROM dbo.sysobjects LEFT OUTER JOIN dbo.syscomments ON syscomments.id = sysobjects.id WHERE (SUBSTRING(sysobjects.NAME,1,4) = 'View' or SUBSTRING(sysobjects.NAME,1,2) = 'cr') AND sysobjects.uid = 1 ORDER BY sysobjects.id, syscomments.colid"
Set Conn0 = CreateObject("ADODB.Connection")
Conn0.Open strConn0
Set Rs0 = CreateObject("ADODB.Recordset")
Rs0.Open strQuery0, Conn0, adOpenForwardOnly, adLockOptimistic
'Read in the list of staff users
strViewName = Rs0!Name
Do Until Rs0.EOF
If Rs0("Name") = strViewName Then
strViewText = strViewText + Rs0("Text")
Else
'output the string
GoSub WriteRecord
strViewText = Rs0("Text")
strViewName = Rs0("Name")
End If
Rs0.MoveNext
Loop
'output the string
GoSub WriteRecord
Rs0.Close
WriteViews_SP = True
Exit Function
WriteRecord:
Open strFileLocation & strViewName & strFileTail For Binary As #1
Put #1, , strViewText
Close #1
Return
End Function
-
Whoa, nice script Harry.
SQLDMO also has scripting capabilities, but I guess you knew that.
You must unlearn what You have learnt -
First of all, there is an sp_helptext command. What you could do is to dump the contents of this to a text file for each of the stored procedures.
You could write a script (I don't have enough time right now), to list all of the stored procedures from the sysobjects table, and rund the sp_helptext command to dump them to a text file that is named after the sp. I envision a loop, possibly a cursor to read the next name of sp (though you may want to avoid cursors).
Just some quick thoughts.
I agree though, that if you do not need this scripted, Enterprise manager can scipt them all for you in a single step to separate files too!
Enjoy.
Michael
-
Here's an example of a script I built that runs every night to script all of our custom stored procedures. It creates multiple script files, one per stored procedure and one that contains the scripts for all stored procedures.
It may not be the prettiest or best but it works well and since I run it automatically each night in the middle of the night, I don't have any problem with it.
CREATE PROC dbo.script_sps(
@backup_path varchar(1000))
AS
DECLARE @bcp varchar(8000),
@path varchar(1000),
@server_name varchar(128),
@SP SYSNAME,
@Status int,
@str varchar(4000)
-- Set the path for the output file
-- Set this based on the value of your @backup_path parameter if the
-- @backup_path parameter is not the full path. (i.e. We use the
-- server name as part of the path so we incorporate it into the
-- path but we don't send it in the parameter to make it more generic.
SET @path = @backup_path
-- Create the table wich saves the SP text for each SP individually
CREATE TABLE ##SPs(
[text] varchar(4000))
-- Create the table which saves the SP text for all SPs
CREATE TABLE ##ALL_SPs(
[text] varchar(4000))
-- Insert comments/header info into the table with all SPs text
INSERT INTO ##ALL_SPs
VALUES ('-- Server Name:' + @@SERVERNAME)
INSERT INTO ##ALL_SPs
VALUES ('-- Database Name: ' + DB_NAME())
INSERT INTO ##ALL_SPs
VALUES ('-- Date:' + CONVERT(VARCHAR, GETDATE()))
INSERT INTO ##ALL_SPs
VALUES ('-- Stored prodecure generated automatically by user ' + USER_NAME())
INSERT INTO ##ALL_SPs
VALUES ('GO')
INSERT INTO ##ALL_SPs
VALUES (' ')
INSERT INTO ##SPs
VALUES ('USE master')
INSERT INTO ##SPs
VALUES ('GO')
INSERT INTO ##SPs
VALUES (' ')
-- Cursor wich will save all the SPs in the database
-- Use the LIKE clause to filter the SPs you want to
-- script. For example, in our company we use a common
-- prefix (such as MY_SP_) for all of our SPs. We
-- only script these as part of our process.
DECLARE curSP CURSOR FAST_FORWARD FOR
SELECT name
FROM sysobjects
WHERE xtype = 'P'
AND name LIKE 'MY_SP_%'
ORDER BY name
OPEN curSP
FETCH NEXT FROM curSP INTO @sp-2
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert comments/header info into the table with the individual SPs
INSERT INTO ##SPs
VALUES ('-- Server Name:' + @@SERVERNAME)
INSERT INTO ##SPs
VALUES ('-- Database Name: ' + DB_NAME())
INSERT INTO ##SPs
VALUES ('-- Date:' + CONVERT(VARCHAR, GETDATE()))
INSERT INTO ##SPs
VALUES ('-- Stored prodecure generated automatically by user ' + USER_NAME())
INSERT INTO ##SPs
VALUES ('GO')
INSERT INTO ##SPs
VALUES (' ')
INSERT INTO ##SPs
VALUES ('USE master')
INSERT INTO ##SPs
VALUES ('GO')
INSERT INTO ##SPs
VALUES (' ')
-- Insert in the temp tables, the text of the SP
SELECT @STR = 'EXEC sp_helptext ' + @sp-2
INSERT INTO ##SPs
EXEC (@str)
INSERT INTO ##ALL_SPs
EXEC (@str)
INSERT INTO ##SPs
VALUES ('GO')
INSERT INTO ##SPs
VALUES (' ')
INSERT INTO ##ALL_SPs
VALUES ('GO')
INSERT INTO ##ALL_SPs
VALUES (' ')
-- Output the SP's text to a SQL script file
SET @bcp = 'bcp "SELECT * FROM ' + DB_NAME() + '.dbo.##SPs" QUERYOUT "' + @path + @sp-2 + '.sql" -T -c'
EXEC @status = master.dbo.xp_cmdshell @bcp, no_output
-- Clear the table for the next SP text
TRUNCATE TABLE ##SPs
FETCH NEXT FROM curSP INTO @sp-2
END
CLOSE curSP
DEALLOCATE curSP
-- Output the text for all SPs to a SQL script file
SET @bcp = 'bcp "SELECT * FROM ' + DB_NAME() + '.dbo.##ALL_SPs" QUERYOUT "' + @path + 'ALL_SPs_' + @@SERVERNAME + '_' + DB_NAME() + '.sql" -T -c'
EXEC @status = master.dbo.xp_cmdshell @bcp, no_output
DROP TABLE ##SPs
DROP TABLE ##ALL_SPs
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Hope you find this useful.
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
Hi,
If you want to do it for one time do it from Generate SQL Script from Options tab under Files to Generate choose Create one file per Object.
But if you want to schedule it, you have to write a procedure like hawg wrote.
Regards,
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply