June 13, 2012 at 2:34 am
Hi All,
Planning to migrate 2000/2005 databases to either 2008 or 2012. What are the best ways to do this with sql server in-built features and tools?
_____________________________________________
One ounce of practice is more important than tonnes of dreams
June 13, 2012 at 6:20 am
The 2000 databases will have to be updated to 2005 or 2008 before you can make the jump to 2012.
Other than that, you have two ways of doing the upgrade. First, in all cases, run the SQL Server Upgrade Advisor. This is a free download from Microsoft. They have one for each different release of the product. Resolve any issues that it identifies. Second, you have choices. Choice one, in place upgrade. Simply install the new version of the software on top of the existing software. This is considered the least safe version. Choice two, side-by-side upgrade. You can take a backup of your database in 2005/2008/2008r2 and restore it to a 2012 server. Alternatively, you can take your 2005/2008/2008r2 database offline, copy the mdf, ldf, ndf, files over to the 2012 server and attach them. Upgrade complete. Now, make sure the compatibility level is set to … 12.0 is I think the new number, might be 11. Also, update all the statistics manually. They'll get updated eventually on their own, but better to just get it done as part of the upgrade.
My personal preference is to use the backup/restore approach in a side-by-side upgrade. I find this preferred because if anything goes wrong (nothing ever has, but paranoia is a good thing) I can just continue using my older server, nothing has to be turned back on or reset up or uninstalled.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply