Sql-server – Copy SQL table to other server identity becomes int

identityreplicationsql-server-2008-r2

I just built an Umbraco website. This uses a not-that-small SQL Server db. I copied this DB using the export data function built in in SQL Server 2008.

This copies all the tables and their content but it sets (for the one table i know) the Identity(1,1) column to "int". This way my umbraco installation fails over this.

As I tried to change the column type to identity, obviously I can't edit the columns.
Even if there was a way to do this manually, I'd still have to change most of the tables in the new database.

Is there some option or so I missed when copying? I'd like to make it copy with the included identity.

I tried backup, and restore but my SQL server edition are not exactly the same. The one is x86 and the other is x64, which is giving me compatibility issues.

Thanks in advance!

Best Answer

If you want to copy a DB from A to B, use the backup and restore commands. This might be easier for what you need to do.

If you want to export the tables: during the export process at the Select Source Tables and Views screen, click on the Edit Mappings button, then check the Enable identity insert check box.

Related Solutions

Sql-server – SQL Server 2008 R2 Transactional Replication “Cannot insert explicit value for identity column…”

Just to add that you don't have to drop and re-create the replication just to change the "not for replication" bit.

You can do it using T-SQL without generating a snapshot or breaking your replication --

sys.sp_identitycolumnforreplication

1 = not for replication

0 = for replication and this causes the problems with Identity colums on subscriber side

To change it for all the tables :

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

To change it for just 1 table, first find out the object_id of the table and then run below

EXEC sys.sp_identitycolumnforreplication table_object_id, 1

EDIT

Below tsql will give you a nice output with command that can be reviewed before running for entire database :

if exists (select 1 from sys.identity_columns where is_not_for_replication = 0)
begin
SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) as SchemaName,  
        QUOTENAME(t.name) AS TableName, 
        c.name AS ColumnName,
        c.object_id as ObjectID,
        c.is_not_for_replication,
        'EXEC sys.sp_identitycolumnforreplication '+cast(c.object_id as varchar(20)) + ', 1 ;' as CommandTORun_SetIdendityNOTForReplication
    FROM    sys.identity_columns AS c 
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1
        and c.is_not_for_replication = 0
end
else 
print 'There are no identity columns that needs NOT FOR REPLICATION set to 1'

For adventureworks database :

Sql-server – SQL import data when both source and destination has data and identity column

Don't just use the whole table. Instead choose the option to write a query.

BasicInstructions

Then simply write a select statement that doesn't use that Identity Column. SoBasic

Alternatively, when you map columns, if you click "Delete rows in destination table", it will truncate the target table before loading data. If the opportunity exists for data to be removed from the source system and it should have remained in the destination, then this step will not be right for you.

enter image description here

Your package would look something like this