MS SQL 2005 identity lost new database

[anselme]

Hello

I am creating a TABLE

CREATE TABLE [dbo].[TbTest](
[id_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
[Title] [varchar](100) NULL,
) ON [PRIMARY]


when looking at the Column Properties in MS Server Management Studio I get :
Identity Specification = Yes
(Is Identity) = Yes
Increment = 1
Seed = 1

now if I want to make a copy of that database, importing datas and tables

I get :
Identity Specification = No
(Is Identity) = No

How can I avoid this problem ? my application is not working anymore

ALTER TABLE [dbo].[TbTest] WITH NOCHECK ADD
CONSTRAINT [PK_TbTest] PRIMARY KEY CLUSTERED
(
[id_TbTest]
) ON [PRIMARY]


is not solving that problem

thank you

[skhanal]

How did you make a copy of the database?

[rmiao]

If you copied with 'insert into ... select ... from ...', it doesn't copy any column extened properties. Better to script the table, modify table name in the script then create new table with the script.

[anselme]

task > Import Datas
and it was supposed to create the tables + datas

but how can you script an ALTER COLUMN for IDENTITY (1,1) ?

thank you for helping

[rmiao]

Should choose transfer objects in the wizard.

[shamshe]

It is impossible alter column to type IDENTITY directly, but you can do it in few steps:

ALTER TABLE [dbo].[TbTest] ADD
[id1_TbTest] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED;
ALTER TABLE [dbo].[TbTest] DROP COLUMN [id_TbTest];
EXEC sp_rename '[TbTest].[id1_TbTest]', 'id_TbTest', 'COLUMN';

good luck!

[anselme]

rmiao : transfer objects ? i'll try thank you

shamshe : wonderfull ! I try at once , thank you