Skip to content
Mar 01 10

Copy SharePoint production data to a test environment

In a lot of SharePoint projects you will be required to deploy customizations after the customer has started using the solution. In cases like this it’s very important to test all customizations extensively on a test and/or user acceptance environment before deploying them to production. 
In order to be able to do tests that, to a certain degree, can guarantee that both the current and the new functionality will work on your production environment you need an environment that matches your production environment in a lot of ways.

  • As similar a hardware setup as possible
  • Same Windows Server and SQL Server versions and setup
  • Same SharePoint version and setup
  • Same third party tools installed

All of the above are fairly straightforward to implement using standard tools and processes. However in order to be able to properly test your solution you will also need very similar data on your test, user acceptance and production environment. This is a bit more difficult to achieve. There are several third party tools that you could buy that will help you to move around SharePoint data, but you can also achieve this using free and standard tools and scripts.

If you want to copy data from you production environment to your test environment and you want to environment to still function as it’s supposed to as well you need to take four different steps that, apart from the actual copying of the data also involve preparing your environments for the data move and finalizing them after the data has been copied.

  1. Create the Active Directory accounts that are used in the production environment in the test domain or OU
  2. Backup the SharePoint data from the production environment
  3. Restore SharePoint data on the test environment
  4. Migrate the users in your SharePoint environment from their production version to their test equivalent
  1. To assist with the Active Directory export and import of accounts the first thing I did was download the Quest Free PowerShell Commands for Active Directory here. Next step was to write a PowerShell script to export the users from the Production Active Directory to an XML file. Install the ActiveRolesManagementShell on the domain controller and make sure the add-in is loaded in PowerShell. Now run the export command: 
    add-PSSnapin quest.activeroles.admanagement 
get-QADUser -SearchRoot "OU=Company,OU=ContainerName,DC=DomainName,DC=com" 
       -SerializeValues -SizeLimit 0 | Export-Clixml ADUsers.xml

    The exported XML looks like this: 
    <Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<Employee>
		<S N="objectClass">top,person,organizationalPerson,user</S>
		<S N="objectSid">0105000000000005150000002E2314E71F387BEE9F955DED490E0000</S>
		<S N="name">JSmith@Company.com</S>
		<S N="displayName">John Smith</S>
		<S N="givenName">John</S>
		<S N="whenCreated">09/20/2009 16:11:21</S>
		<S N="objectGUID">84576687B7A0CE49BB67FD71B1BAD046</S>
		<S N="primaryGroupID">513</S>
		<S N="sAMAccountName">JSmith</S>
		<S N="whenChanged">09/25/2009 10:04:52</S>
		<S N="userPrincipalName">JSmith@Company.com</S>
		<S N="mail">john.smith@company.com</S>
		<S N="sn">Smith</S>
	</Employee>
	<Employee>
		<S N="objectClass">top,person,organizationalPerson,user</S>
		<S N="objectSid">0105000000000005150000002E2314E71F387BEE9F955DED0D1A0000</S>
		<S N="name">JDoe@Company.com</S>
		<S N="displayName">Jane Doe</S>
		<S N="givenName">Jane</S>
		<S N="whenCreated">09/20/2009 17:01:04</S>
		<S N="objectGUID">A631C68C086D5A488565F99898F1E5CD</S>
		<S N="primaryGroupID">513</S>
		<S N="sAMAccountName">JDoe</S>
		<S N="whenChanged">01/13/2010 15:47:55</S>
		<S N="userPrincipalName">JDoe@Company.com</S>
		<S N="mail">jane.doe@company.com</S>
		<S N="sn">Doe</S>
	</Employee>
</Employees>

    The XML will get a bit easier to process if you transform it slightly by using a short XSLT script: 
    <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
	<Employees>
	<xsl:apply-templates />
	</Employees>
</xsl:template>

<xsl:template match="Employee">
	<Employee>
		<xsl:apply-templates/>
	</Employee>
</xsl:template>
<xsl:template match="S">
	<xsl:text disable-output-escaping="yes">&lt;</xsl:text>
	<xsl:value-of select="@N"/>
	<xsl:text disable-output-escaping="yes">&gt;</xsl:text>
	<xsl:value-of select="."/>
	<xsl:text disable-output-escaping="yes">&lt;/</xsl:text>
	<xsl:value-of select="@N"/>
	<xsl:text disable-output-escaping="yes">&gt;</xsl:text>
</xsl:template>

</xsl:stylesheet>

    After we transformed the XML we also do some text replacements. In my case the test environment uses the same domain as the production environment is. Because of this I have to create new usernames. Because I don’t want to send the users email from the test environment I change the email addresses to non-existing or test email addresses.
    The XML now looks like this: 
    <Employees>
	<Employee>
		<objectClass>top,person,organizationalPerson,user</objectClass>
		<objectSid>0105000000000005150000002E2314E71F387BEE9F955DED490E0000</objectSid>
		<name>JSmith@CompanyTest.com</name>
		<displayName>John Smith</displayName>
		<givenName>John</givenName>
		<whenCreated>09/20/2009 16:11:21</whenCreated>
		<objectGUID>84576687B7A0CE49BB67FD71B1BAD046</objectGUID>
		<primaryGroupID>513</primaryGroupID>
		<sAMAccountName>JSmith_Test</sAMAccountName>
		<whenChanged>09/25/2009 10:04:52</whenChanged>
		<userPrincipalName>JSmith@CompanyTest.com</userPrincipalName>
		<mail>doesnotexist@companyTest.com</mail>
		<sn>Smith</sn>
	</Employee>
	<Employee>
		<objectClass>top,person,organizationalPerson,user</objectClass>
		<objectSid>0105000000000005150000002E2314E71F387BEE9F955DED490F0000</objectSid>
		<name>JDoe@CompanyTest.com</name>
		<displayName>Ricardo Hooijmaijers</displayName>
		<givenName>Ricardo</givenName>
		<whenCreated>09/20/2009 16:35:52</whenCreated>
		<objectGUID>AB161693EC231D41A0C9F72F1279411C</objectGUID>
		<primaryGroupID>513</primaryGroupID>
		<sAMAccountName>JDoe_Test</sAMAccountName>
		<whenChanged>09/25/2009 10:04:53</whenChanged>
		<userPrincipalName>JDoe@CompanyTest.com</userPrincipalName>
		<mail>doesnotexist@companyTest.com</mail>
		<sn>Doe</sn>
	</Employee>
    </Employees>

    In order to be able to use all user accounts for testing purposes I use the same password for all users on the test environment.
    We can now run the PowerShell script that creates the users in the test environment. The script adds all users to a group called AllUsers@Company – Test right away. The new-QADUser cmdlet returns a user and that user is fed into the add-QADGroupMember cmdlet by using the | (pipe) symbol.  
    add-pssnapin quest.activeroles.admanagement

cd "C:\ADUsers"
[xml]$userfile = Get-Content ADUsers.xml

foreach ($user in $userfile.Employees.Employee)
{
   new-qaduser -ParentContainer "OU=Company - Test,OU=ContainerName - Test,DC=DomainName,DC=com" -Name $user.name 
    -UserPassword "Password1" -Email $user.mail -FirstName $user.givenName -LastName $user.sn 
    -samAccountName $user.sAMAccountName -UserPrincipalName $user.userPrincipalName -DisplayName $user.DisplayName 
    | add-QADGroupMember -identity CN=AllUsers@Company - Test,CN=_Private,OU=Company - Test,OU=Container - Test,DC=DomainName,DC=com  
   Write-Host $user.AccountName
}
  2. The next step is to backup the data from the production environment.  Before we can simply backup the  content database we have to “unhook” it from the production environment. We do this by running the STSADM preparetomove command.

    STSADM –o preparetomove –contentdb SQLServerInstance:ContentDBName

    Now just make a SQL Server backup.
    Next we have to undo the preparetomove command, or else the user profile info won’t sync to the user info lists in the site collections in that particular content database anymore.

    STSADM –o preparetomove –contentdb SQLServerInstance:ContentDBName –undo

  3. We can now copy the database backup file from the production environment to the test environment and restore it on our SQL Server test environment. Next we go into the SharePoint test environment. You might want to delete the current content database in the test environment, before adding the newly restored one.

    STSADM –o deletecontentdb –url http://testportalurl –databasename ContentDBName

    Running the STSADM deletecontentdb command won’t delete your database from SQL, it will only delete it from SharePoint. This means that the data is still on the SQL Server, the sites just aren’t accessible anymore through SharePoint.

    Now we are ready to add the restored content database to our SharePoint test environment.

    STSADM –o addcontentdb –url http://testportalurl –databasename ContentDBName

    Go into Central Administration, browse to Application Management and click on Policy for Web Application. Give yourself Full Control on the web application where you just added the content database, so you can test whether it’s working.

  4. Now all we have to do is tell SharePoint how to map the production users accounts to the test user accounts, to make sure that the permissions for all accounts will work like they do on the production environment. We use the STSADM migrateuser command for this. Be aware that you need to use the sAMAccountName for this, using the userPrincipalName won’t work. For generating the migrateuser statements I have written a small XSLT script that uses the ADUsers.xml file. 

    <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
	<xsl:apply-templates />

</xsl:template>

<xsl:template match="Employee">
<xsl:apply-templates select="sAMAccountName" />
</xsl:template>
<xsl:template match="sAMAccountName">
	<xsl:text disable-output-escaping="yes">stsadm -o migrateuser -oldlogin domainname\</xsl:text>
	<xsl:value-of select="."/>
	<xsl:text disable-output-escaping="yes"> -newlogin domainname\</xsl:text>
	<xsl:value-of select="."/>
	<br />
</xsl:template>

</xsl:stylesheet>

    This will generate a statement that looks like this:

    stsadm -o migrateuser -oldlogin domainname\JSmith_Test -newlogin domainname\JSmith_Test –ignoresidhistory
    stsadm -o migrateuser -oldlogin domainname\JDoe_Test -newlogin domainname\JDoe_Test –ignoresidhistory

    This is almost what we want. The only thing left to do is a text replacement to make sure that the old login is actually the production account. So we replace “_Test –newlogin” with “ –newlogin”.

Wrap up

The above 4 steps will help you to restore you production data on your test environment. While the process described in this post might look difficult and elaborate it is actually fairly straight forward and fast. Most of the fiddling is with transforming XML and the longest running operations are (by far) the export and import of AD users.

From → dotnetmag