Solved

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

Posted on 2015-02-12
15
150 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 48

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 48

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 48

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 48

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 48

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 48

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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