Solved

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

Posted on 2015-02-12
15
142 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 47

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 47

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 47

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 47

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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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