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

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
XeronimoAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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
 
XeronimoAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
XeronimoAuthor Commented:
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
 
XeronimoAuthor Commented:
Hm, when I try to install the Desktop Engine (on Win7) I get this error:

ss
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
XeronimoAuthor Commented:
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
 
XeronimoAuthor Commented:
Hm, the compatibility level only goes to 80 and SQL 2005 is 90!?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
XeronimoAuthor Commented:
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
 
XeronimoAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
XeronimoAuthor Commented:
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
 
XeronimoAuthor Commented:
Ok, I will!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.