July 15, 2008 at 2:12 am
Hi Experts,
Our client want to migrate SQL Server 2000 to 2005,so what are all the queries i have to ask them before doing the job.
TIA
July 15, 2008 at 4:01 am
Is it possible to migrate SQL Server 2000 SP2 to SQL Server 2005.
TIA
July 15, 2008 at 5:07 am
Keep in mind that several things have changed in 2005. Check the whitepapers for the differences between 2000 and 2005. For instance many of the system tables (like sysobjects / sys.objects) are renamed. You have to check all your SP's, etc. and make changes where nessacary.
On the website of Microsoft are also several white papers available about the upgrade from 2000 to 2005.
July 15, 2008 at 5:11 am
Thanks for your reply HANSHi
I want to migrate only the data .A new SQL 2005 DB have to be created having more constraits.
July 15, 2008 at 5:41 am
Ratheesh.K.Nair (7/15/2008)
I want to migrate only the data .A new SQL 2005 DB have to be created having more constraits.
You can migrate the data without problems. When you encounter some issues it is possible to run the database in a lower compatibility level (80 == SQL 2000). This might solve some issues.
July 15, 2008 at 7:37 am
What kind of Data, How many rows do you have.. and how many tables.
Some times it might be easier to Just Migrate the Database rather than the Data itself, and then you can reapply the constraints in place once you move(restore) the Database to 2005.
Even if you do migrate, always check back with DBCc CHECKDB and also your index fragmentation.(Have a Maintenance Plan take care of your index maintenance). Double Check your indexing Strategy(fill factors and number of required indexes).
Maninder
www.dbanation.com
July 16, 2008 at 12:04 am
wat abut dts do we get any issue in dts .... after migration from 2000 to 2005 and wat if we detach the database..deinstall sqlserver 2000 and install 2005 and then attach the database.......will there will be any impact ... as mentioned in the above scenario by default the compatibilty level is 80 so do 2005 hav any changes in compatibilty level
July 16, 2008 at 12:56 am
You can migrate the data, but after migrating data just verify the database consistancy using DBCC CHECKDB command for new database in SQL Server 2005, check for the index fragmentation. if require rebuild the indexes & statistics on all tables.
Regards,
Abhijit
July 16, 2008 at 2:29 am
What are all the ways to migrate a database from 2000 to 2005??
July 16, 2008 at 2:38 am
Hi,
If you are to migrate databases then :
1)
Backup the database in the current server
Move the backup file to the new location
Restore the backup file to the new server
After the restore of the database is done, make sure to map the logins to the location
2)
Detach the database and then attach the mdf and ldf file to the new database, once the files are moved.
3)
You can script out the jobs from the old server and excute them in the new.
4)
Change the compatibility level of sql 2005 to 90. as compatibilty level in 2000 is 80.
You can ask for
> a relable network connection so that you could move the backup files from one location to the other.
> You can be ready with the backup of the database.
> You can script out the jobs
> You can note down the logins from the old server and their permissions.
😛
July 16, 2008 at 2:43 am
why do we need to change compatibilty level in 2005 from 80 to 90
jus give me some reasons.....
July 16, 2008 at 2:51 am
I faced some issues while running some SPs on 2005, which were running perfectly fine in 2000. Then once we changed the compatibilty level it worked fine!
Some features of the SQL Server 2005 may not function properly if the compatibility level isnt changed..this is what I understand..
lets check for more replies ..;)
July 16, 2008 at 3:10 am
This is wat my friend got wen he upgrated from 2000 to 2005 and changed the Compatibility Level
After the install I tried to delete a row from my Item table which is a
foreign key to about 10 other tables. A simple
Delete Item Where ItemID = 888
In SQL Server 2000 it takes about 9secs. In SQL Server 2005 i have yet
to let it run successfully, i stop it after 5mins.
After the upgrade I changed the Compatibility Level to SQL Server 2005
(90) under Database Properties -> Options
After I changed the Compatibiliity Level back to SQL Server 2000 (80)
the query ran in approx 17 secs.
I am little confused bcoz i have to migrate from 2000 to 2005 and there are around 25 live servers.
July 16, 2008 at 3:27 am
oh ok..
Do you have any DTS packages that need to be migrated?
any relication setup done?
July 16, 2008 at 3:46 am
Is there any option in SQL Server IMPORT/EXPORT like error file in BCP
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply