Copy SQL Stored Procedures (Export and Import)

Posted on 2009-04-22
Last Modified: 2012-05-06
We have a DB that got corrupted and had to be repaired (log file was deleted) - unfortunately it seems like not everything was recovered.
It's a CRM database and when we try to run the redeployment wizard it fails. When we look at the logs to see why it failed we see :

04/22/2009 07:54:17 The object 'p_CascadeGrantAccess' does not exist in database 'AcmeWidgits_MSCRM'.
04/22/2009 07:54:17 Microsoft OLE DB Provider for SQL Server

We do have a backup, but it's at least a month old! If we recover that SQL DB, is it possible to export the stored procedure (or procedures...) then go back to the more current DB and IMPORT the stored procedures?
Question by:spamster
    LVL 6

    Expert Comment

    It is - you can copy objects using the import/export wizard (amongst other ways of recreating them)
    LVL 7

    Author Comment

    Ok, do both Databases have to be up at the same time to do that?
    Or could I export to a file?
    Or do I have to create a blank DB and export the stored procedures into that, and then restore the more current DB and import them in?
    LVL 6

    Accepted Solution

    For the wizard both DB's need to be online and you need access to both. If they are not you can just copy and past the statements from the old DB; open the new one, paste in a query window and running them will create the procedure. Either way; there are numerous options to achieve what you want (you could perhaps restore the old DB under a different name on the same server; then run the wizard)
    LVL 7

    Author Closing Comment

    Thanks man, I'm sure I can use this solution to get at least most of my data back :) Hope I can return the help that you gave some day.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
    It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now