Upgrade from 2000 or before - SQL Server Forums

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 2008 Forums

SQL Server Administration (2008)

Upgrade from 2000 or before

Author

Topic

Beengie
Starting Member

3 Posts

Posted - 2013-10-01 : 19:08:50

I am currently trying to get a SQL server database up to the current version of SQL Server Management Studio that I am using (2008R2). I wish to create a database diagram for a current database. I am unable to create one.

I have researched and found that there are 2 issues associated with this problem. First is the authentication. I am logged in as "sa", so I am good there. The other is the compatibility which should 2008. However, that option is not on the list to choose from when I go into the database properties -> Options page. I am guessing that is the problem.

What is the fix for this? Was this installed correctly?
P.S. I didn't install it! lol

Beengie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-01 : 20:22:13

The compatibility for 2008 is 100. Do you have that in the list?

80=2000
90=2005
100=2008
110=2012

Why MS decided to use that scheme is beyond me?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Beengie
Starting Member

3 Posts

Posted - 2013-10-02 : 09:16:30

I only show 2000(80). I am trying to populate the list to show at least 2005(90). From what I have read, that is the beginning of the database diagram support.

Beengie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-02 : 15:44:41

Run SELECT @@VERSION and post the output. It sounds like your database is actually SQL Server 2000, but your management tools are 2008 R2.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Beengie
Starting Member

3 Posts

Posted - 2013-10-03 : 10:23:52

You are correct. It is showing "Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)"

I would like to upgrade it, but I'm worried about data integrity. How would I upgrade it, and what would be the process for making sure that I do not mess up any existing queries or data?

BTW, thanks for helping me get on track...

Beengie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-03 : 12:47:28

Upgrading is a large topic. Please google for the various documents/articles regarding this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2013-10-08 : 09:17:11

quote:
Originally posted by Beengie

I would like to upgrade it, but I'm worried about data integrity. How would I upgrade it, and what would be the process for making sure that I do not mess up any existing queries or data?

BTW, thanks for helping me get on track...

Beengie

More or less these are the steps:
(1) run SQL 2008 Upgrade Advisor, identify broken code/features
(2) Backup from SQL 2000
(3) Restore on the new SQL 2008 server
(4) change compatibility mode to 100 only if your application will still work on SQL 2008 (you need to test it!!!)
(5) fix any broken code
(6) Rebuild all indexes
(7) Update stats
(8) Run CheckDB with the Data_purity option.

You can start with a dbcc checkdb on your sql 2000 databases.
Next start a full backup of your SQL 2000 databases.
Now you will need to transport username and passwords between instances.
For that you may read this Microsoft KB:
http://support.microsoft.com/kb/246133
The important section of this KB is "Method 2".
Run the 2 stored procedures provided by Microsoft on your SQL 2K, read carefully the output and choose which login you need to transport.
Run the output code on your SQL 2008 and then restore your databases.
Now you will need to check if you have any orphaned user with this stored procedure:
exec sp_change_users_login 'report'

You may also need to run updateusage:
dbcc updateusage(0);

You may need to set page verify to checksum:
alter database "yourdb" set page_verify checksum;

Also run dbcc checkdb on you sql2k8 dbs:
dbcc checkdb with no_infomsgs, data_purity;

I agree with Tara, this is a very large topic.
I also suggest to try all that in a TEST environment if you can.

Franco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources