Extract all content to disk from a SPS2007 content DB using PHP

Today I ran into a problem. We needed to migrate a huge amount of data from an old SharePoint 2007 content database without the availability of the MOSS front-end. All i had was the database and a corrupted sharepoint install that wasnt going to help me allot.

To overcome this problem I decided to write a little PHP application that would do this task for me. I allready had WAMP setup on my desktop, so i figured this to be the quickest route. Then i figured, maybe other people face this problem as well. So here it is, the code, and some helpers to get you going.


<?php
/**
* @name           : index.php - MSSql Content connector
* @Author        : Chris Gralike
* @version        :
* @copyright     : WETFYWTDWI - what ever ** you want to do with it, no guarantees🙂
*  This script ONLY READS the database tables, so dont give it more permissions🙂
*/

// What to search for in the directory structure.
$search = '';
// Where too put the files
$createdir = './Downloaded';
// What server too connect to.
$ServerName = 'amisnt05.amis.local';
// Database connection parameters.
$connectionInfo = array('Database' => 'MOSS_PROD_WSS_Content_WebApp02',
'UID' => 'php_login',
'PWD' => 'welcome12345678');
// This can be a very long task to complete, so disable the timelimit.
set_time_limit(0);
// Create a connection
$conn = sqlsrv_connect($ServerName, $connectionInfo)
or die( print_r( sqlsrv_errors(), true));

// The SQL statment to query the AllDocs tables.
$tsql = "SELECT dbo.AllDocs.Id,
dbo.AllDocs.SetupPath,
dbo.AllDocs.LeafName,
dbo.AllDocs.DirName,
dbo.AllDocs.SetupPath,
dbo.AllDocs.Extension,
dbo.AllDocs.ExtensionForFile,
dbo.AllDocStreams.Id as StreamId,
dbo.AllDocStreams.Content
FROM dbo.AllDocs
RIGHT OUTER JOIN dbo.AllDocStreams ON dbo.AllDocs.Id = dbo.AllDocStreams.Id
WHERE AllDocs.DirName LIKE '%{$search}%'
AND AllDocs.SetupPath IS NULL
AND AllDocs.Extension != ''
";
// The result set
$result = sqlsrv_query($conn, $tsql);

// Process the results
while($row = sqlsrv_fetch_array($result,  SQLSRV_FETCH_ASSOC)){
// When create is true, then it will create the folders in
// in the foreach
$create = false;
$dirptr = $createdir;

// Find the folders and recreate them starting from the searchstring.
$folders = explode('/', $row['DirName']);
foreach ($folders as $val){
if($val == $search || $create == true || empty($search)){
$create = true;
$dirptr .= '/'.$val;
if(!is_dir($dirptr)){
mkdir($dirptr);
echo "INFO: created $dirptr <br/>";
}else{
echo "WARN: skipping $dirptr allready exists. <br />";
}
}
}

// Recreate the file
$filepath = $dirptr.'/'.$row['LeafName'];
if(!is_file($filepath)){
touch($filepath);
}
if($fp = fopen($filepath,'w')){
fwrite($fp, $row['Content']);
echo "INFO: file {$row['LeafName']} written. <br />";
}else{
echo "ERROR: file {$row['LeafName']} could not be written in $filepath. <br />";
}
fclose($fp);
}
// Close the database connection.
sqlsrv_close($conn);
?>

Simply configure the first vars in the script and run the file. It might take a huge while before you get some output.

TIP: Use the $search to narrow down the query a bit.
It searches the DirName (I.e. Site\DocLib\Folder\SubFolder\)

The output will look like this.

INFO: created ./Downloaded/SearchCenter
INFO: created ./Downloaded/SearchCenter/Pages
INFO: file facetedsearch.aspx written.
WARN: skipping ./Downloaded/SearchCenter allready exists.
WARN: skipping ./Downloaded/SearchCenter/Pages allready exists.
INFO: file resultskeyword.aspx written.

ANY THOUGHTS, OR NEED SOME HELP?
Then please leave a comment🙂

WARNING!: YOU NEED THE Microsoft MSSQL DRIVER FOR PHP, not the old php equivalent.
here are some tips on where to get it. My version was php 5.3.8

First off, mssql isnt supported out of the box anymore. when using PHP 5.2 and up, you need to get the Microsoft for PHP driver. Check this site for more information : http://sqlsrvphp.codeplex.com/

Its a bit of an hassle ill give you that.
Challenge: I needed 1.5hours to find the correct Lib,Install,Coding info and get it working.

Basically it requires you to download the native client, the drivers and an correct update of the php.ini your wamp instance is using.

Tip: Use <?php phpinfo() ?> to find the right version for your PHP compilation.
Search for : PHP Extension Build : API20090626,TS,VC9

DIFFERENT SHAREPOINT VERSION?!

Be sure to verify the SQL query inside the $tsql=” var and alter it accordingly. The other part should be pretty straight forward.

About Chris Gralike

Momenteel ben ik manager van de afdeling business continuity bij de zakelijke IT dienstverlener AMIS Services BV. Sinds 2003 ben ik actief in de ICT branche. Tussen 2003 en nu heb ik verschillende rollen vervuld. In de rollen: systeem- en netwerkbeheer, system engineer, servicemanager en nu practice manager ben ik in contact gekomen met uiteenlopende technologieën, methodologieën, ideeën, oplossingen en innovaties. Een rijke ervaring waarmee ik de klanten van Conclusion en AMIS elke dag probeer te ondersteunen. Mijn credo: 'Altijd opzoek een win-win tussen business en technologie.'

Posted on January 2, 2013, in PHP, Tooling and tagged , , , , , , , , , , , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: