SharePoint Blogs / SharePoint University
SharePoint Blogs and SharePoint University - all in one place!
The SharePoint Redemption » Restore Production SharePoint 2003 Databases to Test Servers
Need SharePoint Training? Attend a SharePoint Bootcamp!

Please delete cookies related to sharepointblogs.com and sharepointu.com to resolve login issues!

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 Files\Common Files\Microsoft Shared\web server extensions\60
  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:\MSSQL\data\”Servername”.mdf and d:\MSSQL\data\”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 Files\Common Files\Microsoft Shared\web server extensions\60 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.
Posted 06-19-2007 2:13 PM by Jeff Holliday

Comments

vaperware wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 06-20-2007 6:24 AM

Great post, is there a simlar step by step for WSS v2

University Update-Microsoft SQL Server-Restore Production SharePoint 2003 Databases to Test Servers wrote University Update-Microsoft SQL Server-Restore Production SharePoint 2003 Databases to Test Servers
on 06-20-2007 7:52 AM

Pingback from  University Update-Microsoft SQL Server-Restore Production SharePoint 2003 Databases to Test Servers

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 06-27-2007 1:18 AM

vaperware:

The restore should close to the same.  I will try to perform a specific restore using just WSS 2.0 and will post back.

Thanks,

Jeff

Frank wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 06-28-2007 6:26 AM

Great post! Just what you need for testing migration from SPS 2003 to MOSS 2007.

Gade wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 06-29-2007 4:35 PM

I got this error message 'SvrAdminPortalCreate_SecurityConfigurationSection_Text' is not a valid value for attribute 'TitleLocID'. It must be of enum type 'Microsoft.SharePoint.Portal.WebControls.LocStringId'

Is anyone familiar with that?

Tobi Heims wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 07-02-2007 12:33 AM

Hello,

very good post!

Is it the same way on Sharepoint 2007? I need to configure a test system!

thnx!

Chris wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 07-06-2007 2:58 PM

Am I missing something, or is the attachment for the  "...PDF with screenshots" missing?

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 07-09-2007 3:06 PM

I apologize Chris, the screenshots were lost when the sharepointblogs.com went offline.

Jeff

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 07-09-2007 3:17 PM

Tobi,

The process should be very similar with interface differences of course.  Also, if you do not have too much data within your MOSS environment at this time you could use the tools built-in such as stsadm or backup/restore using central administration.

Jeff

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 07-09-2007 3:19 PM

Tobi,

Microsoft also provides a 30-day trial VHD already configured with MOSS.

www.microsoft.com/.../details.aspx

Jeff

Zip wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 08-08-2007 1:54 PM

Did you ever figure out Vaperwares question. Does the steps work for WSS2.0?

mike wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 08-30-2007 2:07 PM

your step sucks.

you missed parts about backing up IIS stuff like application pools

also creating virtual servers

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 08-30-2007 3:17 PM

Mike: I appreciate your "constructive feedback" but this was strictly to move the databases over to a test system under the assumption that the test system was already setup with no portal created.

Jeff

Mike wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 08-31-2007 11:03 AM

sorry jeff. it's just it is getting very frustrating.

i backed up IIS from production.  restored to test environment including the directories that goes with the web servers in iis. installed sharepoint 2003. restored databases to test sql machine. connected to a new config database. went to restore portal but my virtual servers are not listed-only default. i did what your instructions did which was restore to default but it didn't go in its own portal. now i can't restore all the other 5 portals.

then i found how to add content databases by changing the url in the address bar. when i add them i get error Cannot drop type 'dbotSystemID' because it is currently in use. (error code: 3732). then i try again. i get error A database with the given name already exists on the specified database server. i uninstall. reinstalled. same thing. also tried to bring the config database from production but same thing.

production has 2 front end servers. 2 search servers + 1 job server. total of 4 servers.  the test environment has one web server, and one sql server.  i don't think i can create a sharepoint 2003 test environment after trying for 2 days with no progress.

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 08-31-2007 11:37 AM

Mike: I can understand your frustration.  Did you restore the IIS config to test from production or just the folder structure?

I would remove everything you have in test and install SPS 2003 from scratch with the exact same service pack levels you have in production.  If a portal is created then delete the portal using Central Administration.  Then walk through my steps with restoring the DBs, copying the files over, etc.

This will restore the primary Profile, Serv, and Site DBs.  After you restore the portal you will need to add any additional content databases you have using central administration also.

Good luck.

Jeff

Mike wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 09-04-2007 10:31 AM

Okay, the SharePoint 03 portals are running on the test environment.  It was very tricky.  Now all my web parts need to be redeployed.  Hopefully after that I can continue with upgrading the test environment with a gradual upgrade for Sharepoint 2007 that is when the exciting part comes into play.

Sandy wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 09-06-2007 8:58 PM

I used this documentation as a basis to move our production SharePoint & SQL servers from one domain to another. Thank You!

Kian wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 09-26-2007 10:50 AM

Great post.  I'll be giving this a run soon as we're getting ready to upgrade as well.

Pharmann wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 09-28-2007 4:00 AM

hello tnx for info - its seldom found in such details.

BUT....

I have ( or so it seems ) lost connection with the database. I have copied all files, restored the databases etc.

but clicking on the Administrative Tools -> Central Admin pages leads me to a page telling me that connection to the configuration databbase isnt possible.

SQL 2K SP4 server i located on the same box (test) as the  sharepoint  (just as on the production system)

Trying to disconnect from existing config db tells me that (local) is an invalid SQL server name. wich makes no sense at all.

I can browse and everything.

AND few moments ago i could  browse the Central Admin site just fine.

I have made no changes in accounts and so but still

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 09-28-2007 2:17 PM

I would delete the portal again, but do not delete the databases.  Then restore the portal one more time.

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 10-11-2007 4:23 PM

Susan: it sounds like a rights issue.  I would compare the settings and rights in IIS as well as NTFS against your production server.

Jeff

Poch wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 10-23-2007 12:56 AM

Hi, I tried restoring the database, but my enterprise manager doesn't have (local) Windows NT server under the SQL Server Group. It just says No Item. How do I add that item to that server group? Thanks.

Poch wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 10-23-2007 3:33 AM

Hi,

I've figured out my database problem. Now I'm stuck with No. 29. Mine just says Virtual Server Configuration and I can't find a setting to create a new portal. What do I do? please help.

Poch wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 10-23-2007 10:44 PM

I didn't realize that the steps were for MOSS. I'm using WSS 2.0 :( Any workaround for WSS 2.0, since Portal creation can't be done in that platform? :(

paul wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 12-17-2007 12:58 AM

Foe people who need to backup and restore WSS 2.0 a great article is at www.windowsitlibrary.com/.../toc.html

plugugly wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 03-07-2008 12:19 AM

Dude,

Your steps are probably the most clear of all the stuff about SPS restore that I've read all over the net, kudos on a fantastic job and keep the good work going.

-plugugly

PS: if ever in Bangalore, I owe you a beer, WTF make it lots of beers

Sudha wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 03-13-2008 2:10 AM

Hello,

Dont know whether this is the palce where i can post such issues or comments

I'm new to sharepoint and i have to work on the project creating a portal site for a client.Got installed SPS 2003 and created few sample sites in central administration.which are in the port 80.It was working fine when i checked on my system and suddenly i'm unable to acces the site "Page cannot be found"...

Can i know what are the exact requirements if my site need to be tested on my testing team members.do i need to install SPS 2003 on their machine also?and what are the files needed to be copied and where is the database stored?

Steve wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 04-02-2008 11:56 AM

I have the same error that Gabe received:

'SvrAdminPortalCreate_SecurityConfigurationSection_Text' is not a valid value for attribute 'TitleLocID'. It must be of enum type 'Microsoft.SharePoint.Portal.WebControls.LocStringId'.

I don't see where anyone answered this.

Could you assist?

Scott Lock wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 04-18-2008 3:49 PM

Steve,

I had the same issue.  I would check the version numbers of WSS 2.0 and SPS 2003.  I had a mismatch in versions.  You can find a list of versions here:  mindsharpblogs.com/.../481.aspx

Make sure that all are exactly the same.

Hope that helps.

Reetu wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-28-2008 4:05 PM

I am getting 401 Unauthorized error when I am overwriting the Inetpub and 60 folder from Production to test server.

My test server is a fresh install with Sharepoint 2003.

Any ideas?

Reetu wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 06-02-2008 4:39 PM

I copied the Inetpub and 60 folder from production to test server. This is where I am facing issues. I copy Inetpub and 60 folder to test server, and then I click on Restore Portal in Sharepoint central administration. It gives me 401 Unauthorized error. Any idea why I am getting this.

Manju wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 08-17-2008 9:27 PM

Hi buddy,

Amazing article which rescued me from deep trouble..

Thanks a million.

Sudha wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 04-17-2009 3:09 AM

Great post.  

I am currently working on SharePoint 2003 to MOSS 2007 Upgrade.

Could you post a similar article on SharePoint 2003 to MOSS 2007 Upgrade which would be reallly helpfule

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 04-20-2009 1:56 PM

Sudha,

It depends on several key components.

For example:

Upgrade method including Infrastructure changes, same hardware or new hardware;

Custom components including site defintions/templates, pages unghosted with FrontPage, Web Parts, etc;

Microsoft has some great checklists available to assist you:

technet.microsoft.com/.../bb421259.aspx

Jeff

Anna wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-12-2009 12:50 PM

I followed all above steps but I cannot authenticate through the browser, I've check all IIS settings and DB. Any idea?

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-12-2009 1:20 PM

Anna,

I have seen this happen in the past with NTFS permissions being stripped when the 60 folder was copied over from Production.  Verify that the folder in Test is still inheriting from it's Parent.

Mike Morales wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-12-2009 7:05 PM

Jeff,

Got it work on the first try. Anything extra I need to do if all the sites containing https don't appear in the browser whrn I click then but others do?

Also the reason I needed to do this was a co-worker deleted a site on 4/10 and I restored the database from 4/7.

I still can't see the site listed though on the recovered system. Any thoughts?

Mike Morales wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-13-2009 9:34 AM

Jeff, how do I mark Webparts as safe? Once the restored site attempts to open them on the default home-page I get the cannot open because the webpart is not considered safe error.

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-13-2009 3:40 PM

Mike,

To answer your first question, verify your Alternate Access Mappings are correct.

Your second question, make sure you have reinstalled all web parts that were installed on your original system.  If you do not have all the installation files compare your web.config files for your web application.  The settings will be in there for marking a web part as safe.

Jeff

Mike Morales wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-14-2009 1:54 PM

Thanks Jeff,

I reinstalled RSS feeder and Lead-IT Essentials and they render but I had to upload them manually, the titles for Breadcrumbs, etc In that Virtual Server Gallary appear as Untitled, If you know a quick way to fix that great otherwise I'll live with these Apps being in the Default Gallery.

Anna wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-15-2009 2:15 PM

Hi, about NTFS seems ot be rigth. I have another portal too on the test server with its own db so it's working fine. Seems something with authenticating over the database copy of production. Do I have to install sharepoint test with the same user of my production for ex?

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-15-2009 3:29 PM

Anna,

Verify that your service accounts used in your new environment has the same access as the service accounts in your prod environment to all SQL databases.

Jeff

Jeff Holliday wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 05-15-2009 3:30 PM

Mike,

I apologize that I do not have an answer for you at this point.  If something comes to me I will post it here.

Jeff

Martin wrote re: Restore Production SharePoint 2003 Databases to Test Servers
on 06-23-2009 7:55 AM

Hello Jeff

When I reach your step 32, i get an error "The selected language is not supported. If using shared services, ensure that the language of the parent farm and the child farm are the same."

Do you have any clue how to fix this?