My good friend Todd Klindt pointed me to this posting written by Mark Jen which showed some sample code to export site content from a SharePoint Content Database directly.
!!! DISCLAIMER !!!!
Everyone who knows me, knows that I am TOTALLY AGAINST doing direct database calls to the SharePoint databases UNLESS it is for complete disaster recovery, or complete offline reporting. This is one of the reasons DeliverPoint will NEVER have any direct SharePoint database calls in it.
For additional information, see the following Microsoft articles:
“Support for changes to the databases that are used by Office Server products and by Windows SharePoint Services”
http://support.microsoft.com/kb/841057/en-us
“SharePoint Database Access”
http://msdn.microsoft.com/en-us/library/bb861829.aspx
Do NOT run this code against Live SharePoint databases.
Ok, now with that out the way…The problem with Mark’s original code, is that it was written for V2 (WSS 2.0/SPS2003), not V3 (WSS 3.0/MOSS 2007). Also, his query is targeted at specific files (such as .doc/.ppt/.etc).
With a couple of slight changes, I updated the query and code to work with a V3 content database, and also just have it export everything.
Since his code was also just a snippit, I’m placing an updated version with complete source here.
// BEGIN SPDBEX.CS
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace spdbex
{
class Program
{
static void Main(string[] args)
{
// replace this string with your
// Sharepoint content DB connection string
string DBConnString =
"Server=DATABASESERVER;" +
"Database=CONTENTDBNAME;Trusted_Connection=True;";
// create a DB connection
SqlConnection con = new SqlConnection(DBConnString);
con.Open();
// the query to grab all the files.
SqlCommand com = con.CreateCommand();
com.CommandText = "SELECT ad.SiteId, ad.Id, ad.DirName," +
" ad.LeafName, ads.Content" +
" FROM AllDocs ad, AllDocStreams ads" +
" WHERE ad.SiteId = ads.SiteId" +
" AND ad.Id = ads.Id" +
" AND ads.Content IS NOT NULL" +
" Order by DirName";
// execute query
SqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
// grab the file’s directory and name
string DirName = (string)reader["DirName"];
string LeafName = (string)reader["LeafName"];
// create directory for the file if it doesn’t yet exist
if (!Directory.Exists(DirName))
{
Directory.CreateDirectory(DirName);
Console.WriteLine("Creating directory: " + DirName);
}
// create a filestream to spit out the file
FileStream fs = new FileStream(DirName + "/" + LeafName,
FileMode.Create, FileAccess.Write);
BinaryWriter writer = new BinaryWriter(fs);
// depending on the speed of your network,
// you may want to change the buffer size (it’s in bytes)
int bufferSize = 1000000;
long startIndex = 0;
long retval = 0;
byte[] outByte = new byte[bufferSize];
// grab the file out of the db one chunk
// (of size bufferSize) at a time
do
{
retval = reader.GetBytes(4, startIndex, outByte, 0,
bufferSize);
startIndex += bufferSize;
writer.Write(outByte, 0, (int)retval);
writer.Flush();
} while (retval == bufferSize);
// finish writing the file
writer.Close();
fs.Close();
Console.WriteLine("Finished writing file: " + LeafName);
}
// close the DB connection and whatnots
reader.Close();
con.Close();
}
}
}
// END SPDBEX.CS
Be sure to change the DATABASESERVER and CONTENTDBNAME for the connection string, then simply execute the C# compiler on your server as follows:
%WINDIR%\Microsoft.NET\Framework\v2.0.50727\csc /target:exe /out:spdbex.exe spdbex.cs
The code in Marks’ original form, does not allow you to specify any target directory etc, and just begins exporting in the CURRENT DIRECTORY you’re in for any root site, so be sure to run this from a directory/folder you create to hold the data from the root.
I’ll ping Mark and see if he’ll allow me to take that code and polish it up with other options (Such as site selection, specific folder targeting etc) if he doesn’t want to update it.
HTH
- Keith
Thanks Keith. This works great.
tk
Comment by Todd Klindt — July 6, 2008 @ 11:25 pm
[...] Exporting site content from a SharePoint Content Database for recovery purposes [...]
Pingback by Links (7/6/2008) « Steve Pietrek - Everything SharePoint — July 7, 2008 @ 7:44 am
[...] Exporting Site Content From a SharePoint Content Database for Recovery Purposes (Keith Richie) [...]
Pingback by Dew Drop - July 7, 2008 | Alvin Ashcraft's Morning Dew — July 7, 2008 @ 6:46 pm
Keith
Loving it buddy !
Comment by Neil Hodgkinson — July 12, 2008 @ 5:49 am
Thanks for the excellent code – it’s a real life saver!
Comment by Mark Fertig — October 15, 2008 @ 12:58 am
Hi,
Thanks for this code, it’s been really useful. The only problem I have at the moment is we have quite a few folder paths that are longer than 248 characters. Do you know if there is a workaround to this?
Thanks
Comment by Al — November 11, 2008 @ 11:30 pm
The code would need to be adjusted to create a shorter folder path on the file system for each folder found, then create an appropriate reference map that would then be exported (That is if you were still exporting everything in the content database)
Optionally, you could modifiy the Query to and an AND clause to point to a specific Site GUID for the site in question.
Just thoughts at this moment. I’d like to update the code to handle those scenarios, but it’s not something I have time for right at this moment. I’ll make a note of it though.
Comment by Keith Richie — November 11, 2008 @ 11:40 pm
Hi,
Thanks for that. I have one other question. Currently, my SQL instance for the Sharepoint databases is only accessible via Named Pipes (I just can’t get it to work using TCP/IP).
How would I adapt your code to allow me to either used named pipes or a DSN on the Sharepoint server?
(Unfortunately I’m a complete C# novice)
Comment by Al — November 12, 2008 @ 8:46 pm
Hi,
I managed to find some code on the web to allow me to use a DSN. Thanks once again.
Comment by Al — November 12, 2008 @ 9:11 pm
My god… you have just saved my life. This is the only way I was able to get the content after upgrading from WSS3 to MOSS 2007 and experiencing complete meltdown. You are a god among men- a gentleman, a saint!
Comment by John — November 17, 2008 @ 4:48 pm
I deleted a site from the site settings…but then hit cancel. From what I can tell the database is intact…can I rebuild my site with the code above
Comment by Virginia — December 14, 2008 @ 9:57 am
I crashed a sharepoint server during a domain migration but was able to retrieve the files from a DB backup file thanks to your nice script and some tweaking (increased memory and updated query to work by subfolder).
Great work !!!
I will mention you in my next blog article.
Comment by Jehan — March 27, 2009 @ 3:31 pm
Thanks Jehan!
Comment by Keith Richie — March 27, 2009 @ 8:53 pm
Hi,
Just want to make a note for a sharepoint document recovery software
http://www.kerneldatarecovery.com/sharepoint-recovery.html
this one proves helpful to restore and recover from MDF db’s with rae repair and Live repair and recovery from the database. Might prove helpful when you face sharepoint database corruptions.
Comment by techiee — April 25, 2009 @ 1:06 pm
An error occurred when I execute spdbex.exe.
“ArgumentException”
How can I fix this error?
Comment by sungho — June 12, 2009 @ 1:44 pm
I need a bit more context here. the code snippet doesn’t take any arguments.
Do you get a stack trace after the error?
Comment by Keith Richie — June 23, 2009 @ 8:46 pm
Thanks for this post!
Comment by Intelligenz — July 14, 2009 @ 1:05 am
I’m a real newbie at this stuff, I’m trying to learn….but when I run this code i get the following error.
————-
Unhandled Exception: System.Data.SqlClient.SqlException: An error has occurred w
hile establishing a connection to the server. When connecting to SQL Server 200
5, this failure may be caused by the fact that under the default settings SQL Se
rver does not allow remote connections. (provider: Named Pipes Provider, error:
40 – Could not open a connection to SQL Server)
————–
The thing is that sql server is set up for remote connections.
Any ideas???
Comment by Steven — September 11, 2009 @ 11:35 pm
Steven, apologies for the extremely late reply.
Is this the developer version of SQL Server? If so, you need to run the surface area configuration app.
Comment by Keith Richie — October 9, 2009 @ 8:55 pm
Thanks for sharing this information with us …its really helpful…
Comment by srehman — December 24, 2009 @ 1:03 pm
Excellent programming. I must admit I was skeptic about this when I was struggling to get it compiled with pipes. So for the others out there that might have had problems with the pipes like I did… My Scenario..
Server 2008 SP2
SQL 2005 Express
Windows Internal Database (WSS 3.0)
SQL Server Management Studio Express(SMSE)
Open Sharepoint Central Admin. > Application Management > Content Databases.
Click on the Database. Write down its full name or at least enough for you to remember it. (beginning and ending 3 characters of the hash if you left default)
When the database settings page opens, change the status to Offline and check the “Remove Content Database.” Click Ok.
Next you need to attach the database to your SQL Express which has prefered naming scheme for this code.
In SQL SMSE;
Connect to \\.\pipes\MICROSOFT##SSEE\sql\query with the SQL Connection Mgr.
Detach the database in question.
Disconnect from \\.\pipes…
Connect to
Server\SQLEXPRESS (assuming you left default)
Right click on Databases and select Attach. Attach the database from
C:\Windows\SYSMSI\SSEE…
\MSSQL.2005\MSSQL\Data
Continue to your spdbex.cs file from these wonderful lines of code. Thank you Keith for sharing.
Update lines 20/21.
Server=Server\SQLEXPRESS
Database=WSS_Content_…
Compile as instructed.
You will have to navigate your command line to that directory. However the output is optional.
My Command lines:
CD %WINDIR%\Microsoft.NET\Framework\v2.0.50727
csc /target:exe /out:C:\SPDBExport\SharepointSiteNameExport.exe spdbex.cs
I also updated documentation to use this code should I discontinue employment with this company for the next person.
Thanks again Keith.
Best Regards,
Steve K.
Comment by Steve Kline — August 27, 2010 @ 7:05 pm