Mrdenny 
46765 pts. | May 2 2008 1:49AM GMT
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Q:
Looking for best approach to upgrade SQL Server 2000 databases to SQL Server 2005
I will be upgrading a number of SQL Server 2000 databases to SQL Server 2005 and was wondering what database objects I have to worry about? I believe to run Data Transformation Services (DTS)in 2005, I will have to have legacy installed. Is that correct? Other than that, should there be any problems with stored procedures, views, etc. The way I was thinking about performing the upgrade is to create a new database on the SQL Server 2005 server with compatibility for SQL Server 2000. Then I'd restore the SQL Server 2000 backup file. Is this a good approach?
ASKED:
Apr 30 2008 5:51 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
If needed you can set the compatibility to SQL 2000, but it's usually not needed.
There is no need to create a new database on the SQL 2005 server and then restore the SQL 2000 database over it. Simply restore the database backup, SQL will create the database for you.
You are correct, DTS will require the legacy tools in order to edit and run the packages.
As always I recommend doing the upgrade is development and QA first, to be sure that there are no problems.
