Introduction

Recently while being shown around Oracle 10G (yes I said the 'O' word) I noticed that this product has a recycle bin. This recycle bin which stores only dropped tables falls under the Oracle flashback technology umbrella.

I was pretty sure I could do the same in SQL server using DDL triggers and schemas and prevent accidental drops of other objects as well. This article is the result of that challenge.

In a nutshell the SQL Server recycle bin is a combination of two schemas which act as bins (recycle bin and trash can) and a DDL trigger which determines which bin to place the dropped object (which is not dropped but renamed). A stored procedure (sp_undrop) is used to revert the object to its original name and schema.

Functional overview

The recycle bin holds one only copy of the most recent version of a dropped object (table, view, stored procedure or function). The trash can holds older versions of the object if the object has been dropped more than once. The trash can be purged regularly with a scheduled task consisting of a simple script.

• The UNDROP command will revert the most recent dropped copy of an object to its original location.
• If a DROP is performed on an object already in the recycle bin the object is moved to the trash can.
• If a DROP is performed on an object already in the trash can the DROP is ignored.

Figure 1 shows the Sales.vIndividualDemographics view with the most recent drop in the recycle bin and older versions in the trash can.

Figure 1 A view of recycle bin and trash can objects

Technology overview

The SQL Server recycle bin protects spurious drops of tables, views, stored procedures and user defined functions. To enable the recycle bin the following is needed:

• Two schemas. One for the recycle bin and one for the trash can.
• One DDL trigger. This database level trigger manages objects into the recycle bin and trash can.
• One stored procedure. This is the undrop functionality and is best mounted in the master database.

Via the DDL trigger the dropped object is renamed and then moved to the recycle bin schema and the original transaction rolled back. All the information needed to undrop the object is stored in the new name so no additional metadata tables are needed.

Schemas for recycle bin and trash can

The recycle bin and trash can are simply schemas (created by the DBA as a prerequisite). The main DDL trigger will check for the existence of these schemas and abort the DROP if they don't exist. For this article I have used schema names starting with 'z' which keeps them at the bottom of the explorer view (see Figure 2 below).

Tip: The schema names for the recycle bin and trash can are declared as constants in the trigger and stored procedure. Feel free to choose your own but check they match up across all code.

USE [AdventureWorks]
GO
CREATE SCHEMA [zz_RecycleBin] AUTHORIZATION [dbo]
GO
CREATE SCHEMA [zzz_TrashCan] AUTHORIZATION [dbo]
GO

Figure 2 Recycle bin and trash can schemas

Creating the main DDL trigger

This article assumes a working knowledge of DDL triggers. For a refresher on this feature see http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/2927/ . A full code listing (commented) of this trigger is provided as a resource with this article. We will now walk through the main sections. Only one DDL trigger with database scope is required.

The trigger does the following:

• Checks for the existence of the recycle bin and trash can schemas.
• Captures the data about the dropped object.
• Builds a new object name which is the name it will have while in the recycle bin.
• If needed changes the schema of an old version of the object in the recycle bin to the trash can.
• Renames the dropped object.
• Changes the schema of the dropped object to that of the recycle bin.

First create the trigger. The trigger is at the database scope level.

USE [AdventureWorks]
GO
CREATE TRIGGER [recyclebin_drop_object] ON DATABASE 
FOR DROP_TABLE, DROP_PROCEDURE, DROP_VIEW, DROP_FUNCTION
AS 
DECLARE @change_schema_command nvarchar(200)
,@rename_command  nvarchar(200)
,@populate_object_command nvarchar(200)
,@object_list_count smallint
,@DBNAME sysname
,@RECYCLEBIN_SCHEMA_NAME sysname
,@first_delimiter_pos tinyint
,@second_delimiter_pos tinyint
DECLARE @CONST_EMPTYSTRING VARCHAR(1)
,@CONST_RECYCLEBIN_DELIMITER_NAME nvarchar(3)
,@CONST_RECYCLEBIN_SCHEMA_NAME sysname
,@CONST_TRASHCAN_SCHEMA_NAME sysname
SET @CONST_RECYCLEBIN_DELIMITER_NAME = '_$_'
SET @CONST_EMPTYSTRING = ''
SET @RECYCLEBIN_SCHEMA_NAME = 'zz_RecycleBin'
SELECT @undroptype = ISNULL(@undroptype, @CONST_EMPTYSTRING)
SELECT @undropname = ISNULL(@undropname, @CONST_EMPTYSTRING)
SELECT  @DBNAME = db_name(db_ID())

Then check to see if the recycle bin and trash can schemas exist. No point continuing if they don't.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @CONST_RECYCLEBIN_SCHEMA_NAME)
BEGIN
PRINT 'Recycle bin schema does not exist'
PRINT 'The drop has been aborted'
PRINT 'Please create the schema'
PRINT ''
PRINT 'CREATE SCHEMA ' + @CONST_RECYCLEBIN_SCHEMA_NAME + ' AUTHORIZATION [dbo]'
PRINT ''
ROLLBACK
RETURN
END
 
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @CONST_TRASHCAN_SCHEMA_NAME)
BEGIN
PRINT 'Trash can schema does not exist'
PRINT 'The drop has been aborted'
PRINT 'Please create the schema'
PRINT ''
PRINT 'CREATE SCHEMA ' + @CONST_TRASHCAN_SCHEMA_NAME + ' AUTHORIZATION [dbo]'
PRINT ''
ROLLBACK
RETURN
END

The next step is to extract the information need from EVENTDATA(). We're interested in:

• What was dropped (both schema and object name)?
• Who dropped?
• When dropped?

Then build up the object name as it would exist in the recycle bin. The format using the delimiter _$_ is:originalschema_$_originalname_$_domain@login _$_yyyy_mm_ddThh_mm_ss_sss

So the Sales.vIndividualDemographics view dropped by kinleyc on March 23 at 10:20:41 would be renamed to: Sales_$_vIndividualDemographics_$_DOMAIN@kinleyc_$_2009_03_23T10_20_41_997

SET @eventdata = EVENTDATA()
SET @Login_name = @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
SET @Post_time = @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'sysname')
SET @Schema_name = @eventdata.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')
SET @Object_name = @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
SELECT @datetimestamp = REPLACE(REPLACE(REPLACE(@Post_time,':','_'),'-','_'),'.','_')
SET @new_object_name =
@Schema_name
+ @CONST_RECYCLEBIN_DELIMITER_NAME + @Object_name
+ @CONST_RECYCLEBIN_DELIMITER_NAME + REPLACE(@Login_name,'\','@') + @CONST_RECYCLEBIN_DELIMITER_NAME + @datetimestamp

There is another check to see if the object being dropped is already in the trash can. If so the drop is aborted by issuing a ROLLBACK followed by a RETURN thus ending the trigger. I've chosen to engineer this way for the following reasons. Firstly it prevents objects from ever being dropped unless the DBA explicitly disables the trigger. Secondly forcing a drop would again fire the same trigger recursively and the code would have to be made more complex to allow for this.

IF @schema_name = @CONST_TRASHCAN_SCHEMA_NAME
BEGIN
PRINT 'This object is already in the trash can '
PRINT 'The trigger recyclebin_drop_object must be disabled for this DROP to work'
ROLLBACK
RETURN
END

Now we come to the core part of the trigger where the main rename and transfer takes place. There are checks here to determine if the object being dropped is a recycle bin object or the object is in a non-recycle bin schema and an older version exists in the recycle bin. If it is an explicit drop of an object already in the recycle bin then no rename takes place and only a schema transfer to the trash can is invoked. If there is an older version of the object in the recycle bin then this is moved to the trash can to make 'space' for the new object coming in. All renames and transfer commands are prepared before initiating a transaction.

ELSE
BEGIN
 
IF @schema_name = @CONST_RECYCLEBIN_SCHEMA_NAME
SELECT @existing_recycle_bin_object = @Object_name
ELSE
BEGIN
SELECT
@existing_recycle_bin_object = name ,
@object_type = RTRIM(type)
FROM sys.objects WHERE type IN ('U','V','FN','TF','P')
AND schema_id in(SELECT schema_id FROM sys.schemas WHERE name = @CONST_RECYCLEBIN_SCHEMA_NAME)
and name like @Schema_name + @CONST_RECYCLEBIN_DELIMITER_NAME + @Object_name + '%'
 
END
 
IF (@existing_recycle_bin_object IS NOT NULL) OR (@schema_name = @CONST_RECYCLEBIN_SCHEMA_NAME)
BEGIN
SET @change_schema_command_trashcan =
'ALTER SCHEMA ' +
@CONST_TRASHCAN_SCHEMA_NAME +
' TRANSFER ' +
@CONST_RECYCLEBIN_SCHEMA_NAME + '.' +
@existing_recycle_bin_object
END
 
IF @schema_name <> @CONST_RECYCLEBIN_SCHEMA_NAME
BEGIN
SET @change_schema_command_recyclebin =
'ALTER SCHEMA ' +
@CONST_RECYCLEBIN_SCHEMA_NAME +
' TRANSFER ' +
@Schema_name + '.' +
@new_object_name
 
SET @rename_command =
'sp_rename ' + '''' +
@Schema_name + '.' +
@Object_name + ''',''' +
@new_object_name + ''''
END

It's time now to rollback the original transaction. Remember that all triggers have a transaction in progress when they are invoked. Usually it's autocommited when the trigger completes but in our case we want to stop the original drop and do our own thing.

ROLLBACK

For the final rename\transfer I've elected to use a nested transaction within the trigger the reason being I want the rename and transfer to be an all or nothing event. I've kept the transaction very short, within a try block and am not doing validation within the transaction except for variables.

See books online: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/650105c1-32fd-4f16-8082-f391c42c3fb0.htm for information on transactions in triggers.

BEGIN TRY
 
BEGIN TRANSACTION
IF (@change_schema_command_trashcan IS NOT NULL) OR (@schema_name = @CONST_RECYCLEBIN_SCHEMA_NAME)
BEGIN
EXEC sp_executesql @change_schema_command_trashcan
END
 
IF @schema_name <> @CONST_RECYCLEBIN_SCHEMA_NAME
BEGIN
EXEC sp_executesql @rename_command
EXEC sp_executesql @change_schema_command_recyclebin
END
COMMIT TRANSACTION
END TRY

The catch block is taken directly from books online to allow for uncommittable transactions. See books online:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/e9300827-e793-4eb6-9042-ffa0204aeb50.htm

Following the catch there are some PRINT statements back to the DBA to see the new object name.

BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state. ' +
'Rolling back transaction.'
ROLLBACK TRANSACTION
END
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable. ' +
'Committing transaction.'
COMMIT TRANSACTION
END
END CATCH
 
IF @schema_name <> @CONST_RECYCLEBIN_SCHEMA_NAME
BEGIN
PRINT ''
PRINT 'The object ' + @Schema_name + '.' + @Object_name + ' has been moved to the recycle bin'
PRINT 'as object ' + @CONST_RECYCLEBIN_SCHEMA_NAME + '.' + @new_object_name
PRINT ''
PRINT 'The object ' + @existing_recycle_bin_object + ' has been moved to the trash can'
END
ELSE
BEGIN
PRINT 'The object ' + @existing_recycle_bin_object + ' has been moved to the trash can'
END

I've chosen to start a new transaction at the end of the trigger. This is solely to prevent to 3609 error when the trigger detects @@trancount = 0. The trigger is in autocommit mode so this dummy transaction is committed when the trigger ends. If you don't want to have it there and are OK seeing the 3609 error then remove this line. The result is the same the recycle bin will still work. The books online article for triggers mentioned above discusses this error.

BEGIN TRANSACTION
END

Figure 3 below shows different objects being dropped and the results in the query results window.

Figure 3.

The UNDROP

It's no surprise that the undrop functionality is basically a reverse of the process from the recycle bin trigger. The undrop does not involve the trash can.

I chose to name the stored procedure sp_undrop and mount it in the master database so it can be called from any user database. Books online recommends not naming stored procedures with the sp_ prefix as they may clash with future system stored procedures. I figure if Microsoft introduces similar functionality they will use UNDROP as the command - that's my excuse anyway.

The full code listing (commented) of this stored procedure is provided as a resource with this article.

There are two input parameters, the combination of which makes the object unique in the database. Note that the @undropname parameter includes both the schema and name.

An example of the stored procedure invocation.

EXEC SP_UNDROP @undroptype = 'PROCEDURE' , @undropname = N'HumanResources.uspUpdateEmployeeHireInfo'

USE MASTER
GO
CREATE PROC [dbo].[SP_UNDROP]
@undroptype varchar(10) =NULL
, @undropname nvarchar(200)=NULL
AS

Two tables are used two store initial objects kept in the recycle bin. The first table #sysobjects is a temporary table so that it can be called with sp_executesql. The second table @object_list is the main storage table for the procedure. It stores the recycled name and various components of the original name. As this procedure is essentially about parsing there are also columns to store delimiter positions. After the initialisation of variables and constants an initial check is made to guard against execution in system databases.

DECLARE ,@change_schema_command nvarchar(200)
,@rename_command nvarchar(200)
,@populate_object_command nvarchar(200)
,@object_list_count smallint
,@DBNAME sysname
,@RECYCLEBIN_SCHEMA_NAME sysname
,@first_delimiter_pos tinyint
,@second_delimiter_pos tinyint
 
DECLARE @CONST_EMPTYSTRING varchar(1)
,@CONST_RECYCLEBIN_DELIMITER_NAME nvarchar(3)
 
CREATE TABLE #sysobjects
(
name sysname,
type char(2),
modify_date datetime ,
schema_name sysname
)
 
DECLARE @object_list TABLE
(
ID INT IDENTITY (1,1),
objectName sysname,
objecttype char(2),
first_delimiter_pos tinyint,
second_delimiter_pos tinyint,
original_schemaname sysname NULL,
original_objectname sysname NULL,
objecttype_long varchar(10),
date_modifed datetime
)
 
SET @CONST_RECYCLEBIN_DELIMITER_NAME = '_$_'
SET @CONST_EMPTYSTRING = ''
SET @RECYCLEBIN_SCHEMA_NAME = 'zz_RecycleBin'
 
SELECT @undroptype = ISNULL(@undroptype, @CONST_EMPTYSTRING)
SELECT @undropname = ISNULL(@undropname, @CONST_EMPTYSTRING)
SELECT @DBNAME = db_name(db_ID())
 
IF @DBNAME IN ('master','model','tempdb','msdb')
BEGIN
PRINT 'Not permitted in context of system databases'
RETURN

Now the two work tables are populated. The table #sysobjects table is loaded with recycle bin objects using sys.objects and sys.schemas. The table variable @object_list is populated with the same data plus additional information about the location of objects in the long recycle bin object name. An object count is determined and then the #sysobjects is then dropped. A further update of the work table is done to parse out the original object and schema names as well as deriving a full object type name.

SET @populate_object_command =
N'INSERT INTO #sysobjects' +
N' select o.name, o.type, o. modify_date, s.name' +
N' from sys.objects o' +
N' join sys.schemas s ON o.schema_id = s.schema_id' +
N' WHERE s.name = ''' + @RECYCLEBIN_SCHEMA_NAME + ''''
EXEC sp_executesql @populate_object_command
 
INSERT INTO @object_list
SELECT
name
,type
,CHARINDEX(@CONST_RECYCLEBIN_DELIMITER_NAME,name,1) - 1 lastcharofschema
,CHARINDEX(@CONST_RECYCLEBIN_DELIMITER_NAME,name,
CHARINDEX(@CONST_RECYCLEBIN_DELIMITER_NAME,name,1) +
LEN(@CONST_RECYCLEBIN_DELIMITER_NAME) ) - 1 lastcharofobjectname
,NULL
,NULL
,NULL
,modify_date
FROM #sysobjects WHERE type in ('U','V','FN','TF','P')
 
SELECT @object_list_count = COUNT(1) FROM @object_list
 
DROP TABLE #sysobjects
UPDATE @object_list
SET
original_schemaname = LEFT(objectname,first_delimiter_pos)
,original_objectName =
SUBSTRING(objectname, first_delimiter_pos +
LEN(@CONST_RECYCLEBIN_DELIMITER_NAME) + 1,
second_delimiter_pos - first_delimiter_pos
- LEN(@CONST_RECYCLEBIN_DELIMITER_NAME))
,objecttype_long = CASE RTRIM(objecttype)
WHEN 'V' THEN N'VIEW'
WHEN 'P' THEN N'PROCEDURE'
WHEN 'U' THEN N'TABLE'
WHEN 'FN' THEN N'FUNCTION'
WHEN 'TF' THEN N'FUNCTION'
END

All the preparation is now done. The main conditional statement now starts and determines one of three paths:

1. At least one parameter is missing and at least one object in recycle bin.
2. Both parameters supplied and at least one object in recycle bin.
3. No objects in recycle bin.

If the first condition is met the procedure lists out all objects in the recycle bin with ready-to-go undrop syntax.

IF (@undroptype = '' or @undropname = '') AND @object_list_count > 0
BEGIN
PRINT 'Objects available in recycle bin of: ' + @DBNAME
PRINT 'Copy and paste desired UNDROP into a query window'
select
'EXEC SP_UNDROP ' + '@undroptype = ' + '''' + objecttype_long
+ ''' , @undropname = N''' +
original_schemaname
+ '.' + original_objectname + ''' -- dropped on ' +
cast (date_modifed as varchar(30))
FROM @object_list
ORDER BY objecttype_long, original_schemaname, original_objectname
END

EXEC SP_UNDROP results in:

Figure 4 using sp_undrop without parameters

If the second condition is satisfied a further check is done to make sure the object exists.

ELSE IF (@object_list_count = 0 OR @object_list_count IS NULL)
BEGIN
PRINT 'There are no objects in the recycle bin'
END

EXEC SP_UNDROP 'TABLE' , 'NOT_A_TABLE' results in:

Figure 5 using sp_undrop when the object does not exist

If the objects exists then the undrop is attempted within in a transaction.

ELSE IF @object_list_count > 0
BEGIN
IF EXISTS (SELECT TOP 1 * FROM @object_list
WHERE
original_schemaname + '.' + original_objectname = @undropname
and objecttype_long = @undroptype)
BEGIN
SELECT @change_schema_command =
'ALTER SCHEMA ' +
original_schemaname +
' TRANSFER ' +
@RECYCLEBIN_SCHEMA_NAME + '.' +
objectname
FROM @object_list
WHERE
original_schemaname + '.' + original_objectname = @undropname
and objecttype_long = @undroptype
 
SELECT @rename_command =
'sp_rename ' + '''' +
original_schemaname + '.' +
objectname + ''',''' +
original_objectname + ''''
FROM @object_list
WHERE
original_schemaname + '.' + original_objectname = @undropname
AND objecttype_long = @undroptype
 
BEGIN TRY
BEGIN TRANSACTION
EXEC sp_executesql @change_schema_command
EXEC sp_executesql @rename_command
PRINT ''
PRINT @undroptype + ' : ' + @undropname + ' undropped successfully'
COMMIT TRANSACTION
END TRY
 
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
PRINT 'UNDROP FAILED'
END CATCH
END
 
IF NOT EXISTS (SELECT TOP 1 * FROM @object_list
WHERE
(original_schemaname + '.' + original_objectname) = @undropname
and objecttype_long = @undroptype)
BEGIN
PRINT 'This object is not available in recycle bin of: ' + @dbname
PRINT 'Run (exec sp_undrop) without any parameters to see contents of bin'
END
END

Taking out the trash

At the DBA's discretion the trash can be purged with a basic loop-the-loop script inside a scheduled job. There are two items of note in this script. One is the disabling and enabling of the DDL trigger to avoid any recursive trigger complications. The other is setting the trash can schema name of the @TRASHCAN_SCHEMA_NAME which must match the one used in the DDL trigger.

USE AdventureWorks;
GO
/*
simple script for purging objects from trash can
run hourly, daily, weekly to suit environment
*/DECLARE @objects TABLE (ID SMALLINT IDENTITY(1,1), command sysname)
DECLARE @TRASHCAN_SCHEMA_NAME sysname
DECLARE @counter smallint
DECLARE @maxcounter smallint
DECLARE @command nvarchar(300)
 
SET @TRASHCAN_SCHEMA_NAME = 'zzz_TrashCan' -- don't forget case on 'CS' collations
 
IF NOT EXISTS (SELECT schema_id FROM sys.schemas WHERE name = @TRASHCAN_SCHEMA_NAME)
BEGIN
PRINT 'No such schema - check the spelling and case of schema name'
RETURN
END
INSERT INTO @objects
SELECT
'Command' =
CASE RTRIM(type)
WHEN 'V' THEN N'DROP VIEW '
WHEN 'P' THEN N'DROP PROCEDURE '
WHEN 'U' THEN N'DROP TABLE '
WHEN 'FN' THEN N'DROP FUNCTION '
WHEN 'IF' THEN N'DROP FUNCTION '
END + @TRASHCAN_SCHEMA_NAME + '.' +
name
FROM sys.objects WHERE type in ('U','V','FN','TF','P')
AND schema_id in
(SELECT schema_id FROM sys.schemas WHERE name = @TRASHCAN_SCHEMA_NAME)
EXEC sp_executesql N'DISABLE TRIGGER recyclebin_drop_object ON DATABASE'
SELECT @maxcounter = count(1) FROM @objects
SET @counter = 1
WHILE @counter <= @maxcounter
BEGIN
SELECT @command = command FROM @objects WHERE ID = @counter
PRINT 'executing...' + @command
EXEC sp_executesql @command
SET @counter = @counter + 1
END
 
EXEC sp_executesql N'ENABLE TRIGGER recyclebin_drop_object ON DATABASE'
PRINT 'Done'

Conclusion

Whether protecting your production databases from unexpected object drops or implementing a simple method of storing historical versions of objects this combination of schemas and a DDL trigger will do both.

Author

Chris Goldsmith is a UK based SQL Server professional and has been working with SQL Server since 1997. He holds both MCDBA and MCITP certifications. Contact him at chris.goldsmith.work@gmail.com.