Making a database that will work with SQL 2000 and 2005

So I've been working on this VB.NET app that saves to an SQL database for a few weeks now and I've been using various different PCs to write it on (home, work etc). All of these machines so far have had SQL Express 2005 installed on them so I created the database in SQL Management Studio 2005 and have just been taking the most database files with me and attaching them to each PC when I need to. However, I just tried attaching the database to an SQL Server 2000 machine and it wont work... says something about the system index being incorrect - I've googled this and found that a lot of places just say it is because the DB was created in 2005.

So, how can I create a database that will work on all platforms? Is it possible or should I just specify that SQL Server 2005 is needed to run my app?

Cheers
Chris

I'm not 100% sure it's available in Express but in the full Management Studio, when you create a new database you can specify the Compatibility Level as SQL Server 2000 on the Options page. The default is, of course, SQL Server 2005.

That said, you can just require SQL Server 2005 unless you think that that might alienate some customers you'd rather not alienate.

When you create a new SQL Server database in 2005, if you click on the options dropdown you can specify its compatibility level. You can also do this for databases already created by right clicking over the DB, and selection the properties, then options.

ps. I presume that SQL Server 2005 is backward compatible so creating it in SQL Server 2000 should mean that it can work on 2005 as well.

thanks, I just checked that Properties -> Options -> Compatability Level and it says its already in SQL 2000 mode :(

Quote:

---

Originally Posted by kevchadders

ps. I presume that SQL Server 2005 is backward compatible so creating it in SQL Server 2000 should mean that it can work on 2005 as well.

---

This would be my suggestion.

You can't use a database created in SQL 2005 in a SQL 2000 instance of SQL Server. The format of the files are different. The compatablility level has nothing to do with that.

Your choice is one of 3 things.
1. Limit the users to SQL Server 2005 and supply files for an empty
database.
2. Create the database is SQL Server 2000 and supply the files for an
empty database. (You may need to check if a 2000 or 2005 instance
and set compatability level if required).
3. Create the database complely each time an install is performed by
scripting the database and any inserts you need. Using this method
the customer can have a SQL 2000, 2005 or a 2008 database server
instance and you don't have to worry about it.

The compatibility level only controls the SQL version you are accessing (IE, you can't use any additions to SQL that came part of 2005 - like CTEs.)... it does NOT have anything to do with the physical file layout of tyhe MDB - which is the problem here. The ONLY way to get a DB to work for both 2000 and 2005.... is to create it using SQL Server 2000 ... that will, naturally, create the db using SQL2000 format. When it is attached to a SQL Server 2005 instance, it will retain its 2000 format. If the SQL Server is upgraded.... it will still retain its 2000 format. But if a DB is created in SQL 2005 from scratch, it will have the SQL2005 format and will NOT work on SQL 2000.

-tg

Gary posted sametime I was writing... if it were me.... I'd go with option 3 anyways.

-tg

Wasnt sure the effect the compatibility part had on the Database.
Good explaination guys. :thumb:

Probably why i included my ps bit. ;)

@tg - are you completely sure about that? We never use attach - we always restore from .BAK - but when we restore a 2000 BAK onto a 2005 machine it goes through upgrade stages during the restore.

Are you saying that simply attaching the 2000 DB on a 2005 machine will not go through these upgrade stages?

szlamany - yup .... don't make me dig out the article (I don't remember where I read that at - I think it was a white paper on MS). The reason the RESTORE does, is because it overwrites the files, essentially starting from scratch. But simply attaching the MDF or even taking an existing SQL2000 instance and upgrading it to SQL2005 doesn't change the files (but subsequently added databases will have the new SQL2005 format.)

-tg

Thanks for the info.

...not like it's really important to us because we use 2005 features anyway so a 2000 install would not be possible for us - but it's good to know it works that way

It's not that 2005 feartures are unavailable -they are... what it affects is the underlying database format of the file itself (ah, there is one functionality that isn't possible in a SQL2000 sourced DB... databse diagrams...) the sql capabilities (like CTEs) are then controlled by the compatibility setting discuss earlier in the thread. So if I have a SQL2000 database and attach it to a SQL2005 Server.... Unless I revert the compatibility layer back to SQL2000, I should be able to use CTEs. At least that's the way I understood the article.

-tg

Wow - that's enlightening!

They should have shared that with us in greater detail before we all went an "upgraded" our db's to 2005 "file format" for no great reason!

There is though... their file format changes were done for performance reasons... alegedly to allow for faster access to the data... so upsdizing the files isn't necessarily a bad thing... just not a necessity... unless you are using Database Diagrams... then there's no choice.

-tg

edit - I should note that there maybe otehr functionality that's file format based, but the DB Diag are the only ones I know from first hand experience (the hard way).

It's just something we are going to keep more in mind with the next release of SQL...

We have customers that are all at 2005 now - so it's a non issue at the moment.

When we sell a new customer we take an "existing" customers db and migrate it to that new site - removing just data - leaving schema and objects and such.

If we get a new customer that is in a new state that has sql 2008 we will keep the DB at 2005 file format at first (we do lots of state by state changes to tables and such). And we will leave it at 2005 until we feel comfortable that other possible new sales in that state will all be coming in at 2008 levels.

Another time this would have been important was when one of our clients created a new SQL box and made that install be 2005. We simply used a .BAK to move the 2000 DB to the 2005 box. Knowing what I know now it would have been safer to "attach" the 2000 DB in 2000 file format until we felt the 2005 server was stable enough to never want to migrate back to the 2000 box.