- External SQL Server Database Move

  •  Log Shipping Method(low impact)

To migrate your content database from 1 SQL Server to another there are a few choices, your choice will depend on how much downtime you are willing to take.  First option being you can setup SQL Log shipping, Once your 2 databases are in sync you would do the following steps.

 Stsadm –o preparetomove –contentdb SQLServerA:demo_content_1

 Stsadm –o deletecontentdb –url http://demo –databasename demo_content_1 –databaseserver SQLServerA

 At this point you would perform the final cutover on your log shipped database to SQLServerB

 Then finish with

 Stsadm –o addcontentdb –url http://demo –databasename demo_content_1 –databaseserver SQLServerB

 

  • Copy Method(high impact)

Your other option would be to simply detach the database from SQL and copy the .mdf and .ldf to the new SQL server.  While in transit your users will get a generic “Cannot connect to content database” error when visiting their site.  Following the copy you would perform the following on your web front end.

 Stsadm –o preparetomove –contentdb SQLServerA:demo_content_1

 Stsadm –o deletecontentdb –url http://demo –databasename demo_content_1 –databaseserver SQLServerA

 Stsadm –o addcontentdb –url http://demo –databasename demo_content_1 –databaseserver SQLServerB