May 19, 2009 at 9:12 am
Hi, I am in the process of looking at a migration plan from an old 32bit win 2003 server with SQL 2000 to our new 64 bit win 2008 Server with SQL 2005.
I am wondering which is a the better approach for migrating the databases across, If I restore the db from 2000 to 2005 it goes goes through an upgrade process at the end of the restore, will I lose anything by simply detaching / moving / attaching the seperate dbs?
Thanks.
May 19, 2009 at 9:44 am
Hi,
The detach/attach method will still go through an upgrade process when the files are attached to the SQL Server 2005 instance. Have a look at section 3 of the SQL Server 2005 Upgrade Technical Reference Guide, which discusses both methods in detail:
BOL also has details on upgrading databases using detach/attach:
http://msdn.microsoft.com/en-US/library/ms189625(SQL.90).aspx
Cheers,
Rob
May 19, 2009 at 10:34 am
Thanks for the links Rob, much appreciated.
May 19, 2009 at 4:59 pm
If you detach\move\attach you lose the ability to easily back out so detach\copy\attach is safer.
I usually go the backup\restore method but thats really just a personal preference and it seems a simpler process to me, end result is the same. Its a question of weighing up which takes longer, the backup\restore or copying data files which are potentially much larger than the backup file
---------------------------------------------------------------------
May 19, 2009 at 5:26 pm
george sibbald (5/19/2009)
If you detach\move\attach you lose the ability to easily back out so detach\copy\attach is safer.I usually go the backup\restore method but thats really just a personal preference and it seems a simpler process to me, end result is the same. Its a question of weighing up which takes longer, the backup\restore or copying data files which are potentially much larger than the backup file
I have found that either way is usually a wash. You can improve the backup\restore option by using Litespeed\Hyperbac\etc... to compress the backup file. But, you still have to suffer the time to copy the backup file to the new server and restore the database.
You would think it would take less time to just copy the files and attach, but that is really going to depend on how your network is setup. Here, it is much faster to pull the backup files down from network storage than to copy the mdf/ldf files from server to server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply