Feed Icon  

Contact

  • Bryant Likes
  • Send mail to the author(s) E-mail
  • twitter
  • View Bryant Likes's profile on LinkedIn
  • del.icio.us
Get Microsoft Silverlight
by clicking "Install Microsoft Silverlight" you accept the
Silverlight license agreement

Hosting By

Hot Topics

Tags

Open Source Projects

Archives

Ads

SharePoint Recycle Bins

Posted in SharePoint at Monday, February 14, 2005 10:41 AM Pacific Standard Time

I recently read the MSDN article Add a Recycle Bin to Windows SharePoint Services for Easy Document Recovery by Maxim V. Karpov and Eric Schoonover. It was an interesting read, but I was pretty amazed at the lengths they had to go to in order to get something as simple as a recycle bin. Obviously, this biggest setback was the fact that

events are processed asynchronously. As a result, a registered event sink will only be notified about the document deletion after that document has already been deleted from the SQL Server backend database. As a result, the event sink can't simply copy the document to the recycle bin library because the deleted document no longer exists.

Sounds like a YASPQ (or YASQ if you prefer) to me. So in order to create the recycle bin they Maxim and Eric end up mirroring the document libraries in order to add the recycle bin functionality. While reading the article my mind couldn't help of trying to come up with a simplier method. Here is my own version of a SharePoint recycle bin (note: don't play around with your SharePoint databases if you don't know what you're getting yourself into. I always build and test my scripts out on test servers and sites and this is what I would call a pre-alpha release).

Connect to the _SITE database using Query Analyzer and run the following SQL Scripts.

1) Create the RecycledDocs table which is basically a copy of the Docs table:

-- create the RecycledDocs table
select *
into RecycledDocs
from Docs
where 1 = 0

 

2) Create an instead of trigger on the docs table that will redirects requests to delete documents.

create trigger doc_recycle on docs instead of delete
as
  delete  RecycledDocs
  where  id in (select id from deleted)
  insert into RecycledDocs
  select   id, siteid, dirname, leafname, webid, listid, doclibrowid,
    type, size, metainfosize, version, uiversion, dirty, cacheparseid,
    docflags, thicketflag, charset, timecreated, timelastmodified, 
    nexttolasttimemodified, metainfotimelastmodified, timelastwritten,
    setuppath, checkoutuserid, checkoutdate, checkoutexpires, checkoutsize,
    versioncreatedsincestcheckout, ltcheckoutuserid, virusvendorid,
    virusstatus, virusinfo, metainfo, content, checkoutcontent
  from   Docs 
  where   type = 0 and Id in (select Id from deleted)
  delete  docs
  where  id in (select id from deleted)

 

That is basically it. Of course, there is a lot of functionality that needs to be added from a user perspective, but as an admin you can now restore any document that gets deleted.

I'm not sure how this would affect Microsoft's support of a SharePoint installation, it may or may not. Your RecycledDocs table will also fill up (much like the Windows Recycle Bin) until you manually empty it. I'm hoping to do a longer write up on this along with either a web part or an ASPX page that can manage it.

So there is my simple method of creating a Recycle Bin in SharePoint. It isn't very fancy, but it works. :)

Monday, February 14, 2005 8:56:00 PM (Pacific Standard Time, UTC-08:00)
This does seem to be a much more elegant solution. Does this account for documents in doc libs that has versioning turned on?
Jason Dossett
Monday, February 14, 2005 10:41:00 PM (Pacific Standard Time, UTC-08:00)
According to Michael Yeager who made a webcast on SharePoint Workflow last week, there are rumors Service Pack 2 will add a few essentials missing functionalities to SPS. Maybe they'll add something like this.
Xi
Tuesday, February 15, 2005 12:33:00 AM (Pacific Standard Time, UTC-08:00)
It doesn't account for versioning, however, you could add that in if you wanted. There is a seperate table used to version the documents.
Tuesday, February 15, 2005 3:58:00 PM (Pacific Standard Time, UTC-08:00)
Avanade, my employer has a solution using the DB trigger method. It's very full featured although I haven't actually used it yet.
<br>
<br>I also developed a similar solution before coming to Avanade but the problem that Maxim and I had with it was that it is a little more "high risk" when it comes to deployment, requiring a certain level of SQL skills. It also has the shortcoming of voiding Microsoft support, any modification to the database will result in Microsoft's refusal to support the SPS installation. MSDN being a Microsoft publication we didn't think they would be comfortable publishing something that voided Micrsoft Support for the product.
Wednesday, February 16, 2005 1:48:00 AM (Pacific Standard Time, UTC-08:00)
Eric,
<br>
<br>Thanks for the comment. That makes a lot of sense and I kind of suspected that making a change like this to the database might cause issues for MS support. In light of that the article makes a lot more sense.
Tuesday, April 05, 2005 10:28:00 AM (Pacific Daylight Time, UTC-07:00)
I'm trying to use the trigger script but it's not allowing me to add the trigger to the Docs table. Help please.
Mr. E
Tuesday, April 05, 2005 10:29:00 AM (Pacific Daylight Time, UTC-07:00)
I'm trying to use the trigger script but MS SQL is not allowing me to add the trigger to the Docs table. Help please.
Mr. E
Tuesday, April 05, 2005 10:43:00 AM (Pacific Daylight Time, UTC-07:00)
Do you have permission to add triggers? What is the error message you're getting?
Wednesday, April 06, 2005 6:11:00 AM (Pacific Daylight Time, UTC-07:00)
Thank you for your response. I currently have db_owner role to the Database. I tried adding the trigger through Enterprise Manager and Query Analyzer. Here are the error messages I received.
<br>Enterprise Manager
<br>Error 213: Insert Error: Column name or number of supplied values does not match table definition.
<br>Query Analyzer
<br>Server: Msg 208, Level 16, State 4, Procedure doc_recycle, Line 1
<br>Invalid object name 'docs'.
<br>Was I supposed to change any part of the script?
Mr. E
Wednesday, April 06, 2005 7:15:00 AM (Pacific Daylight Time, UTC-07:00)
Are you hitting the correct database? If there is no Docs table then something is wrong.
Wednesday, April 06, 2005 7:30:00 AM (Pacific Daylight Time, UTC-07:00)
I tried adding the trigger to the Docs table from Enterprise Manager. I did a right mouse click on the Docs table and went to All Task and then Manage Triggers. I can also run a select statement off the Docs table and queries the table fine.
Mr. E
Wednesday, April 06, 2005 7:51:00 AM (Pacific Daylight Time, UTC-07:00)
Hmmm.. I'm not familiar with how EM adds triggers. The scripts above were ran in Query Analyzer. So I'm not sure why you're getting errors, but you might try using QA instead of EM.
Wednesday, April 06, 2005 8:12:00 AM (Pacific Daylight Time, UTC-07:00)
This is the error message I receive with QA
<br>Server: Msg 213, Level 16, State 4, Procedure doc_recycle, Line 5
<br>Insert Error: Column name or number of supplied values does not match table definition.
<br>
<br>This is Line 5
<br> insert into RecycledDocs
<br>I created RecycledDocs per the first script and I can see the table in the Object Browser. I copied the columns but none of the Docs table properties.
Mr. E
Wednesday, April 06, 2005 9:01:00 AM (Pacific Daylight Time, UTC-07:00)
The "select * into RecycledDocs from Docs where 1 = 0" should create the RecycledDocs table for you. Once that table is created this way the insert into statement in the trigger should work correctly. If it isn't working then I would verify the columns based on what is in the trigger. Make sure your RecycledDocs table has all those column that are defined in the trigger.
Monday, August 01, 2005 7:54:00 PM (Pacific Daylight Time, UTC-07:00)
You are getting the error because SQL server is case sensative and one of the column names is missing. Use this code for the trigger creation, it's with the corrections.
<br>
<br>create trigger doc_recycle on docs instead of delete
<br>as
<br> delete RecycledDocs
<br> where Id in (select Id from Deleted)
<br> insert into RecycledDocs
<br> select Id, SiteId, DirName, LeafName, WebId, ListId, DoclibRowId,
<br> Type, Size, MetaInfoSize, Version, UIVersion, Dirty, CacheParseId,
<br> DocFlags, ThicketFlag, charset, timeCreated, TimeLastModified,
<br> NextToLastTimeModified, MetaInfoTimeLastModified, TimeLastWritten,
<br> SetupPath, CheckOutUserId, CheckOutDate, CheckOutExpires, CheckOutSize,
<br> VersionCreatedSinceSTCheckout, LTCheckOutUserId, VirusVendorID,
<br> VirusStatus, VirusInfo, MetaInfo, Content, CheckOutContent, Extension
<br> from Docs
<br> where Type = 0 and Id in (select Id from deleted)
<br> delete Docs
<br> where Id in (select Id from Deleted)
Gil Bar
Wednesday, August 03, 2005 9:08:00 PM (Pacific Daylight Time, UTC-07:00)
Or you can use:
<br>
<br>create trigger doc_recycle on docs instead of delete
<br>as
<br>delete RecycledDocs
<br>where Id in (select Id from Deleted)
<br>insert into RecycledDocs
<br>select *
<br>from Docs
<br>where Type = 0 and Id in (select Id from deleted)
<br>delete Docs
<br>where Id in (select Id from Deleted)
Thursday, August 25, 2005 4:27:00 AM (Pacific Daylight Time, UTC-07:00)
This is all great, but how do you get the document back into the library? I have created a stoerd procedure that inserts the record back into the Docs table and calls the proc_QMChangeSiteDiskUsedAndContentTimestamp procedure to update the disk utilization for the library. This all executes fine.
<br>
<br>My problem is that I don't see the document listed in the library. I CAN see it in Explorer view, but when I open the document, I can't save it back to the library with the same name. I get a "bad URL" error in Office.
<br>
<br>What more must I do to get the document completely inserted back into the library?
Mike
Thursday, August 25, 2005 4:30:00 AM (Pacific Daylight Time, UTC-07:00)
THE FOLLOWING CODE WILL CREATE A TABLE FROM WHICH YOU MAY RESTORE RECORDS BACK TO THE Docs TABLE. NOTE IT CONTAINS AN EXTRA COLUMN DeletedDate USED TO RECORD THE DOCUMENT DELETION DATE.
<br>
<br>
<br>
<br>CREATE TABLE [dbo].[RecycledDocs] (
<br> [Id] [uniqueidentifier] NOT NULL ,
<br> [SiteId] [uniqueidentifier] NOT NULL ,
<br> [DirName] [nvarchar] (256) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL ,
<br> [LeafName] [nvarchar] (128) COLLATE Latin1_General_CI_AS_KS_WS NOT NULL ,
<br> [WebId] [uniqueidentifier] NOT NULL ,
<br> [ListId] [uniqueidentifier] NULL ,
<br> [DoclibRowId] [int] NULL ,
<br> [Type] [tinyint] NOT NULL ,
<br> [Size] [int] NULL ,
<br> [MetaInfoSize] [int] NULL ,
<br> [Version] [int] NULL ,
<br> [UIVersion] [int] NOT NULL CONSTRAINT [DEFAULT_UIVersion_Recycled] DEFAULT (1),
<br> [Dirty] [bit] NULL ,
<br> [CacheParseId] [uniqueidentifier] NULL ,
<br> [DocFlags] [tinyint] NULL ,
<br> [ThicketFlag] [bit] NULL CONSTRAINT [DF__RecycledDocs__ThicketFla__7A9C383C] DEFAULT (0),
<br> [CharSet] [int] NULL ,
<br> [TimeCreated] [datetime] NOT NULL ,
<br> [TimeLastModified] [datetime] NOT NULL ,
<br> [NextToLastTimeModified] [datetime] NULL ,
<br> [MetaInfoTimeLastModified] [datetime] NULL ,
<br> [TimeLastWritten] [datetime] NULL ,
<br> [SetupPath] [nvarchar] (255) COLLATE Latin1_General_CI_AS_KS_WS NULL ,
<br> [CheckoutUserId] [int] NULL ,
<br> [CheckoutDate] [datetime] NULL ,
<br> [CheckoutExpires] [datetime] NULL ,
<br> [CheckoutSize] [int] NULL ,
<br> [VersionCreatedSinceSTCheckout] [bit] NOT NULL CONSTRAINT [DF__RecycledDocs__VersionCre__7B905C75] DEFAULT (0),
<br> [LTCheckoutUserId] AS (case when ([DocFlags] & 32 = 0) then null else [CheckoutUserId] end) ,
<br> [VirusVendorID] [int] NULL ,
<br> [VirusStatus] [int] NULL ,
<br> [VirusInfo] [nvarchar] (255) COLLATE Latin1_General_CI_AS_KS_WS NULL ,
<br> [MetaInfo] [image] NULL ,
<br> [Content] [image] NULL ,
<br> [CheckoutContent] [image] NULL ,
<br> [Extension] AS (case when ([DoclibRowId] is not null and (charindex(N'.',[LeafName] COLLATE Latin1_General_BIN) > 0)) then (right([LeafName],(charindex(N'.',reverse([LeafName]) COLLATE Latin1_General_BIN) - 1))) else N'' end) ,
<br> [DeletedDate] [datetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
<br> CONSTRAINT [RecycledDocs_PK] PRIMARY KEY CLUSTERED
<br> (
<br> [SiteId],
<br> [DirName],
<br> [LeafName]
<br> ) ON [PRIMARY] ,
<br> CONSTRAINT [RecycledDocs_IdUnique] UNIQUE NONCLUSTERED
<br> (
<br> [Id]
<br> ) ON [PRIMARY]
<br>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
<br>GO
Mike
Wednesday, August 31, 2005 3:51:00 AM (Pacific Daylight Time, UTC-07:00)
This is all great, but how do you get the document back into the library? I have created a stoerd procedure that inserts the record back into the Docs table and calls the proc_QMChangeSiteDiskUsedAndContentTimestamp procedure to update the disk utilization for the library. This all executes fine.
<br>
<br>My problem is that I don't see the document listed in the library. I CAN see it in Explorer view, but when I open the document, I can't save it back to the library with the same name. I get a "bad URL" error in Office.
<br>
<br>What more must I do to get the document completely inserted back into the library?
Mike
Thursday, February 23, 2006 10:28:03 PM (Pacific Standard Time, UTC-08:00)
I don't think that this solution is supported by Microsoft.
N
Monday, May 01, 2006 2:06:01 PM (Pacific Daylight Time, UTC-07:00)
Did anyone already had a look on the "Document Library with Recycle Bin" from Dark Blue Duck?
It’s free (after you register with them): http://www.darkblueduck.com

paul
paul richard
Thursday, November 09, 2006 7:26:48 AM (Pacific Standard Time, UTC-08:00)
Look at the stored proc proc_AddDocument. At the very bottom, it calls proc_QMChangeSiteDiskUsedAndContentTimestamp, but it also called proc_ResyncWelcomeLinks (which might have something to do with the doc not showing up in standard views) and proc_UpdateAttachmentsFlag.

Instead of trying to copy the row back to the Docs table on your own, I'd call proc_AddDocument...
Charles Holmes
Friday, December 01, 2006 10:10:34 PM (Pacific Standard Time, UTC-08:00)
Tutorials & Code
hyrdorlesto
Comments are closed.