Blog Archives

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.

Advertisements