Solved

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

Posted on 2015-02-12
15
134 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
  • 9
  • 6
15 Comments
 
LVL 45

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 45

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
 

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 45

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

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

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 45

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 45

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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now