SQL: 6.5 Compatibility in SQL Server 2005

This isn’t a very common scenario, but I figured I’d write about it anyway. We recently implemented a Windows 2003 Active Directory domain at work. I suggested to my employer that, going forward, we only install SQL Server 2005 when we need a database server (phasing out 2000). I’d also suggested consolidating a couple of our older servers (7 and 2000) into one.

So I setup a test SQL Server 2005 server in our lab environment. I migrated a few of our most active databases using two different methods: detach -> reattach and backup -> restore. Both worked perfectly well. Actually, the detach -> reattach complained about possible corrupt indexes but it was a false alarm. Other than that, things went very smoothly.

Apparently, one of the databases I migrated has been around for quite some time. It showed up as SQL 6.5 Compatible and lacked most of the options in the Management Studio.

SQL Server 6.5 Compatibility mode in SQL Server 2005 Management Studio

I assumed that this particular database started out on a SQL 6.5 machine which was then upgraded to SQL 7 and SQL 7 kept it in 6.5 compatibility mode. Still, the database worked and I was able to issue a few queries to it without a problem. I wondered if perhaps SQL Server 7 or 2000’s Enterprise Manager would allow me to manage that SQL 6.5 database in the GUI but I couldn’t even connect.. SQL 2005 requires that you connect via the management studio to administer the machine graphically. Query Analyser connected just fine though it took a little bit of tweaking from the SQL 7 machine. Initially, I ran into this error:

Named Pipes Connection Broken

Named Pipes Connection Broken. Alright, we’ll avoid NetBIOS and try by IP.. perhaps that would force a TCP/IP connection. No dice. It just forced a TCP/IP over NetBIOS connection. I thought back to the days of SQL 7 and did the following, which worked: Start -> Programs -> SQL Server 7 -> Client Network Utility -> Add -> Server Alias: labsql2k5 | Network Library: TCP/IP | Server Name: 192.168.1.x

That worked like a charm. Initially, I wondered if the Named Pipes connection was breaking because the two SQL Servers are on different domains (that have a 2 way trust) but I had no problems connecting to a SQL Server 2000 machine over a truested domain — no alias needed. SQL Server 2000’s Query Analyser required no additional tweaking to connect to SQL 2005. This may be due to the fact that TCP/IP was listed as the first network protocol to attempt.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SQL Server
19 comments on “SQL: 6.5 Compatibility in SQL Server 2005
  1. Samir Vaidya says:

    You can additionally change the database compatibility level so that it is usable by SQL 2005 Management Studio by issuing the following command:

    –Changes the database compatibility level to SQL 2000
    EXEC sp_dbcmptlevel ‘My65Database, ’80’;
    GO

  2. Chrissy says:

    awesome tip, thanks Samir!

  3. Pete says:

    Just upgrading from 2000 to 2005 and this tip was very helpful. But, I think I painted myself in a corner.

    I have a 6.5 compatible database that is offline… I can’t detach it, and can’t use the above EXEC command b/c the database is offline (around and around we go).

    In hindsight, I should have made all my database ONLINE and then updated the compatibility level and all would have been good.

    Thanks,
    Pete

  4. Greg says:

    worked great!! thanks for the tip!!

  5. reGotcha says:

    Great tip for changing db compatibility using a sp command

    — Reports the current compatibility level of ‘MyDBName’
    EXEC sp_dbcmptlevel ‘MyDBName’;
    GO

    — Changes it to SQL 2000 – 80
    EXEC sp_dbcmptlevel ‘MyDBName’, ’80’;
    GO

    — Changes it to SQL 2005 – 90
    EXEC sp_dbcmptlevel ‘MyDBName’, ’90’;
    GO

  6. David says:

    Samir , THANKS A LOT, i was stuck with this and didn´t know what to do.

    You’re a real saint

  7. Gilligan says:

    This fixed me right up as well. thank you very much!

  8. Oliveira says:

    Hi, i read this article and i want to ask your help, if possible.

    I’m in one near situation… I’m in one company that have one database in SQL server 6.5 too and they want to pass it to sql server 2005.
    At this moment i’m trying to replicate it in my owne pc, but i can’t, i’m making ino backup of the original database and try to use it in my owne pc for after try to update widtout the risk of damage data.

    My problem is exactly that… i install sql s 6.5 widhout problems but wen i try to put the backups that i made doesn’t alow me… Can u help me?

    Thanks for the attencion.

  9. Bruce Sharp says:

    Oh, sure, I’m two years late coming to the party, but I feel compelled to chime in and say: Chrissy, Samir, Regotcha… you ROCK. Thanks for a very helpful post!

  10. Adrian Harmer says:

    I agree with Bruce. This is exactly what I wanted and has saved me hours. Thanks a lot.

  11. Cynthia Paulas says:

    Thank you very much. It was just too easy.

  12. Gorgorlou says:

    Thanks Samir and Regotcha,

    Your posts are fantastic. My problem is fixed.

  13. Mike in Tulsa says:

    Samir and Regotcha – “Mavericks!”

  14. San says:

    My problem is different. We had a database in SQL 2000 in one machine. Took backup of the database and restored in another machine in which SQL 2005 runs. It is working fine since it is at compatibilty level “SQL Server 2000 (80)”. Now, my task is to make this database as fully SQL Server 2005 mode but not in compatibile mode. Any help would be appreciated.
    -San

  15. Thushara says:

    This is a good command and thanks a lot for sharing this

    Thushara

  16. pankaj says:

    is it applicable for SQL97??

    Please help

  17. Rick Droske says:

    Still helping people with this tip. Just restored a 6.5 backup to a 2005 server and this worked perfectly. I did have to remove the quotes from the version parameter as it is a smallint type on my machine.

    Thanks

  18. Dale W says:

    Excellent tip. We are converting from a very old system and I had this problem with the 6.5 compatibility. sp_dbcmptlevel solved my problem.

  19. Rafael says:

    being very honest I usually do not comment on post, but today I must expend few secondos and thanks you guys. It was the first post I found and saved me from a huge headache… Thanks again!

Leave a Reply

Your email address will not be published. Required fields are marked *

*