Issues after migrating SQL 2000 database SQL 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Issues after migrating SQL 2000 database SQL 2005

Author

Topic

anbusql
Starting Member

4 Posts

Posted - 2008-02-06 : 04:19:18

Hi,

Currently we have SQL server 2000 database for past 3 to 4 years. Now planning to move SQL Server 2005. The SQL 2000 database will be restored to 2005 server.
My questions are,

1. The SQL 2000 queries, Stored proc and views will work with SQL server 2005 without any changes?
2. Do the SQL server 2005 have backward compatability with SQL Server 2000 or we need to convert any specific SQL script after converting to 2005.
3. Is there any issues after conversion?

Please help me in this area.

Thanks in Advance!

Thanks,
Anbu

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2008-02-06 : 04:48:54

Some commands are different in SQL SERVER 2005 and 2K5 doesnt support backward compatability.You Cant restore a 2005 backup in 2000.You have to rebuild the indexes. change the compatability level from 80 to 90

RKNAIR

anbusql
Starting Member

4 Posts

Posted - 2008-02-06 : 05:27:59

Hi,

Thnaks for your reply. But I want to confirm you that i am going restore SQL Server 2000 database into SQl 2005 server. you have mentioned in other way.

Sireesh
Starting Member

4 Posts

Posted - 2008-02-06 : 10:08:42

Hi,

I suggest to apply upgrade advisor on all databases to migrate and following are some of teh considerations you should be careful.

Sysxlogins
Syslocks
Sysproperties
Sysfulltextnotify

Collation Settings:
In prior versions of SQL Server collation of master database is applicable to all the databases,but in 2005 it doesn't work in that way.

Compatibility Levels:
80 to 90.
compatablity 90 doesn't support the following:
With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword.
*= and=* are not supported outer join should be used.
SET XACT_ABORT OFF is not allowed inside a trigger.

Views with CHECK OPTION are not supported if the view or a view it references contains TOP (Specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows).

Security Related Changes:
Grant ALL option doesn’t work.
Passwords are case sensitive.
To use sp_addtype (add a user defined datatype), add the users to the db_ddladmin or db_owner database role.

Good luck for the migration.

Regards,
Sireesh

anbusql
Starting Member

4 Posts

Posted - 2008-02-07 : 00:48:26

Thank you all for helpful points... After migration I will update my learnings...

dewacorp.alliances

452 Posts

Posted - 2008-02-07 : 01:15:21

quote:
1. The SQL 2000 queries, Stored proc and views will work with SQL server 2005 without any changes?

You need to use the Upgrade Advisor and that one will give you advice if there is an issue with it.

quote:
Do the SQL server 2005 have backward compatability with SQL Server 2000 or we need to convert any specific SQL script after converting to 2005.

You can still have a compatibility level of 80 which SQL2000. But obviously you miss the functionality of SQL2005 such reporting stuff etc etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources