We often build databases on behalf of our clients and we're still using SQL
7 as a base level as we can generate .BAK files which can be restored to SQL
7 and SQL 2000.

I assume that if we upgraded to SQL 2005 on our production server that we
will be *unable* to restore .BAK files generated on that version onto an
older server running SQL 2000 or SQL 7.

If so, how to developers normally distribute their database? Note: we can't
use DTS and we're restoring to a server on a completely separate network.

Thanks, Rob.

Rob Nicholson (rob.nicholson@nospam_unforgettable.com) writes:
Yes, you cannot restore a backup on SQL 2005 on SQL 2000 or SQL 7.

We build our databases from scripts using our own load tool, which we
then but into an install kit. Distributing a shrink-wrapped database is good
for the first installation, but it does not fly with upgrades. So in the
end, you need an ability do to scripts.

But of course, for a first installation, attaching a complete database
makes that installation faster. For us this is not an issue, as we work
with an expensive product with a small customer base, so we upgrade
far more often that we build a new databse.

Sounds a similar scenario to our app - small user base, niche product. We
typically build and configure the system locally and then take the BAK file
to the customer for pilot installation.

Is it possible to DTS a database from SQL 2005 -> SQL 2000? It so, that
might be an option. We can set-up a SQL 2000 server in development, DTS to
there and take the BAK from that environment.

We'll not be using any SQL 2005 specific functions but I'm looking at our
strategy for 2006 which is including major upgrades of all production
servers like SQL.

Cheers, Rob.

Rob Nicholson (rob.nicholson@nospam_unforgettable.com) writes:
There isn't even a DTS in SQL 2005, it's called SQL Server Integration
Services. Maybe there is a way to export from SQL 2005 to SQL 2000, but
I actually tried to use the Copy Database Wizard (which uses SSIS) in that
direction the other week, but I was told to that it was not supported.
(Not that I would recommend the Copy Database Wizard anyway. I filed a
number of bugs for it during the beta programme, and not all are fixed
in the RTM version.)

If you want to look at what we use, our toolset is available as freeware
from http://www.abaris.se/abaperls/. It's fairly complex, and not easy to
get started with, but it might give you some inspiration.

Help with constraint
How does engine decide whether to scan or seek index
Table design (search with ranking)
Nightmare Query
Calculating Row Size