SQL Server cannot undo a dropped table, view, stored procedure, function or trigger because it has no recycle bin. Three methods help you recover deleted objects of SQL database. Restoring a backup copy works when a full backup exists and a STOPAT restore works when log backups exist. Recovery software scans the MDF file and marks deleted objects in red when no backup exists. Try all three methods on a copy before the live database.
Someone drops a stored procedure on the production server and nobody has the script. DROP statements remove objects at once and SQL Server has no undo button. Teams then search for ways to recover deleted objects of SQL database.
Most guides stop at the backup restore. They never show when the object disappeared or who dropped it.
Steps to recover deleted objects of SQL database begin after the drop. A trace query finds the time and three recovery methods follow, plus a query that lists objects missing from your live database.
Microsoft states that DROP PROCEDURE needs CONTROL permission on the procedure or ALTER permission on its schema or membership in the db_ddladmin role.
What Happens to Deleted Objects After a DROP?
Deleted objects vanish from sys.objects the moment DROP finishes. Microsoft documents no undo option in the DROP PROCEDURE reference and warns that dependent objects and scripts fail until you update them.
Recovery depends on where a copy of the definition survives. Backups and MDF files still contain deleted objects in the forms below.
| Object | Type Code in sys.objects | Where You Find the Definition |
|---|---|---|
| Table | U | Column layout and rows in the restored copy |
| View | V | sys.sql_modules in the copy |
| Stored procedure | P | Definition column in the copy |
| Function | FN or IF or TF | Definition column for the function |
| Trigger | TR | Definition column for the trigger |
Microsoft states that the definition column of sys.sql_modules returns NULL for an encrypted module. Encrypted objects show no text in a restored copy.
How to Find the Drop Time Before You Recover Deleted Objects of SQL Database?
Every restore needs a time. Default trace files record the deleted object event class (event ID 47) with the object name and the login that dropped it. Microsoft lists ObjectName, LoginName, StartTime, DatabaseName and ObjectType among the data columns of the deleted object event class.
DECLARE @trace NVARCHAR(260);
SELECT @trace = LEFT(path, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT DatabaseName, ObjectName, ObjectType, LoginName, StartTime
FROM sys.fn_trace_gettable(@trace, DEFAULT)
WHERE EventClass = 47 AND DatabaseName = N'CinemaDB'
ORDER BY StartTime DESC;
Microsoft states that fn_trace_gettable needs the ALTER TRACE permission on the server. Use the StartTime of the drop as the limit for Method #2.
Per Microsoft the default trace enabled option is 1 by default and the trace files use the base name log.trc in the MSSQL\LOG folder of the instance.
Microsoft marks the default trace and fn_trace_gettable for removal in a future SQL Server version and recommends Extended Events for new work.
3 Methods to Recover Deleted Objects of SQL Database
Two free methods rely on backups while the third works from an MDF file when no backup exists.
| Feature | Backup Copy | Point in Time Restore | Recovery Software |
|---|---|---|---|
| Price | Free | Free | Free scan with a licensed export |
| Best for | One object from a recent full backup | Objects dropped after the last full backup | Databases without a usable backup |
| Needs | Full backup | Full or bulk logged model with log backups | Windows computer and MDF file copy |
| Main limit | Later edits to the object are gone | Every log backup must exist | Demo export limit |
Method #1: Restore a Backup Copy and Script the Object (Free, Needs a Backup)
Restore the newest full backup under a new name and copy the object across. Your live database remains online. Microsoft describes the dialog in its restore a database backup using SSMS article.
Five actions create the copy:
- Open Object Explorer and select Databases and then Restore Database.
- Choose Device under Source and add the BAK file.
- Enter CinemaDB_Copy in the Database box under Destination.
- Open the Options page and untick Take tail-log backup before restore.
- Click OK and wait for the message that the restore finished.
Microsoft warns that a ticked tail log box moves the existing database into the restoring state. Untick it before you click OK.
Query the copy to see the lost text of a view, procedure, function or trigger.
SELECT OBJECT_SCHEMA_NAME(m.object_id, DB_ID(N'CinemaDB_Copy')) AS schema_name,
o.name, o.type_desc, m.definition
FROM CinemaDB_Copy.sys.sql_modules AS m
JOIN CinemaDB_Copy.sys.objects AS o ON o.object_id = m.object_id
WHERE o.name = N'usp_CloseShow';
Copy the definition into a query window on the live database and execute it. Tables need two steps. Create the structure with Script Table as and CREATE To from the context menu of the table in the copy and copy the rows next.
INSERT INTO CinemaDB.dbo.Shows (show_id, title, show_time)
SELECT show_id, title, show_time
FROM CinemaDB_Copy.dbo.Shows;
Tables with an identity column need SET IDENTITY_INSERT ON for the new table before the insert.
Method #2: Restore to a Point in Time With STOPAT (Free, Needs Log Backups)
Use this method when the deleted objects existed after the last full backup. As per Microsoft's guide to restore a database to a point in time, the database needs the full or bulk logged model and a full backup that ends before the chosen time.
Restore every later log backup up to the one that contains that time. Microsoft names the recovery point as the latest transaction commit at or before the STOPAT time.
RESTORE DATABASE CinemaDB_Copy
FROM DISK = N'D:\Backups\CinemaDB_full.bak'
WITH MOVE N'CinemaDB' TO N'E:\SQLData\CinemaDB_Copy.mdf',
MOVE N'CinemaDB_log' TO N'F:\SQLLogs\CinemaDB_Copy_log.ldf',
NORECOVERY;
RESTORE LOG CinemaDB_Copy
FROM DISK = N'D:\Backups\CinemaDB_log1.trn'
WITH NORECOVERY, STOPAT = N'2026-10-20T14:34:00';
RESTORE LOG CinemaDB_Copy
FROM DISK = N'D:\Backups\CinemaDB_log2.trn'
WITH NORECOVERY, STOPAT = N'2026-10-20T14:34:00';
RESTORE DATABASE CinemaDB_Copy WITH RECOVERY;
Set STOPAT one minute before the StartTime from the trace query. Four actions do the same in SSMS:
- Open Restore Database and choose the source backups.
- Click Timeline.
- Select Specific date and time under Restore to and enter a time before the drop.
- Click OK and finish the restore.
Microsoft suggests WITH STANDBY when you do not know the exact time so you can inspect the data between restores. Script the object from CinemaDB_Copy with the query from Method #1.
Microsoft states that SQL Server raises a warning and the database remains unrecovered when the STOPAT time lies beyond the end of the log backup.
Method #3: Recover Deleted Objects Without a Backup Using EmailDoctor (Scan Free, Export Licensed)
No backup and no log chain make the MDF file the only source. EmailDoctor SQL Database Recovery opens the MDF file with its NDF files and marks deleted objects in red in the tree of Tables, Views, Stored Procedures, Rules, Triggers and Functions. SQL Server versions from 2000 to 2022 appear in the scan window list.
Copy the MDF file to a safe folder before the scan. Demo exports are limited to 25 rows of a table and 5 additional objects.
Scan the MDF File and Preview the Deleted Objects
- Start the recovery software on a Windows server or desktop.
- Choose Open and add the CinemaDB.mdf copy from your folder.
- Select Advance Scan and tick Recover Deleted Objects. Tick Auto detect and the scan identifies the SQL Server version.
- Use the NDF Options tab for the secondary files of the database and click OK.
- Wait for the scan report to appear.
- Click Yes when the save prompt appears and store the scan as an STR file.
- Expand Tables, Views, Stored Procedures, Triggers and Functions. Red marks show the deleted ones. Click an object to preview it.
Export the Deleted Objects as a SQL Script
- Select Export from the top toolbar.
- Under Export To/As choose the SQL Server Compatible SQL Scripts option.
- Tick the deleted objects you need in the components tree.
- Select With only schema for views, procedures, functions and triggers. Choose With Schema & Data for a table and tick Export Deleted Objects. Click Save.
- Choose the folder that receives the SQL files and confirm with OK.
- Acknowledge Export Completed and close the export status window.
- Browse the folder for one SQL file per object and execute the files on your live database in SSMS.
How to Move the Deleted Objects Into the Live Database?
Create tables first and views, functions, procedures and triggers next. Microsoft lists user tables as type U and views as type V in sys.objects. Use the query below to list objects that exist in the restored copy and not in the live database.
SELECT c.name, c.type_desc
FROM CinemaDB_Copy.sys.objects AS c
WHERE c.is_ms_shipped = 0
AND c.type IN ('U', 'V', 'P', 'FN', 'IF', 'TF', 'TR')
AND NOT EXISTS (SELECT 1 FROM CinemaDB.sys.objects AS l
WHERE l.name = c.name AND l.type = c.type);
Two schemas that share an object name need a manual check. Microsoft states that sys.objects omits DDL triggers so query sys.triggers for those. Row level recovery sits in the SQL query guide for deleted records.
What Problems Stop You When You Recover Deleted Objects of SQL Database?
Five problems stop most attempts:
| Problem | Reason | Fix |
|---|---|---|
| Restore reports an unsaved tail of the log | The new database name is missing | Type a new name into the Database box of Destination |
| Live database shows Restoring | The tail log box was ticked | Untick Take tail-log backup before restore and use a new name |
| STOPAT warning and an unrecovered database | The time lies beyond the log backup | Restore the next log backup or use an earlier time |
| Trace query returns no rows | ALTER TRACE is missing or the event is older than the trace files | Ask a sysadmin for the permission or use the backup dates |
| definition column shows NULL | The module is encrypted | Use your own source script for that object |
Method #3 applies when every fix fails. Work with a duplicate of the MDF file.
Frequently Asked Questions
Can I Recover a Deleted Stored Procedure Without a Backup?
Yes. Scanning the MDF file with recovery software marks the deleted procedure in red. Export the procedure as a SQL script and execute the script on the live database.
Is There an Undo for DROP TABLE?
No. SQL Server removes the object at once and Microsoft documents no undo option in the DROP PROCEDURE reference. Use a backup copy or recovery software.
How do I Find Who Deleted an Object?
Query the default trace for event ID 47. LoginName and StartTime name the account and the time. You need the ALTER TRACE permission.
Does the Default Trace Restore the Object?
No. Trace rows name the object and the login but store no definition. Use them to choose the restore time.
Can I Restore One Object From a Backup?
Yes. Restore a backup with a new database name and script only that object. Your live database remains untouched.
Does STOPAT Alter the Live Database?
No. Restore under a new name and your live database remains online. STOPAT limits the copy to the chosen time.
Which Recovery Model Does STOPAT Need?
Microsoft states that the full or bulk logged model is required. No log backups exist under the SIMPLE model.
Can Software Recover Deleted Objects of SQL Database Like Triggers and Functions?
Yes. Tables, Views, Stored Procedures, Rules, Triggers and Functions appear in the tree. Deleted objects show in red.
Does the Software Need the Log File?
No. Scans use the MDF file alone. Use the NDF Options tab for NDF files when the database has them.
Does the Demo Restrict the Export?
Yes. Demo exports stop at 25 rows of one table plus 5 more objects. Licensing lifts the limit.
Author’s Verdict
Recovery needs a time and a source. Default trace rows give the time while a backup or the MDF file gives the source.
Restore a backup copy for a recent drop. Use STOPAT when log backups exist and recovery software when nothing else does.
Execute every script on a copy before it reaches the live database.
- Recover deleted objects of SQL database with a backup copy, STOPAT or recovery software.
- DROP statements remove objects at once and SQL Server has no undo.
- Default trace rows show the object name and the login with the drop time when event ID 47 is present.
- Backup copies give the definition from sys.sql_modules.
- STOPAT restores the database to a time before the drop when log backups exist.
- Recovery software exports deleted objects from the MDF file as SQL scripts.
- Try every script on a copy before the live database.