在SQL Server 2005/2008中创建回收站

Creating a recycle bin for SQL Server 2005/2008 By Chris Kinley, 2009/05/20 From: http://www.sqlservercentral.com/articles/DDL+trigger/66552/ 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.     Zoom in   |   Open in new window   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.     Zoom in   |   Open in new 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: At least one parameter is missing and at least one object in recycle bin. Both parameters supplied and at least one object in recycle bin. 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:       Zoom in   |   Open in new window   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:       Zoom in   |   Open in new window   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 Kinley 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 chriskinley@yahoo.com. Resources: SP_UNDROP.sql | DDL_trigger_recycle_bin.sql By Chris Kinley, 2009/05/20