At some point you will need to restore/move/backfill content
from a Production Farm to a test or developer Farm.
You can do this by copying the SQL DB to your test SQL
server and reattaching the DB in CA to the Target web application.
A few things to remember :
You will have to have the same solutions deployed on your
target Farm.
Check the dependencies of any service applications i.e. Managed Metadata Service & others...
Make sure you have the same SQL server versions on target
and source.
These instructions are for SQL Server 2008 R2 and SP 2010.
It is also possible to do this via PS, check the article at
the end for further details.
On SQL Server
Get the current SQL permissions set on your target SQL DB.
Check the permissions set on the DB.
1. SQL DB Properties > Files > Owner > record the account listed
2. SQL DB Properties > Files > Permissions > record the accounts listed
3. Security > Logins > accounts from step 2 > Properties > User Mapping > select the target DB name > select source DB > record Permissions listed
Create a SQL DB backup of your Source web application DB.
Right click your target DB > Tasks > Backup > Backup
type > select Full
Under Destination > Click remove > Click add > File
name > browse … > Select your backup dir > enter a relevant filename
> click ok > ok
Click OK to kick off your backup.
Copy the source SQL DB backup to your Target SQL Server.
To have a recovery option in case this doesn’t work. Backup your target SP web
application DB either in SQL or via a SP Farm backup, both if you want to cover
all bases.
On your SP Farm
At this point I normally stop the IIS sites on all SP WFE’s
that use this target SQL DB to try stop connections to the DB’s. timer jobs may
still kick off though.
On your target SP Farm, navigate to > Central Admin > application
management > Databases > Manage content databases > Select you web application
> select the target DB Name.
Select “Remove content DB” > OK
On SQL Server
On the target SQL server, restore the source DB over the
Target DB
Check there are no connections to the source DB.
In SQL mgmt Studio, right click the server > Activity
monitor > expand processes > filter by target db > right click and Kill
Process on any connections.
Close the activity Monitor pane.
Right click your target DB > Tasks > Restore > Database.
Under To Database > ensure the target DB is selected.
Unser Source for restore select From Device > browse …
> backup media File > Click Add > browse to backup location and source
DB Backup file > click ok > Ok
Select the backup sets to restore > check the box
Select Options on the left > restore options >
Overwrite the existing DB (with replace)
Click OK to kick off the restore.
Once completed, refresh the DB view, right click the
restored DB > Files > Change the Owner to the correct account listed in
step 1. Above (normally your SP farm account)
Click ok
Expand the target DB > select security > Users >
remove any accounts from your source SP farm.
From the top level of SQL mgmt Studio, Select the server
> Security > Logins > Select the equivalent account listed above in
step 2. for your target SP Farm.
Right click > properties > user mapping > Users
mapped to this login > select the target DB name > enable the permissions
listed from step 3. above > click OK.
Your target DB should now have the same permissions set as
before the restore.
On your SP Farm
On your target SP Farm, navigate to Central Admin > application
management > Databases > Manage content databases > Select you web application
> click “Add a Content DB”
Ensure the correct Web application and Database Server is
listed.
Under “Database Name” Enter the target DB name.
Enter any configuration specifics, if any for the rest of
the DB settings.
Click OK.
Your restored DB should now be attached successfully.
Start all IIS sites that use the Target DB’s is you stopped
them.
Reset IIS on your WFE’s if you can.
If you have a warm up script for your target web application,
run this then access the site.
The site should eventually come up, if you got all your SQL
permissions correct.
It can be really slow sometimes so be patient.
Natalia Tsymbalenko has some common errors in this useful
article.
How restore a SharePoint 2010 content database on the
different farm :
