Extract .wsp files from SharePoint Config Database

January 14, 2013 · 2 min read

I recently had a customer with the need to extract a .wsp (SharePoint Solution) File from the SharePoint Farm Solutions because the source file disappeared mysteriously…

As SharePoint loads the .wsp file into the config database (SharePoint_Config in most SharePoint environments – although it should be a name that you can recognize easily – you don’t name you files dok1, dok2, etc.) and stores it as binary content, it is not too hard to extract. When uploading the farm solution with Add-SPSolution or stsadm -o addsolution, the file is stored in the Binaries-Table of the SharePoint_Config Database.

As usual, be careful when querying the SharePoint Config/Content Database directly – this is an unsupported action.

What you get is the FileImage and a beautiful GUID representing the link to the object, which can be found in the Objects Table.

Now that you have your binary content and the original filename of your .wsp, there are a lot of scripts out there to extract the binary content. I modified one of the SQL Server Team Scripts (a quite sophisticated one that uses an ADODB.Stream) to meet my needs. Be sure to allow OLE Automation Procedures on your SQL Server, otherwise you will get lots of errors.

sp_configure'show advanced options', 1;

GO

RECONFIGURE;

EXEC sp_configure 'Ole Automation Procedures',1

reconfigure

GO

declare @ObjectToken INT

declare @TheSolution VARBINARY(MAX)

declare @Save2File VARCHAR(255)

declare @FileName varchar(255)

DECLARE TheCursor CURSOR FAST_FORWARD FOR

select fileimage, name from binaries, objects

where objects.id=binaries.objectid and name like ’%.wsp' order by name

        

OPEN TheCursor

FETCH NEXT FROM TheCursor INTO @TheSolution, @FileName

WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @Save2File = 'c:\Temp' + @FileName

        PRINT @Save2File

        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT – Creates an instance of an OLE object.

        EXEC sp_OASetProperty @ObjectToken, 'Type', 1 – Sets a property of an OLE object to a new value.

        EXEC sp_OAMethod @ObjectToken, 'Open' – Calls a method of an OLE object.

        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @TheSolution

        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @Save2File, 2

        EXEC sp_OAMethod @ObjectToken, 'Close'

        EXEC sp_OADestroy @ObjectToken    – Destroys a created OLE object.

        FETCH NEXT FROM TheCursor INTO @TheSolution, @FileName

    END

CLOSE TheCursor

DEALLOCATE TheCursor

Finally, your c:\temp directory contains all the .wsp files from your SharePoint Farm.

Markus