Solved

How to open a '6.5 compatible' DB on MSSQL 2005?

Posted on 2015-02-12
15
158 Views
Last Modified: 2015-02-12
Hi,

I've got a DB that's displayed as '6.5 compatible' in my SQL Server Management Studio 2005 but I can't open it or do anything with it ... ? Any ideas? And no, I don't have an SQL Server 2000 somewhere ...

Thank you,

Jerome
0
Comment
Question by:Xeronimo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 40605504
Before doing something else with that DB, do you have a copy of it?
If so, try to change the compatibility to 9.0 (MSSQL 2005) and check if you can connect to the database. By default Microsoft only guarantee the compatibility 2 versions backward, so in MSSQL 2005 it should open at least a MSSQL 7 database but give a try on that one.
0
 

Author Comment

by:Xeronimo
ID: 40605519
Hi, what do you mean with 'a copy of it'? I've got the MDF and the LDF file ... but I don't have them working on a non-2005 SQL server ...

so would I have to find an SQL Server 2000 to attach those files to in order to change their compatibility?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40605532
If you have the .mdf and .ldf files simple copy them to a safe place.
If you can't attach the database in a SQL Server 2005 then I'm afraid there's no other option to you but find a SQL Server 7 or 2000 instance.
How big is the database? If it less than 2GB you can download and install the SQL Server 2000 Desktop Engine (the predecessor of Express Edition). And you'll need to install the respective SP4.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Xeronimo
ID: 40605537
Well, they're attached in SQL Server 2005 ... at least it seems to me that way since they're displayed there? But I can do anything with them ...

They're less than 2GB. I'll try the Desktop Engine option then. Thanks
0
 

Author Comment

by:Xeronimo
ID: 40605546
Hm, when I try to install the Desktop Engine (on Win7) I get this error:

ss
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40605557
I don't know if SQL Server 2000 runs on a Win7 machine. Also the message should be a warning only. Can you confirm if you have the SQL Server 2000 service installed and running?
0
 

Author Comment

by:Xeronimo
ID: 40605583
No, SQL Server 2000 did not install ... I got that error message during the installation ;)

But I've found an old server with SQL Server 2000 on it! I've attached a copy of the DB there ... so now what? How do I make the files readable for 2005? Thank you!
0
 

Author Comment

by:Xeronimo
ID: 40605586
Hm, the compatibility level only goes to 80 and SQL 2005 is 90!?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40605590
Good. In SQL Server 2000 Enterprise Manager change the compatibility level on that database to 8.0 (MSSQL 2000), then detach it and copy the files to the SQL Server 2005 instance. You should be able to connect to the database now. If so, change the compatibility level to 9.0 (MSSQL 2005).
0
 

Author Comment

by:Xeronimo
ID: 40605595
Ok, right now I'm simply trying to export the data from the SQL Server 2000 to the 2005 and that seems to work so far ...
0
 

Author Comment

by:Xeronimo
ID: 40605599
The level seems to have been on 80 already (or by default?) so I'm not sure that will change anything ... but exporting the data should achieve the same, no?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40605611
but exporting the data should achieve the same, no?
Depends. I would go for a full backup and restore (or detach and attach) option. Will guarantee that all objects will be in the new version (users, indexes, triggers, stored procedures, ...). Exports only take care of the data.
0
 

Author Comment

by:Xeronimo
ID: 40605827
Ok, I was able to change the compatibility level of the DB from 65 to 80 on the MSSQL 2000 server. I made a backup of it and restored it on the MSSQL 2005 server where I can now open it etc. Seems to work now ... Thank you!
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40605847
Don't forget to change the compatibility to 90 in MSSQL 2005 instance.
After that run the following command on the new database: DBCC CHECKDB WITH DATA_PURITY
And perform a rebuild of the indexes also.
0
 

Author Comment

by:Xeronimo
ID: 40605879
Ok, I will!
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL 2008 - Disappearing Temp Table in Stored Procedure 24 63
semaphore timeout period has expired 1 31
rolling count by date, hour query 7 31
SQL Server Pivot 5 42
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question