identity property not coming over when using sql script

[beckyh]

I am trying to make a copy of a database. When the wizrd writes a script to recreate the database, and run it, I find that all identity properties are not there. How can this be accomplished??

 

[beckyh]

I apologize, I meant to write, I am trying to make a copy of a table.....

 

[SonOfEmidec1100]

Both methods used to recreate a table in another database, always seem transfer the identity column attributes correctly. But primary keys,indexes,defaults and constraints need special attention.

Which method are you using DTS wizard or Generate SQL Script wizard ?

 

[beckyh]

I am using Generate SQL Script Wizard. It seemed to be copying over keys properly (when I checked the boxes to copy keys, indexes, triggers etc...), but not identity columns. Is there another method I should be using?

 

[SonOfEmidec1100]

Should be OK. In my example the field Recid will be created as an identity field. If you look at your script you should see something similar. If its missing the only thing I can think of is the identity column is not on the field you expect in the source table. If it is maybe run my example and see if it works, it works for me. (change table name if _zzz is already used)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_zzz]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_zzz]
GO

CREATE TABLE [dbo].[_zzz] (
[RecID] [int] IDENTITY (1, 1) NOT NULL ,
[CurDate] [datetime] NULL ,
[UserId] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[_zzz] WITH NOCHECK ADD
CONSTRAINT [PK__zzz] PRIMARY KEY CLUSTERED
(
[RecID]
) ON [PRIMARY]
GO