Moving a DTS to another server

Hi I'm very new to this but what I'm looking to do should'nt be too difficult although I've tried to no avail.

I'm looking to deploy a web site that uses SQL2000 to another server. Im trying to find out how to move the database to that server. Ideally I would like to be able to save it to CD for safety but its not like old access where you save the mdb file. I need to save the whole 'DTS' package, is that what its called? stored procedures, triggers the lot.

I tried the wizards but just got in a mess.

ThanksDTS stands for Data Transformation Services. It's a part of Sql Server that is useful for copying tables and other database objects, importing and exporting data, and doing other ETL (extract, transform, load) functions. While you may want to use a DTS package to move your database it doesn't by itself represent the set of tables, views, stored procedures, and other objects that make up a database.

Options for copying or moving a database include:

You can detach a database, copy the mdf and log files to another server, and then re-attach them on the other server. You need to detach from the current db before you copy because otherwise it will say the files are in use.

You can backup the database, copy the backup to the other server, and then restore it there.

If can connect to both database servers from Enterprise Manager you can use DTS to copy all the database objects (tables, views, stored procedures, etc.) to the other server. There is a wizard within Enterprise Manager to help you do this. Right click on the database, select All Tasks->Export Data. After selecting the source and destination servers, select "Copy objects and data between SQL Server databases". It will prompt you for various details about what to copy and whether you want to save it as a DTS package.|||Thanks for that, can I ask how do I go about detaching the database prior to saving the mdf file and also could you tell me where this mdf file resides?

Thanks again|||You can detach a database this way (just make sure that it's not currently in use):

sp_detach_db 'MyDB'

By default, databases are located at c:\Program Files\Microsoft SQL Server\MSSQL\Data and the logs are at c:\Program Files\Microsoft SQL Server\MSSQL\LOG. But you can change the default for a server or for individual databases, and you can scatter filegroups all over the place.

If you have Enterprise Manager, you can right-click on the database name, select Properties, and look under the Data Files and Transaction Log tabs to see where the files are located. If not, you can get the information using osql.

Enough information?

Don|||To detach a database:

1. Shrink it first to make the file sizes smaller. (This is sort of like compacting and repairing an Access file.) Select the database in Enterprise Manager, right-click, select All Tasks->Shrink Database... Click OK on the dialog that comes up.

2. Backup the database just in case: All Tasks->Backup Database...

3. Detach database: All Tasks->Detach Database... (If any process is still using the db it won't let you detach until they are cleared. If the database is being used for replication it also won't let you detach it).

4. File location. They are probably in C:\Program Files\Microsoft SQL Server\MSSQL\Data. Look for two files names: YourDbName.MDF and YourDbName.LDF. They should have very recent timestamps for when you detached them. If the files are not there just search the hard drive for one of those names. The db files don't have to be in that folder but it's the default if not otherwise specfied. If you are using MSDE then the path will be slightly different.