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 stored as binary, this post will help you.

The script I have included will export all the content from the SharePoint Content Database to the file structure set by SharePoint. This script should work as it is 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 will have to do is enable the Ole Automation Procedures. This will allow SQL Server to write to the file system. You will also have to check that you 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;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures‘, 1;
GO
RECONFIGURE;
GO

Now that you have access to the file system you can run the following script. Extracting files from database:

--DECLARING THE CURSOR, THIS IS THE ITEMS YOU WANT TO RUN THE EXTRACTING ON
DECLARE CURSOR_Images CURSOR FOR (SELECT Id	FROM [dbo].[AllDocs])   --DECLARE THE TYPE OF THE COLUMN YOU SELECTED ABOVE
DECLARE @ImageID uniqueidentifier;   --START THE CURSOR AND RUN THROUGH ALL THE ITEMS IN CURSOR_Images
OPEN CURSOR_Images
FETCH NEXT FROM CURSOR_Images INTO @ImageID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  --DECLARE THE VARIABLE THAT WILL KEEP THE BINARY DATA
  DECLARE @ImageData varbinary(MAX);
  --SELECT THE BINARY DATA AND SET IT TO @ImageData.  THE BINARY DATA FOR ALLDOCS ARE LOCATED IN ALLDOCSTREAMS
  --AND THE ID IS THE SAME AS IN ALLDOCS
  SELECT @ImageData = (SELECT 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 WAS SAVED IN AND CHANGE REPLACE THE / WITH \ TO BE USED IN FILESYSTEM
  DECLARE @DIRPATH NVARCHAR(MAX);
  SET @DIRPATH = REPLACE((SELECT  DirName 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 WILL BE STORED
  DECLARE @FullPathToOutputFile NVARCHAR(2048);
  SELECT @FullPathToOutputFile = @Path + ‘\‘ + @Filename;   --SAVE THE FILE TO THE FILE SYSTEM
  DECLARE @ObjectToken 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 IN THE CURSOR
  FETCH NEXT FROM CURSOR_Images INTO @ImageID
END
CLOSE CURSOR_Images
DEALLOCATE CURSOR_Images

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

EXTRACT FILES AND IMAGES FROM A SHAREPOINT CONTENT DATABASE,古老的榕树,5-wow.com