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

Comments -
  1. Gravatar

    Thank for this post. Did you do thi for 2007 or 2010? I see, that you are using STSADM...

      
  2. Gravatar

    Hi Anders,

    This was for a 2007 environment.

      
  3. Gravatar

    Do the permissions (not the user accounts) for each of the sites get stored in the content database or are they in the config database?

      
  4. Gravatar

    Hi bbwi,

    Site permissions are stored in the content database that site is stored in.

    Mirjam

      
  5. Gravatar

    Hi Mirjam,

    Can u tell me the differences in between content DB and Config DB.


    Thanks&Regards,
    Rajender G

      
  6. Gravatar

    Hi Rajender,

    This blog post by Bill Baer talks about the different SharePoint databases. blogs.technet.com/.../...-2010-database-layer.aspx

    Mirjam

      
  7. Gravatar

    Hi ,
    If I have my dev and test server under same network and same domain, I do not need to worry about user permissioning.
    But, My question is what about customizations, web.config changes and custom code ?
    If I do not have WSPs (client doesnt have) for previous customizations, what is the possible way out?

    Thanks
    Sangeet Ahuja

      
  8. Gravatar

    Hi Sangeet,

    That's a nightmare scenario. The best thing you can do is try to find out what customizations are build for the environment and rebuild them using .WSP's. That way you can deploy them for future deployments.

    Good luck.

    Mirjam

      
  9. Gravatar

    "The XML will get a bit easier to process if you transform it slightly by using a short XSLT script:"

    How?

      
  10. Gravatar

    The only thing still makes me hesitant is the step to unhook production environment with the preparetomove statement. Anytime you muck with the production environment, is seems risky something will go wrong. Is there a way to correct inconsistencies of the backup on after restoring the backup (without preparetomove) to test

      
  11. Gravatar

    Hi Mirjam, Very kind of you for sharing this information.

    Now I have a question, what if my test server is in different AD?

    eg. my production domain is ABC
    and my test domain is ABCTEST
    they are in different server boxes.

    Thanks in advance.

      
  12. Gravatar

    Mirjam,

    I have a production environment that I need to copy into a test environment. Since I don't necessary need the users from the production environment becuase the test environment will be used primarily to test updates and customization, can I skip the user creation of the AD users in the test environment? Also are these steps the same in SharePoint 2010 as 2007 because STSADM is depreciated in 2010 which uses Powershell. Do you have these steps for 2010?


    Mack

      
  13. Gravatar

    Good stuff Mirjam!
    I'm starting down this path with 2010 where I need to replicate the production environment to dev and test. I was wondering as well if you have the steps for 2010. Thx.

      
  14. Gravatar

    Hi Tony,

    This post is about SharePoint 2010 :-)

    Regards,
    Mirjam

      
  15. Gravatar

    Just a question!!

    I am new to Share point 2010.

    If we have the production environment with a server OS and want to copy/restore Production data to test environment where Windows 7 OS is present and we have all the other software's installed as per requirement..... Will it allow to restore the data?

    Regards,
    Naveen

      
  16. Gravatar

    Hi Mirjam,

    I am wondering if you already have faced the following challenge in you journey as SharePoint specialist. In the Netherlands we are implementing the law of using NON production data in any Test Environment (read DTA part and not production ofcourse). The challenge I now face is basically the following. We can make sure to change the meta data on the document properties for fields that require to be anonymous (in what form or methode) only their is this basis called documents. Ofcourse they have also data that might contain this kind of data.

    Question: Can we replace the existing (milion and more) documents with documents without data. The challenge is that we need have the same document name and their is something with a document ID in the history part. All I can find is now a lott of manual labour that we are not looking foward for (creating the same files without data) and placing it (you have to make sure no properties are in it to avoid issues with overwriting).

    Looking forward to your answer.
    Best regards,
    Wouter

      
  17. Gravatar

    [...] acceptance and production environment. This is a bit more difficult to achieve. [...]

      

Leave a Reply

 


Please add 6 and 6 and type the answer here: