February 28, 2008 at 11:05 am
I have a SQL Server 2000 database that I need to move to SQL Server 2005 that sits on a server with different configurations. What would be the best way to do this?
February 28, 2008 at 11:18 am
Do a detach and attache method. This should be fine And after migration make your database to compatability level 90 to get the features of 2005. this method involves some downtime.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 11:56 am
Depending on whether or not you want to be able to roll back for any particular reason, I'd recommend doing a backup/restore. If you detach/reattach, there is no going back to 2000. Or just simply make a backup before you detach/reattach.
February 28, 2008 at 12:58 pm
backup and restore is the "safest" path.
* Noel
February 28, 2008 at 1:09 pm
The last time I tried a backup/restore a ran into some incompatibility issues between the two versions of SQL Server and my database. Is there a way to specify the database to remain in SQL Server 2000 database, then change the compatibility mode?
February 28, 2008 at 1:12 pm
Jim Mace (2/28/2008)
The last time I tried a backup/restore a ran into some incompatibility issues between the two versions of SQL Server and my database. Is there a way to specify the database to remain in SQL Server 2000 database, then change the compatibility mode?
Unless you change it to 90, it will stay in 80.
February 28, 2008 at 1:14 pm
Jim Mace (2/28/2008)
The last time I tried a backup/restore a ran into some incompatibility issues between the two versions of SQL Server and my database. Is there a way to specify the database to remain in SQL Server 2000 database, then change the compatibility mode?
By default when you restore 2000 in 2005 your db is incompatibility mode 80 ( 2000)
you can eventually change it to 90 when you know that all requirements of it won't break anything.
* Noel
February 28, 2008 at 4:47 pm
I prefer detach/attache as this will not leave me losing any transaction in the database after it has been backed up. so plan your startergy.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 8:38 pm
Detach/Attach v. Backup/Restore is really a matter of personal preference. So long as you copy/paste the data/log files in the Detach/Attach instead cut/paste you can recover by re-attaching the files on your 2000 SQL Server.
The benefit of backup/restore is that you do not need the extra step of copying the backup file to your 2005 server, you can point to the files via a UNC path and do the restore over the network.
I support 800+ databases and have never had an issue with restoring a db from 2000 to 2005 using either method.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 28, 2008 at 9:13 pm
I prefer detach/attach, but I agree with Tim, either will work.
March 3, 2008 at 10:16 am
Thank you all very much. I will take this into consideration. I am most familiar with the backup/restore operations, so this seems like a good solution. Thanks again.
March 3, 2008 at 11:14 am
One more thing you might want to consider. Before running your backup set the database into single user mode then run the backup. This will insure that there are no other connections to the database and no 'missing' transactions.
Jeff
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
March 3, 2008 at 11:52 am
- as long as you don't forget to perform full maintenance after your restore, you should be fine.
- the data purity check
- reindex all tables
- sp_updatestats
- dbcc updateusage (0) with count_rows
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply