DTS newbie question regarding indexes

DTS newbie question regarding indexes

Post by Neil Trub » Sun, 30 Jun 2002 06:12:30



I'm using DTS to copy a database from one SQL Server to another, in order to
create a test database.

It all works fine, but when I look at my copy database, it has no indexes.

I'm using the DTS wizard and, try as I might, I cannot find an option to
copy and create the indexes.

I realise that creating the indexes before loading the data will slow down
the migration.

I have two questions:

1.    Is there an ioption to copy indexes also?
2.    What is the simplest way of generating just the index SQL from the
source database to use to build them on the copy?

thanks
Neil

 
 
 

DTS newbie question regarding indexes

Post by BP Margoli » Sun, 30 Jun 2002 07:25:06


Neil,

On the "Specify Table Copy or Query" form, choose the "Copy objects and data
between SQL Server databases" option. When you hit Next, the next form ...
"Select Objects to Copy" ... does say "You can copy ... and indexes. Choose
the objects to copy."

However, it appears that indexes can not be explicitly checked anywhere, so
I'm going to assume that they fall in the "Include all dependent objects"
option.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I'm using DTS to copy a database from one SQL Server to another, in order
to
> create a test database.

> It all works fine, but when I look at my copy database, it has no indexes.

> I'm using the DTS wizard and, try as I might, I cannot find an option to
> copy and create the indexes.

> I realise that creating the indexes before loading the data will slow down
> the migration.

> I have two questions:

> 1.    Is there an ioption to copy indexes also?
> 2.    What is the simplest way of generating just the index SQL from the
> source database to use to build them on the copy?

> thanks
> Neil


 
 
 

DTS newbie question regarding indexes

Post by Steve Kas » Sun, 30 Jun 2002 09:43:03


BP and Neil,

  BOL says:

Options
Create destination objects

Create destination objects for all objects (tables, views, stored procedures, defaults, rules, constraints, user-defined data types, logins, users, roles, and indexes) to be transferred. Enable the following options:

Drop destination objects first
Drop all corresponding destination objects before creating new ones.

Include all dependent objects
Include all dependent objects, such as the tables supporting a view, in the transfer of data.

Include all extended properties
Include all extended properties, which are user-supplied definitions on various objects in the database. For more information, see Using Extended Properties on Database Objects.

So it sounds like if you copy objects instead of tables, you get the indexes for free.

Steve Kass
Drew University


> Neil,

> On the "Specify Table Copy or Query" form, choose the "Copy objects and data
> between SQL Server databases" option. When you hit Next, the next form ...
> "Select Objects to Copy" ... does say "You can copy ... and indexes. Choose
> the objects to copy."

> However, it appears that indexes can not be explicitly checked anywhere, so
> I'm going to assume that they fall in the "Include all dependent objects"
> option.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I'm using DTS to copy a database from one SQL Server to another, in order
> to
> > create a test database.

> > It all works fine, but when I look at my copy database, it has no indexes.

> > I'm using the DTS wizard and, try as I might, I cannot find an option to
> > copy and create the indexes.

> > I realise that creating the indexes before loading the data will slow down
> > the migration.

> > I have two questions:

> > 1.    Is there an ioption to copy indexes also?
> > 2.    What is the simplest way of generating just the index SQL from the
> > source database to use to build them on the copy?

> > thanks
> > Neil

 
 
 

1. newbie Q regarding indexes and identities

I've actually been using SQL6.5 for quite sometime but can usually get
what I need done through Interdev's GUI.
Recently, I've had to do some big queries which were giving me some
funky stuff through Interdev, so I began using Enterprise to do the
stuff, but have run into some (what would normally be trivial) problems:

1)  Is there a way to easily delete a field?
2)  What's the best way to create an identity and have it automatically
populate?
3)  What's the different between a clustered and non-clustered index

Thanks in advance,
Chad

2. HELP!!! System Calls in ESQL/COBOL

3. Btrieve newbie questions regarding accessing data

4. Do we still have locking problems with concurrentusers

5. Web: Newbie question regarding importing form data from web into FM Pro 5.0

6. Pro*Cobol precompiler

7. Newbie Question Regarding Locking Down Records

8. Put a DBASE binary (bitmap) field into a TBITMAP??

9. Newbie question regarding VB6 / DAO 3.6

10. Newbie question regarding ADO

11. Newbie question regarding dynamic sql

12. newbie question regarding query

13. Newbie question regarding SQL Server and TCP/IP for IIS Access