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

MS Service Pack frustrations

I just have to vent. I just upgraded out production database to a pair of
clustered DL740 servers running Windows Server 2003 Enterprise Edition, SP1.
My SQL is 2000 with Service Pack4. The "new" items in this configuration are
the servers themselves, Server 2003, SP1 and SQL 2000 SP4.
My first gripe is with the Windodws 2000 SP1. Microsoft Windows Server 2003
Service Pack 1 (SP1) implements a security feature that reduces the size of
the queue for concurrent TCP/IP connections to the server. This is done
because the OS assumes a DoS attack is occuring. There is NO notification
that this event has happened in any event log. Do you suppose that
administrators might want to be notified if the OS thinks such an event is
occuring? Not only does this frost me, but I spent more than 8 hours over two
days on the phone with MS tech support and THEY DIDN'T EVEN FIND THE
SOLUTION!!! One of our developers searched KB articles and came up with
KB899599 which gave us our only clue to this issue. Once we implemented the
workaround, the problem was resolved. Meanwhile our customers experienced
dropped connections for three days.
My second gripe is with SQL 2000 SP4 and it's sudden lack of support for all
memory when using AWE. This little bit of fun is documented in KB899761.
Since both of these service packs were introduced in our new configuration,
you can imagine we had a fun time wading through all of this to get things
back to normal. I'm going back to my original stance that NOTHING from MS as
far as patches is worth putting on until they are at least 6 months old.
Thanks for letting me get on my soapbox.
KenAs Microsoft says: Test everything in a Test environment before you put it
into production.
I work for a very large bank, and with this process, we have never burned
our fingers and are capable of rolling out a patch or fix to all servers
knowing that it works as desired, and usually quicker than someone without a
test environment.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"kmkrause2" <kmkrause2@.discussions.microsoft.com> wrote in message
news:9A825D91-B20E-40EC-843F-77AADFE9A0ED@.microsoft.com...
> I just have to vent. I just upgraded out production database to a pair of
> clustered DL740 servers running Windows Server 2003 Enterprise Edition,
> SP1.
> My SQL is 2000 with Service Pack4. The "new" items in this configuration
> are
> the servers themselves, Server 2003, SP1 and SQL 2000 SP4.
> My first gripe is with the Windodws 2000 SP1. Microsoft Windows Server
> 2003
> Service Pack 1 (SP1) implements a security feature that reduces the size
> of
> the queue for concurrent TCP/IP connections to the server. This is done
> because the OS assumes a DoS attack is occuring. There is NO notification
> that this event has happened in any event log. Do you suppose that
> administrators might want to be notified if the OS thinks such an event is
> occuring? Not only does this frost me, but I spent more than 8 hours over
> two
> days on the phone with MS tech support and THEY DIDN'T EVEN FIND THE
> SOLUTION!!! One of our developers searched KB articles and came up with
> KB899599 which gave us our only clue to this issue. Once we implemented
> the
> workaround, the problem was resolved. Meanwhile our customers experienced
> dropped connections for three days.
> My second gripe is with SQL 2000 SP4 and it's sudden lack of support for
> all
> memory when using AWE. This little bit of fun is documented in KB899761.
> Since both of these service packs were introduced in our new
> configuration,
> you can imagine we had a fun time wading through all of this to get things
> back to normal. I'm going back to my original stance that NOTHING from MS
> as
> far as patches is worth putting on until they are at least 6 months old.
> Thanks for letting me get on my soapbox.
> Ken
>