SharePoint backup and restore database trick

Recently I had to make a SharePoint test farm at a customer site completely identical to the production farm. Therefore I backed up all production databases (including the Config and Admin databases) and restored them to the SQL server in the test environment.

Before I use the Configuration Wizard to disconnect the test servers from the farm and connect them to the new farm (the former production databases) I had to make a change in the restored Config database. This change is made to direct the Config database to the new (test) SQL server and therefore the new test farm.

This is the change:

1. Open SQL Studio Manager
2. Go to the instance > Databases > Config database
3. In the Config database, open Tables > dbo.Objects > Triggers
4. Here you find to triggers. Disable the trigger called “trigger_OnTouchObjects” (right click and choose disable)
5. Right click dbo.Objects and choose Open Table
6. In the middle pane the table opens and in the first row, fourth column (called “name”) you see the name of the SQL server in your production SharePoint farm. Replace this value with the name of the SQL server in your test SharePoint farm.
7. Go back to Triggers and enable the disabled trigger.
8. Close SQL Studio Manager and continue with the Configuration Wizard

Tags: backup & restore, guide