MS SQL 2005 identity lost new database

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 youit sounds like you copied your data using SSIS or DTS or whatever you want to call it. This will copy your data but not your schema. You likely lost all of your indexes and constraints as well. What you want to do if you want to copy all of your data and your schema, is to take a backup of the database you want to copy and restore that database to a new location. If it is a new server, you will have to remap you logins as well.|||With SQL Server 2000 Enterprise Manager you could quickly and easily copy both data and schema between servers. With 2005, you can't. This is Microsoft's idea of progress...

A work-around is to generate a script for you schema and execute it on the target server, and then you SSIS to transfer just the data.|||thank you

what is the code line for ALTER COLUMN to create IDENTITY (1,1)
I must do it now with code ?|||there is not one.

I am a little concerned from what you said in first and last post that you are going to mess up all of your primary key and foreign key relationships.

however if you must you need to create a new table and in your create table statement you must define an identity column. copy the data from your current table to the new one. drop the old table, and rename the new one.|||in that way maybe ?:

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';

thank you