How to recover a specific dropped object (4209632)

Even with all precautions taken, mishaps with databases are still a possibility. Although it is good to be prepared for a disaster - the database is in the full recovery model and transaction log and database backups are created regularly, there are still some situations when a problem cannot be solved using an out-of-the-box solution.

One of these issues is restoring a single object. It’s not unusual to delete a SQL database object (e.g. a stored procedure) that is still needed, or modify it when the old version might be required later.

How to restore that stored procedure without too much trouble

Version control systems provide just the functionality you need. But this is inapplicable if there is no object already versioned and all that is available is a database backup.

Restoring the full database backup

A full database backup is available with the object that is needed, but it cannot be restored over the existing database, as all other changes made after the full database backup was created, will be overwritten.

The solution is to:

1. Restore the full database backup on a test server
2. To create a DDL script for the object you need, right-click the stored procedure you want to restore in the SQL Server Management Studio’s Object Explorer
3. Select Script Stored procedure as | Create To |New Query Editor Window
   

   

4. When the script is generated, execute it against the original database

The downside of this method is that, for huge databases, restoring a database takes up a lot of time and space, so this method is very inefficient

Recovering from cache

If the stored procedure has been executed recently, there is a chance it’s still in the cache

1. Execute the following SQL to read the cache
   

   SELECT
          Cached.refcounts,
          Cached.usecounts,
          Cached.objtype,
          SQLText.dbid,
          SQLText.objectid,
          SQLText.text,
          Query.query_plan
     FROM
          sys.dm_exec_cached_plans Cached
          CROSS APPLY sys.dm_exec_sql_text(Cached.plan_handle)SQLText
                CROSS APPLY sys.dm_exec_query_plan(Cached.plan_handle)Query
   

   
   
2. Check out the results
   

   

3. If you find the stored procedure you need, copy its text column

The advantage of this method is that you don’t even need a full database backup. The downside is that if the stored procedure hasn’t been executed recently, and it will not be possible to recover the stored procedure.

ApexSQL Diff is a SQL Server database comparison and synchronization tool which detects differences between database objects and resolves them without errors. It has granular filtering that enables comparing only specific objects. As it reads database backups, you can compare them without restoring first

1. Start ApexSQL Diff

2. Click New in the Project management dialog

   

3. In the Source pane:
   • Select Backup from the Source drop-down box
   • Select the backup set from the backup file
   • In the Destination pane:
   • Select Database from the Destination drop-down box
   • Specify the SQL instance with the database from the Server drop-down box
   • Specify the authentication method for that SQL instance (and a valid set of credentials if SQL Server authentication is chosen)
   • Specify the database name in the Database drop-down box
4. Click Show advanced options
5. Click Object filter
6. Uncheck all object types, except Procedures

7. Select Use filter for Procedures

   

8. In the filtering tree, click Procedures

9. Uncheck all stored procedures, except the one that is to be recovered

   

10. Click Compare

11. Select the stored procedure that is to be recovered in the Main grid

    

12. On the Home tab, in the Actions group, click Synchronize

    

13. In the Synchronization direction step of the Synchronization wizard, click Next
14. In the Dependencies step of the Synchronization wizard, click Next

15. Review the synchronization summary and warnings, if there are any, and click Next

    

16. In the Output options step of the Synchronization wizard, select Create a synchronization script

    

17. Click Done

18. Check out the script and press F5 to execute it

    

    Once the stored procedure is recreated, you’ll get the following message:

    

If a stored procedure is lost or modified, there’s no need to restore the whole database from a backup or search through the cache. Use ApexSQL Diff to create a DDL script directly from the database backup only for the objects that are to be restored.