SQL Server 2005 - How to copy database with objects?

I've been using SQL 2000 for my stuff at DASP, and now I'm thinking of opening a SQL 2005 account.

In SQL 2000, it's very easy to copy a database from my development machine to DASP by using the DTS Import/Export wizard. It gives me fine control over what I want copied over. For example it copies objects like my stored procedures, views, etc.

But now I'm wondering how to accomplish this in SQL 2005? Because it appears SQL 2005 Export/Import only handles tables, not the other objects.

Any thoughts or ideas will be appreciated.

Polybius

Polybius

 

Howdy,
SQL Server 2005 does handle these tasks much differently.
At times I just create two sets of scripts, one for the tables and objects, one forthe records.

Yes in the past I loved the way DTS in SQL 2000 worked, I even preferred the methods I used in QA.

This is word-of-mouth from Microsoft concerning the changes:



Microsoft said...



The Import / Export wizard is designed to enable the user to easily move data to and from SQL Server. Copying database objects between servers is really a management operation rather than a data movement operation, even though data is, in fact, moved.


The Copy Database Wizard in the SQL Server Management workbench enables users to copy database objects, and is specialized for doing this.


The DTS Import / Export wizard, on the other hand, simplifies typical DTS operations?loading data from diverse sources including flat files, spreadsheets and OLEDB data sources.

By the time we find some good methods for this in SQL Server 2005 we'll all be using SQL 2008.

Here are some helpful links when you need them:
http://blogs.msdn.com/sqltips/
http://search.live.com/macros/sql_server_user_education/booksonline

Salute,
Mark

 

Mark - thanks for the thoughts.


There must be a hundred or more people hosting their SQL 2005 databases at DASP.


And they must all have a common need to frequently copy theirSQL 2005 database tables, stored procedures, views and other objects from their development machines to DASP.


How are they doing it?


Thanks.


Polybius

 

I use the database publishing wizard


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


Overview can be found here


http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx