Moving Content DB’s

Have you ever wanted to move a SharePoint content database? WHAT?!? Why would anyone want to do that? Consider the following scenarios:

• You want to completely migrate your SharePoint infrastructure to new hardware (or location)
• Your SharePoint installation is “shaky” – and you want to make a fresh start on the installation but with your existing data

Each of these are real-life scenarios that I’ve run into over the years. When I first saw this done – I was terrified of losing the data (thanks for that, Matt McDermott!) – but as it turns out – it isn’t that big of a deal. Here is how you do it:

1. If SharePoint is being moved to a different server infrastructure – install that environment now and have it ready to go in order to minimize user down-time. (Create new Site Collections that mirror those that are being brought to the new farm from the old farm.)
2. MAKE A BACKUP (’nuff said)
3. Open SQL Management Studio and connect to the SOURCE SQL server.
4. Detach the content DB’s. It isn’t necessary to bring the admin, config, or search databases as they will be recreated in the new installation.
5. Using SQL Management Studio, connect to the Target SQL Server
6. Detach the content DB’s from the existing site collections on the new servers.
7. Copy the .LDF and .MDF files to the new server’s SQL data directory.
8. Using SQL Management Studio – attach the SharePoint content databases
9. Execute the following stsadm commands to activate the databases in SharePoint:
   1. Stsadm –o deletecontentdb –databasename <database name> –url <enter the url>
   2. Stsadm –o addcontentdb –databasename <database name> –url <enter url>

With that, you will be up and running. You will more than likely need to do some cleanup on search especially if the URL has changed.

UPDATE 11/24/2008:

Fellow SharePointer J.D. Wade posted a commend that warranted being part of the actual post:

Don’t forget that you want to run an stsadm -o preparetomove command before moving those databases to another SSP.
Check out these blogs posts for more information:
http://blogs.technet.com/corybu/archive/2007/06/01/detaching-databases-in-moss-2007-environments.aspx

http://wadingthrough.wordpress.com/2008/04/16/backup-content-databases-the-right-way/