Rico (me@you.com) writes:
I have a database in 2005 that I'd like to copy and attach in 2000. Is
this possible? if so, how do I do it? I've tried doing a number of
things from just trying to attach to the db file to trying to export and
import via access and I can't seem to get anything to work.
You cannot attach a database from SQL 2005 on SQL 2000. When the developers
completed SQL 2000, they did not what know would be in the product five
years later.
To copy the schema, right-click the database in Mgmt Studio and select
Tasks->Generate Script. There is a setting to force SQL 2000 syntax,
you need to select that one. (I seem to recall that there was a bug
with this in SQL 2000 RTM, so make sure that you have SP1.)
If memory serves, create of foreign keys is at the end. In any case,
split the script so that FKs are in a script of their own. Run only
the script without the FKs. Beware that copying the schema can only be
completed successfully, if there is on use of new features in SQL 2005 in
the database.
To copy the data run this:
SELECT 'BCP db..' + name + ' out ' + name + '.bcp -T -S server2005 -n'
FROM sys.objects
WHERE type = 'U'
AND objectproperty(object_id, 'IsMSShipped') = 0
Copy and paste result, run from a command-line window. Then change
the above to:
SELECT 'BCP db..' + name + ' in ' + name + '.bcp -T -S server2000 -n ' +
case when ident_current(name) is not null then ' -E' else '' END
FROM sys.objects
WHERE type = 'U'
AND objectproperty(object_id, 'IsMSShipped') = 0
Copy, paste and run again.
Finally rnn the script with the FK:s.
Note the BCP stuff assumes that you have everything in the dbo schema,
as is the only thing that makes sense on SQL 2000.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
