In this article, I shall run through the SQL database transfer process for SharePoint 2007. The addition we are making here is running a validation script before and after. We actually backed up and restored the SQL server databases (as apposed to detach and reattach) so that the existing service could be reinstated quickly and easily and reduce any risk that SQL could not connect to the database again. This also fitted well with our existing backup strategy.

In our case we actually moved all servers, including the front-end SharePoint servers, from physical servers, to a virtual set of servers.

To see Microsoft best practice on moving SharePoint content databases, see:

Move content databases (Office SharePoint Server 2007)
http://technet.microsoft.com/en-us/library/cc263037(office.12).aspx

To see Microsoft best practice on moving all SharePoint databases, see:

Move all databases (Office SharePoint Server 2007)
http://technet.microsoft.com/en-us/library/cc512725(office.12).aspx

NB Post the SharePoint Infrastructure Update, I don't believe you need to run the "preparetomove" stsadm function. I'll try and dig out a reference for this.

The diagram below, shows the overall process that is used.



For my client, it was very important that there was no data loss during the migration. To ensure this, I created some SQL scripts to validate that the information in the database was the same before and after the data transfer. I actually created a number of scripts (see below for attachments) for a variety of different databases that were involved in the transfer, however, in this document we are referring to content databases only, so the script you would use is validation-content.sql.

The validation script for the content databases is as follows:

USE [WSS_Content]
SELECT TOP 1 tp_Modified AS LastUserDataModified FROM [dbo].[AllUserData] ORDER BY tp_ID DESC
SELECT TOP 1 TimeLastModified AS LastAllDocsModified FROM [dbo].[AllDocs] ORDER BY Id DESC
SELECT TOP 1 Id AS LastAllDocStreamId FROM [dbo].[AllDocStreams] ORDER BY Id DESC
SELECT COUNT(*) AS DocStreamCount FROM [dbo].[AllDocStreams]
SELECT COUNT(*) AS AllDocsCount FROM [dbo].[AllDocs]
SELECT COUNT(*) AS AllListsCount FROM [dbo].[AllLists]
SELECT COUNT(*) AS AllUserDataCount FROM [dbo].[AllUserData]
SELECT COUNT(*) AS SitesCount FROM [dbo].[Sites]
SELECT COUNT(*) AS WebsCount FROM [dbo].[Webs]
SELECT SiteId, Id, FullUrl, Title, Author, TimeCreated FROM [dbo].[Webs] WHERE (ParentWebId IS NULL) ORDER BY Title
GO

You would obviously need to change [WSS_Content] to be the name of your content database. You can save the results to a file such as WSS_Content.rpt (in SQL Studio Manager use Query > Results To > Results to file). I would recommending saving this file to a directory that is the name of the origin and target server name respectively. A visual comparison of the files should be easy enough, although you can actually use a Powershell script to compare the files in the two different folders if you have a large number of databases to compare.

As you can see in the script, what we're doing is grabbing the last modified date from the AllUserData table, as well as counting the documents within SharePoint - just simple row counts.

During a rehearsal of the data transfer, we actually saw an inconsistency in the validation script output (last modified date was different and there was one more document), this was due to after the services being switched off on the source servers (where the business did not want to disconnect SharePoint from the databases using stsadmin, but just turn off the services and stop IIS), they actually had auto-recovery turned on and switched themselves back on, allowing users to still access the site, and make updates to it. To ensure this doesn't happen, you could switch off the front-end servers. If you follow the process above however, you should not see this.

NB I actually scripted this process completely so that you can modify a configuration file and it runs through this process using PowerShell. In addition, you could also run an additional step to confirm the status of your various sites using the PowerShell script that I previously posted.