January 22, 2009 at 2:56 am
Using detach and attach for user databases is a good idea. Even if you do an upgrade in place it is good to detach the user databases before you start, as you do not want these to be involved in any troubleshooting you may have to do for the SQL install.
You cannot use detach and attach for the system databases. There are very many changes to the contents of the system databases with SQL 2005 and SQL 2008, and any attempt to detach/attach system databases from a previous version is doomed to failure. Even if you do somehow manage to re-attach the SQL 2000 system databases to SQL 2005, there will be many SQL 2005 features that will not work as expected (the same applies going from SQL 2005 to SQL 2008), and you will be outside of Microsoft support.
You need to script out from the system databases all data you want to keep, and script it back in after you have completed the upgrade. This obviously adds a fair bit of work to the upgrade process, but this has to be balanced against the probability that about 4 in 10 in-place upgrades will fail. FineBuild on Codeplex has procedures that can script out all the SQL 2000 system objects and script them back into SQL 2005 or SQL 2008.
You need to take a view on the advantages of having a single upgrade process that you can practice and use for all servers with a predictable timscale, compared to starting upgrades with one process and having to fall back to a significantly different process when failures occurr.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing post 31 (of 30 total)
You must be logged in to reply to this topic. Login to reply