Migrating Data from 2000 to 2005
-
Hi There,
At the mo, we're running SQL 2000 and SQL 2005 in parallel with majority DB's/data etc being on SQL 2000. How would i go about migrating data/DB's from the SQL 2000 box to the SQL 2005 box ?
Thanks,
Sonia
-
to be honest detach and attach is about the easiest method, although restoring backups work just as well.
The database stays in 2000 ( 8 ) mode , you need to chnage the mode to upgrade the database to 2005 ( 9 )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
And here i thought it was a lil more involved than that ?
I tried to restore a DB on 2005 from a backup made from the same DB in 2000 and i got some funny errors. Any reason why i wouldn't have been able to restore the DB ?
Sonia
-
As is often the case i've not had any problems restoring databases to 2005, I've taken a collection of production and test databases to 2005 including a replicated database, all been switched to 2005 compat and apparently without any issues!! I also have a 2000 database replicating to 2005
My understanding is the database will run as if it were 2000 unless you make it otherwise. If your database/app makes extensive use of system tables or system procs then expect to have a few problems. You generally want to at least update stats after a move. I usually go the whole hog with integrity and optimisation to be sure.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks for the info
-
Actually - restoring or re-attaching a SQL 2000 database and then setting it to 2005 compatibility will *always* work as SQL Server doesn't check anything - you're just changing a system setting, not performing a validation that your schema will work under the new compatibility setting.
The only way to be sure that nothing is broken in the schema is to scripts out your 2000 database and rebuild it on a 2005 instance (that is already at 2005 compatibility level). You can do this manually or use a tool to do it for you. Take a look at http://www.dbghost.com/2000to2005migration
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com -
agreed. but you could also script the database from EM in 2000 and run the script against 2005 or dts the entire database , although not such a good idea for a several hundred gigabyte database. However I'd probably agree that dbghost would report any problems in a better manner than a straight script or dts.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I would suggest scripting the complete 2000 db and then running the script on 2005 to test for incompatibilities.
If the original developer used reserved words for tables or columns, 2000 will allow that but 2005 requires brackets [] around those - for example, some dum-dum named a table Transaction which works in 2000, but in 2005 you need to write [Transaction] or it will blow up.
Compatibility aside, detach & attach is probably the best approach IMHO.
-
I should have added that after you create the script from EM, you'll need to edit the resulting CREATE DATABASE statement to specify the file name, logical name, and the initial sizes of the data and log files. If you don't, then you'll wind up creating a db as big as the original, but without the data.
-
Hi Colin,
Have you tried using DTS to transfer an entire schema?
It's quite frustrating, basically DTS doesn't seem to bother with any kind of dependency order, especially when transferring data.Ho hum.
Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply