SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Upgrade from 2000 or before
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Beengie
Starting Member

USA
3 Posts

Posted - 10/01/2013 :  19:08:50  Show Profile  Reply with Quote
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

USA
35937 Posts

Posted - 10/01/2013 :  20:22:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Beengie
Starting Member

USA
3 Posts

Posted - 10/02/2013 :  09:16:30  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 10/02/2013 :  15:44:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Beengie
Starting Member

USA
3 Posts

Posted - 10/03/2013 :  10:23:52  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 10/03/2013 :  12:47:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

franco
Constraint Violating Yak Guru

Switzerland
255 Posts

Posted - 10/08/2013 :  09:17:11  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000