For Microsoft SQL 7 and SQL 8
DTS Transfers are possible from SQL 7 to SQL 7 or SQL 7 to SQL 2000 or SQL 2000 to SQL 2000 according to our tests. However, the transfer functionality is limited (due to the non-DBO privileges of the user) and requires a somewhat unintuitive process to complete with any measure of success. Using the DTS wizard, it is not possible to transfer user-defined data types. Also, indexes, keys and constraints will not get transferred correctly.
It may be possible to use a custom DTS script to accomplish a full transfer of all objects. This would be beyond the scope of our support, however.
The recommended way to “transfer” a database from a development server to our shared server would be to generate a TSQL script for all database objects, change where necessary, and then execute the script on the server. Then, the user could use DTS or BCP or some other method to just copy data. Unfortunately, this method may require a knowledgeable and experienced user.
Note: The procedure works with both the SQL 7 version of Enterprise Manager and the SQL 2000 version of Enterprise Manager. The screenshots are from the SQL 2000 version so the SQL 7 Version will look a little different.
Generating SQL Scripts:
Generate scripts using Enterprise Manager:
Right-click database -> All Tasks -> Export Data -> Generate SQL Script
Select all objects.
Optionally choose whether or not to generate the DROP statements and dependent objects.
You will need to uncheck all Security Scripting Options. Other options are up to the user.
After the script is generated, you will need to make some modifications. Use a text editor with search and replace to replace all instances of “dbo” (or whatever user owned the objects on the source server) and replace it with your userid. Note that if you chose the option to generate DROP statements, you will have to leave the reference to “dbo” in the parts of the script where system tables such as “sysobjects”, “systypes”, etc. are being qualified.
Also, remove any calls to the “setuser” function as these will fail.
There is another caveat. Enterprise Manager doesn’t always generate the statements in the right order so you may have to move things around in the script. For example, it may generate a statement for a stored-procedure or a user-defined function that references a table that is created further down in the script.
Once the script has been executed on the destination server, you can use DTS or another method to get the data to the tables. If your tables have foreign key constraints and relationships defined, you may have to transfer your data in separate batches so that you don’t violate referential integrity.
DTS Steps
Select all tables but uncheck all views. Note that the wizard modifies the destination side to appropriately qualify the tables with the appropriate owner.
Now you should have all data and tables copied. Now it is time to get the other SQL Server objects.
Repeat procedure until you get to here:
Uncheck Show all tables as all of the tables have been transferred already. Also uncheck Show user-defined data types as it is necessary to create those on the destination side first. Unfortunately, it is not possible to use DTS to transfer UDTS on our servers. This is because the script that the DTS wizard generates calls “SETUSER” statements which can only be executed by a DBO or sysadmin.
Make sure all three security options are OFF!
This document just attempts to show some simple ways to transfer database objects and data. A user may find different combinations of these techniques are more appropriate for their database. For example, a user could use the DTS wizard to transfer tables and data to the server and then use SQL scripts to apply constraints, indexes, keys, triggers, etc. after all of the data already exists.