EXTRACT FILES and IMAGES from A SHAREPOINT CONTENT DATABASE

If you ever had the problem where you need to extract files from a SharePoint Content database or normal SQL database stor Ed as binary, this post would help you.

The script I have included would export all the content from the SharePoint content Database to the file structure set by S Harepoint. This script, should work as it was on your SharePoint Database. If you modify this script a little you can use it to extract and binary data from SQL to files.
problem:The SharePoint Content Database got corrupted because of a third-party add-on. This caused all kinds of problems as the files could not be found anymore. The Content Database was still accessible through SQL Server.
Solution:To fix this problem we had to extract all the current images and documents to a file system. The first thing you would have the "to do" to enable the Ole Automation procedures. This is allow SQL Server to write to the file system. You'll also have the correct permissions and access to write to the file system from SQL Server.
enabling file writing:Run the following script in SQL:

sp_configure ' show advanced options ', 1; Goreconfigure; Gosp_configure ' Ole Automation procedures ', 1; Goreconfigure; GO

Now, you can run the following script with access to the file system. extracting files from database:

--declaring the cursor, this is the ITEMS of the want to RUN the extracting Ondeclare cursor_images CURSOR for (SELECT idfrom [dbo]. [Alldocs])    --declare The TYPE of the COLUMN you SELECTED abovedeclare @ImageID uniqueidentifier;  --start the CURSOR and RUN THROUGH all the ITEMS in Cursor_imagesopen Cursor_imagesfetch NEXT from cursor_images into @ImageIDWHILE ( @ @FETCH_STATUS <>-1) BEGIN--declare the VARIABLE that would KEEP the BINARY DATA DECLARE @ImageData varbinary (MAX)  ;  --select the BINARY DATA and SET IT to @ImageData. The BINARY DATA for Alldocs was LOCATED in Alldocstreams--and the ID was the same as in Alldocs SELECT @ImageData = (SEL ECT TOP 1 CONVERT (varbinary (MAX), Content, 1) from [dbo]. [Alldocstreams] WHERE Id = @ImageID ORDER by internalversion ASC);  --get The location of the DIRECTORY the FILES were SAVED in  and change REPLACE the/with \ To is used in FILESYSTEM DECLARE @DIRPATH NVARCHAR (MAX);  SET @DIRPATH = REPLACE ((SELECTDirName from [dbo].  [Alldocs]  WHERE Id = @ImageID), '/', ' \ ');  --set the PATH DECLARE @Path nvarchar (1024); SELECT @Path = ' C:\Export\ ' + @DIRPATH + ' \ ';  --create the directories EXEC master.dbo.xp_create_subdir @Path  ;  --get The file NAME of the file from Leafname DECLARE @Filename NVARCHAR (1024); Select @Filename = (select Leafname from [dbo].[ Alldocs] WHERE id = @ImageID);  --set the full PATH for WHERE the FILES would be a STORED DECLARE @FullPathToOutp  Utfile NVARCHAR (2048); SELECT @FullPathToOutputFile = @Path + ' \ ' + @Filename;  --save The file to the file SYSTEM DECLARE @ObjectTok En INT EXEC sp_oacreate ' ADODB.  Stream ', @ObjectToken OUTPUT;  EXEC sp_OASetProperty @ObjectToken, ' TYPE ', 1;  EXEC sp_OAMethod @ObjectToken, ' OPEN ';  EXEC sp_OAMethod @ObjectToken, ' WRITE ', NULL, @ImageData;  EXEC sp_OAMethod @ObjectToken, ' SaveToFile ', NULL, @FullPathToOutputFile, 2;  EXEC sp_OAMethod @ObjectToken, ' Close '; EXEC sp_OADestroy @ObjectToken;  --loop to the next ENTRY on the CURSOR FETCH NEXT from cursor_images into @ImageIDENDCLOSE CUR Sor_imagesdeallocate cursor_images

After running this script it could take some time depending on the size of the SharePoint Content Database. You'll see some errors so you can ignore. When done has a look in the folder you extracted the files and you'll find all your files in the directory. If you had any questions don ' t hesitate to ask. I'll be glad to help where I can.  you can also find me on Skype:corvitech