Tuesday, November 3, 2009

Replication, Restores, and Reseeding Identity Columns

I recently ran into an issue where, after restoring a replicated database, all the identity columns were seeded to start over at 1.  To make sense of my situation, I'll describe it a bit first.

Situation
I have database servers in multiple geographic locations: our production server is off-site while our development environment is on-site.  Being a smaller company, our resources to get high-speed lines into our office are limited and not real high priority right now.  I do, however, have a replication subscriber on-site that subscribes to our production database - works great.

Problem
In order to have a fresh testing environment, I wanted to restore our production database into our development environment each morning so developers had fresh live data to work against.  Problem is, to copy a production backup from our remote location on-site would take a half day whilst consuming most of our bandwidth.

Solution
I decided to run a backup of our on-site replicated database and restore directly from that backup.  Works great - takes 15 minutes or so.  However, as described earlier, the identity columns are not seeded properly - developers were getting primary key violation errors because of this.

Here is how you can resolve this issue in a single script, which for me I included as a step in my restore process.  Basically I'm reseeding all the user tables that have identity columns.

SET NOCOUNT ON

DECLARE @table_name VARCHAR(200),
    @hasidentity TINYINT

DECLARE table_cursor CURSOR
    FOR SELECT  TABLE_NAME
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE'
                AND TABLE_CATALOG = 'database-name'
        ORDER BY TABLES.TABLE_NAME

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name

WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT  @hasidentity = OBJECTPROPERTY(OBJECT_ID(@table_name),
                                              'TableHasIdentity')
        IF @hasidentity = 1
            BEGIN
                DBCC CHECKIDENT ( @table_name, RESEED ) 
            END                           
       
        FETCH NEXT FROM table_cursor INTO @table_name
    END
CLOSE table_cursor
DEALLOCATE table_cursor