SQL Server data dump and schema transfer for downward compatibility from 2005 to 2000

If all you have on administrating your SQL Server is just a SQL Server Enterprise Manager, which you can't upload any backup/restore file or dump file, then this tool is for you:

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

This would allow you to dump a SQL Server database from 2000/2005 to a single script file with everything included at 2000/2005 compatibility level.

However, if you are like me trying to dump a database created by non-SA level account on SQL Server 2005,  you would end up have schema ownership that's not dbo. This is not supported on SQL Server 2000, so you would have to transfer the schema.

Transferring such ownership is easy with SQL Server 2005 too. All you need is this T-SQL Statement:

   1: ALTER SCHEMA dbo TRANSFER [original_owner].[table_name]

I am too lazy to write a full automated sql, so I just use:

   1: Select table_name from Information_schema.Tables

to get all the table name and create the script using Excel. You may just write a cursor script to make it fully automatic.