Restoring SQL databases

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:
> 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.
Yes, you cannot restore a backup on SQL 2005 on 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.
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> 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:
> 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.
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 w, 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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx