Thursday Gift: Copying SharePoint Content Databases

If you are like me and can’t believe that Microsoft doesn’t have a better answer when it comes to copy SharePoint content databases than:

– Build a new farm
– Do a site collection backup/restore (fun times when you have large site collections)
– Do an export/import of the webs (won’t get into that one!)

I now have a solution for you…

Bear in mind that this is NOT SUPPORTED BY MICROSOFT. Use only in dev/QA/lab environments and never in production.

Here’s the process:

– Backup the database using standard SQL Server backup tools (Built-in, DPM, Commvault, etc.)
– Restore the database on your target server using the desired name
– Run the following SQL script:

DECLARE @newSiteId UNIQUEIDENTIFIER,
@oldSiteId UNIQUEIDENTIFIER

DECLARE siteCollections CURSOR FOR
SELECT id
FROM AllSites

OPEN siteCollections

FETCH NEXT FROM siteCollections INTO @oldSiteId

WHILE @@FETCH_STATUS = 0
BEGIN
SET @newSiteId = NEWID()

PRINT ‘Old SiteId:’ + CAST(@oldSiteId AS VARCHAR(128))
PRINT ‘New SiteId: ‘ + CAST(@newSiteId AS VARCHAR(128))

UPDATE AllSites SET Id = @newSiteId WHERE Id = @oldSiteId
UPDATE AllDocs SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE AllDocStreams SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE AllDocVersions SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE AllLinks SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE AllListUniqueFields SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE AllLookupRelationships SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE AllUserData SET tp_SiteId = @newSiteId WHERE tp_SiteId = @oldSiteId
UPDATE AllUserDataJunctions SET tp_SiteId = @newSiteId WHERE tp_SiteId = @oldSiteId
UPDATE AllWebParts SET tp_SiteId = @newSiteId WHERE tp_SiteId = @oldSiteId
UPDATE AllWebs SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE AuditData SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE BuildDependencies SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE ComMd SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE ContentTypes SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE ContentTypeUsage SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE CustomActions SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE Deps SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE EventCache SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE EventLog SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE EventReceivers SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE Features SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE GroupMembership SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE Groups SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE HT_Cache SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE ImmedSubscriptions SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Albanian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Arabic_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Azeri_Cyrillic_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Azeri_Latin_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Chinese_Hong_Kong_Stroke_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Chinese_PRC_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Chinese_PRC_Stroke_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Chinese_Taiwan_Bopomofo_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Chinese_Taiwan_Stroke_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Croatian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Cyrillic_General_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Czech_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Danish_Norwegian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Divehi_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Estonian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Finnish_Swedish_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_French_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Georgian_Modern_Sort_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_German_PhoneBook_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Greek_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Hebrew_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Hindi_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Hungarian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Hungarian_Technical_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Icelandic_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Indic_General_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Japanese_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Japanese_Unicode_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Kazakh_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Korean_Wansung_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Korean_Wansung_Unicode_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Latin1_General_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Latvian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Lithuanian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Lithuanian_Classic_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Macedonian_FYROM_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Modern_Spanish_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Polish_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Romanian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Slovak_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Slovenian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Syriac_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Tatar_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Thai_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Traditional_Spanish_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Turkish_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Ukrainian_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Uzbek_Latin_90_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NameValuePair_Vietnamese_CI_AS SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE NavNodes SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE Perms SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE Personalization SET tp_SiteId = @newSiteId WHERE tp_SiteId = @oldSiteId
UPDATE RecycleBin SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE RoleAssignment SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE Roles SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE SchedSubscriptions SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE ScheduledWorkItems SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE SiteDeletion SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE SiteQuota SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE SiteVersions SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE SolutionResourceUsageDaily SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE SolutionResourceUsageLog SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE SolutionResourceUsageWindowed SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE Solutions SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE StorageMetrics SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE StorageMetricsChanges SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE UserInfo SET tp_SiteID = @newSiteId WHERE tp_SiteID = @oldSiteId
UPDATE WebPartLists SET tp_SiteId = @newSiteId WHERE tp_SiteId = @oldSiteId
UPDATE Workflow SET SiteId = @newSiteId WHERE SiteId = @oldSiteId
UPDATE WorkflowAssociation SET SiteId = @newSiteId WHERE SiteId = @oldSiteId

FETCH NEXT FROM siteCollections INTO @oldSiteId
END

CLOSE siteCollections
DEALLOCATE siteCollections

– Grant the proper access rights to the DB
– Attach the content database in SharePoint
– Done!

We have not performed full regression testing on this and some things may be broken. Use at your own risk.

Enjoy!

Advertisements

2 thoughts on “Thursday Gift: Copying SharePoint Content Databases

    • Hi Scott,

      I’m not sure I understand the comment. Just to clarify, we do not use this process to graduate changes from dev/qa/prod as we migrate dev changes through standard SharePoint solution packages. We don’t graduate content by copying content databases from dev->prod.

      This process is strictly to refresh content (mostly documents) for testing purposes in dev or QA.

      It’s definitely unsupported but has been a time saver for us so far. Use at your own risk.

      Mathieu

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