There are a zillion and one posts and how-tos out there amongst the Interwebs detailing the supposed “correct” way to use STSADMIN to copy or promote SharePoint Content Databases between different environments (i.e. Dev<->Stage<->Prod).  The problem with the typical STSADMIN approach is that it forces you to create a new Web Application within the target environment.  Well what if you’re dealing with a pre-existing target environment with complex IIS Bindings and or Alternate Access Mappings, etc (i.e. things that make you not want to hassle with having to re-set them all up.)  So in these circumstances, the easiest thing to do is just to grab the content database from the source environment and copy/bind it to the target environment.  The instructions to follow outline the steps needed in order to successfully perform such a promotion:

Pre-Move

SharePoint won’t automatically update all the links in your site to match the target environment’s host address, especially if you’ve added your own links to places within the site structure.  So, to make sure links work once the site has been moved, you need to go through any links you’ve created and make sure they are relative URLs and not fully-qualified.  It’s also a good idea to navigate to the internal site list for the Site Directory entries (http://<your host>/SiteDirectory/SitesList/AllItems.aspx) and modify them to be relative.

Performing the Copy/Promotion

First - Copying Content Databases Between Environments

Same Database Name:
If the target database has the same name as the source database

  1. Backup the source database (full backup).
  2. Copy the backup to the target database server.
  3. Stop the target IIS Website on all target front-end-web servers.
  4. Perform a full restore to the target database using the backup.
  5. Start the target IIS Website on all target front-end-web servers.
Different Database Names:

If the target database has a different name than the source database

  1. Stop IIS on each of the target front-end-web servers.
  2. On the target database server:
    a.  Note the name of the applicable content database.
    b.  Document the security applied to the applicable content database
         (Specifically, what logins have what access to the database).
    c.  Document any backup/maintenance plans for the applicable content database.
    d.  Detach the applicable content database.
    e.  Backup the corresponding database data (.MDF) and log (.LDF) files to another location.
  3. Stop IIS on Website on all source front-end-web servers.
  4. Stop the source content database on the source database server.
    a.  Copy the corresponding .MDF and .LDF files to the target database server
          (usually C:\SQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\) .
         i.   Rename the copied database files to match the names of the files in 2.e.
    b.  Re-Start the applicable source database on the source server.
  5. Re-Start IIS on the source server.
  6. On the target database server:
    a.  Open the Attach Database dialog:
         i.   Select the renamed MDF file from 4.a.i.
         ii.  In the upper “Databases to attach:” area, change the “Attach As” value to match 2.a.
         iii. In the lower “’<database name>’ database details” area, change the “Current File Path” values for both rows to match the paths and filenames in 4.a.i
    b.  Modify the security of the newly attached database to match 2.b.
    c.  Re-Apply any backup/maintenance plans from 2.c.
  7. Do not yet start IIS on each of the target web front end servers as we’ll do that later.

Second – Reconfiguring the Content for its New Home

At this point, were you to try to load the restored site, you’d most likely receive a “400 BAD REQUEST” error when browsing “/” and a “File Not Found.” error when browsing the default “/Pages/Default.aspx” path.  This is because SharePoint has gotten a little confused about what has just been done to it.  To resolve the confusion, we need to essentially force SharePoint to reconfigure the newly promoted content database.  The easiest non-STSADMIN way to do this is to use Central Administration web UI to remove the content database and re-add it:

  1. Navigate to the Central Administration Website for the target SharePoint instance.
  2. Within the Application Management tab, find the SharePoint Web Application Management section and open the Content Databases link.
  3. Change the Web Application to match the applicable application we’ve just updated.
  4. Click the Content Database name item to open its property page:
    a.  Document the following settings for use later:
         --   Database server
         --   SQL Server database name
         --   Number of sites before a warning event is generated
         --   Maximum number of sites that can be created in this database
         --   Windows SharePoint Services search server
    b.  Place a check mark in the Remove content database box
         (and click OK in the resulting prompt)
    c.  Click the OK button to perform the removal of the content database.
         (The database itself will not be deleted, it is just no longer associated with this site)
  5. You should now be back at the Content Databases Application Management screen.
  6. Verify the Web Application is still the applicable application .
    (change it if necessary)
  7. Click the Add a content database menu item to open the Ad Content Database page:
    a.  Verify the selected Web Application is correct.
    b.  Using the information documented in 4.a. fill in the properties for each to match exactly.
    c.  Click the OK button to add the existing content database.
         (SharePoint will now update the promoted Content Database to work with its new home)
  8. Start IIS on each of the target web front end servers.
  9. Test the target site.
    (Sometimes the first load will result in an error. It should work if you refresh the page)