Wednesday, May 20, 2009

Creating a recycle bin for SQL Server 2005\2008

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.

No comments:

Post a Comment