Refresh Development from a Production SharePoint 2010 Farm Using Content Database Restore

In the real world it's nice to have a refresh cycle for your development farm. Usually it needs to happen after a large development project or some business process improvement.

The problem is how to update your development site so that it mirrors your current production farm. One answer is to use the publishing infrastructure, create a job and hopefully it runs to completion. In my experience this is messy and takes forever, usually failing at the end with a cliche error.

Another approach is to completely replace the contents of your existing development content database with a copy from production. Here's how I did it:

• Copy the content database backup from production SQL server to the development SQL server.

• If you don't have one…make one using SQL Server Management Studio. Right click your content database –> Tasks –> Backup  (more reading)

• In SQL Server Management Studio right click content database you want retore to and select Restore –> Database

• Choose From Device and point to the backup file that was copied
• Check the Restore box under Select the backup sets to restore
• Under Options section check Overwrite the existing database (WITH REPLACE)
• Double check the file and log locations under Restore the database files as
• Click OK to begin the restore. (more reading)

I experienced the following error when restoring a SharePoint content database:

Restore failed for Server 'sqlserver'. (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)

It's possible to see which user process is locking the database by executing the following in a query editor window:

execute SP_WHO

In the results look for the content database name that you are wishing to restore to and note the session id in the 'spid' column.

Use the following command to kill the session

KILL 54;

(again more reading)

Try the restore again.

The simplest method if you're comfortable with SQL

Sets the database to single user mode to force persistent connections to close. Restore from file on the development SQL server local drive. Change database back to multi-user mode.

ALTER DATABASE SPDev_CONTENT_DB1
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE SPDev_CONTENT_DB1 
FROM DISK='d:\SPProd_CONTENT_DB1_backup.bak'
GO
ALTER DATABASE SPDev_CONTENT_DB1
SET MULTI_USER
GO

Once you have restored the content database to development you will need to run a couple of PowerShell commands. You need to detach the old database from the site collection and re-add the new one. This makes sense because all of the references in the restored content database are configured for production.

Dismount-SPContentDatabase "DBNameHere"

Mount-SPContentDatabase "DBNameHere" -DatabaseServer "DBServerHere" -WebApplication http://SiteName

(and yet more reading)

Or with STSADM

stsadm –o deletecontentdb –url http://websitename/sitecollection –databasename

Don't worry it doesn't delete you database…just the references in SharePoint.

stsadm –o addcontentdb –url http://websitename/sitecollection –databasename –databaseserver

Browse to the site collection an smile at your refreshed development farm 🙂