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:
- Restore the full database backup on a test server
- 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
- Select Script Stored procedure as | Create To |New Query Editor Window
- 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
- 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
- Check out the results
- 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
- Start ApexSQL Diff
Click New in the Project management dialog
- 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
- Click Show advanced options
- Click Object filter
- Uncheck all object types, except Procedures
Select Use filter for Procedures
- In the filtering tree, click Procedures
Uncheck all stored procedures, except the one that is to be recovered
- Click Compare
Select the stored procedure that is to be recovered in the Main grid
On the Home tab, in the Actions group, click Synchronize
- In the Synchronization direction step of the Synchronization wizard, click Next
- In the Dependencies step of the Synchronization wizard, click Next
Review the synchronization summary and warnings, if there are any, and click Next
In the Output options step of the Synchronization wizard, select Create a synchronization script
- Click Done
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.
April 4, 2013