Tuesday, June 19, 2007

Restore Production SharePoint 2003 Databases to Test Servers

This entry is designed to walk through the steps needed to backup and restore production SharePoint 2003 databases to test servers.  These steps have worked well for me especially when preparing for a SharePoint 2003 to MOSS 2007 Upgrade.  (This example is based on Microsoft SQL Server 2000.)



  1. In production, backup the primary Inetpub directory used by IIS.
  2. Backup C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60
  3. Copy SQL database backups (typically *.bak files) and the Inetpub and 60 folders to a temporary area on the target test server for use later.
  4. Log out of Production.
  5. Launch `Enterprise Manager' on the target test SQL server.
  6. Expand Microsoft SQL Servers SQL Server Group (local) (Windows NT).  Right click on "Databases" and select "All Tasks" "Restore Database."
  7. Choose "From device".
  8. Click on "Select Devices."
  9. Click on "Add."
  10. Click on the button with the three dots to browse.
  11. Browse to the temporary location where the database backup, Inetpub directory, and 60 directories were copied to from Production.
  12. Select the Profiles database.
  13. Select the name of the database embedded in the backup filename and copy it to the clipboard.
  14. Click `OK' three times.
  15. Paste the name of the database into the "Restore as database:" field.
  16. Click on the options tab.
  17. Set the physical filename and path to the new target destination (i.e. d:MSSQLdata"Servername".mdf and d:MSSQLdata"Servername".ldf.)
  18. Click `OK' to begin restore.
  19. When complete, the following message will appear:
  20. Repeat Steps 6 - 19 for the Services and Sites databases.
  21. Log back into the web front-end.
  22. Shutdown all SharePoint and IIS Services.
  23. Rename or move the existing Inetpub directory on test.
  24. Copy the backed up InetPub directory from Production to the location on test that was moved or renamed.
  25. Rename or move the existing C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60 directory on test.
  26. Copy the backed up 60 directory from Production into test.
  27. Restart the services stopped in Step 22 or just reboot the server.
  28. Open SharePoint Central Administration.
  29. Click on `Create portal site' under `Portal Site and Virtual Server Configuration'.
  30. Choose "Restore a portal".
  31. Set the names of the databases and select "Default Web Site".  Click `OK' to continue.
  32. Click `OK' to begin creation.
  33. A successful creation will cause a message resembling the following to appear:
  34. Click on Go to SharePoint Portal Server central administration.
  35. Click on Configure virtual server settings from the Virtual Server List page.
  36. Click on Default Web Site.
  37. Click on Manage content databases.
  38. Click on Add a content database.
  39. Choose "Specify database server settings" and enter the additional content database name (i.e. DB2_SITE).
  40. Set the number of site before a warning to a high number initially (i.e. 90000).  Set the max number of sites to a high number as well (i.e. 100000).
  41. Click Ok to restore the additional database and attach it to SharePoint.
  42. Repeat for all additional content databases.
  43. Reset IIS.
  44. Reinstall any custom web parts that may not be functioning.

1 comment:

  1. Thanks for this! We are just now (Jan 2010) upgrading from SharePoint 2003 to 2007. Your steps above worked for us. However, we are using a different ISS app pool account than production so we had to make sure the test account had proper database access and was 'dbo' in the databases. After doing that, and making sure all patches for both WSS and SharePoint were installed, we were able to restore a copy of our portal on the test server. Thanks again.

    ReplyDelete