I have a developer that deleted a very important row from a replicated table in a SQL Server 2005 database. The specific IDENTITY value for this row is used in a lot of places and is hardcoded. The table is basically a data lookup. Now, how do I restore this row. Attempts to insert using SET IDENTITY_INSERT fail because the value I need to insert is not within the identity range being used by the server (merge replication).
If the record was deleted on the Subscriber side, you could just reinitialise the subscription, then restart the snapshot on the publisher. It would then clear out the data at the subscriber, and copy it all again.
I'm not an expert on replication, but if set identity_insert didn't work I would think you'd need to do a restore.
The link below may assist you.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
I encounter this issue with P2P replication.
Please check this script that explains the way of disable IDENTITY columns.
Run it step by step.
Have a nice day
-- Create test table
CREATE TABLE Test
(
id int identity,
obs varchar(50)
)
--Verify the identity column
INSERT INTO Test (obs) VALUES ('Probando id')
GO
--Look at the id value
SELECT * FROM Test ORDER BY id
GO
--Now we need to insert more values for id
INSERT INTO Test (obs) VALUES ('Se inserta del #2 al #51')
GO 50
--Look at the id value
SELECT * FROM Test ORDER BY id
GO
--DELETE 10 records of the table
DELETE FROM test WHERE id between 30 and 40
GO
-- Number of records 51 - 10
SELECT COUNT(*) FROM Test
GO
-- id's 30 to 40 were deleted
SELECT * FROM Test ORDER BY id
GO
-- verify the identity actual value
DBCC CHECKIDENT ("Test", NORESEED);
GO
-- Disable identity insert
/*******************************/
set identity_insert Test on
GO
-- INSERT id values 30 and 31
INSERT INTO Test (id,obs) VALUES (30,'Se inserta del #2 al #51')
GO
INSERT INTO Test (id,obs) VALUES (31,'Se inserta del #2 al #51')
GO
-- Enable identity value
set identity_insert Test off
GO
-- verify records
SELECT * FROM Test ORDER BY id
GO
-- verify value for the identity column
INSERT INTO Test (obs) VALUES ('Probando id #52')
GO
