Sql-server – Restoring database backup makes identity column start at 1 again

backupidentityrestoresql serversql-server-2012

I created a backup of our database in SSMS 2012, using Tasks -> Backup as a Full Backup, including all data.

When I restore this backup on another server (2012 as well), all identity columns are reset to start at 1, which in turn messes up the ORM library (NHiberate) the application uses.

An example is the accounts table. After I restored it and created a new account, that account's ID started with 1, although on the production system that I backed up, the IDs were up to 2000 or something.

CREATE TABLE [accounts](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](255) NULL,
    [password] [nvarchar](255) NULL,
    [passwordSalt] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
) WITH (PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    FILLFACTOR = 90) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
    [name] ASC
) WITH (PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Why is this happening and how can I transfer the database, including all data so the identity columns are properly restored?

The backup file only contains two sets, the Data and Log entries.

Running dbcc checkident('accounts', noreseed) on the source server yields Checking identity information: current identity value '2816', current column value '2816'. On the target server, it returns 1 as the current column value.

Best Answer

Some more digging with the server administrators revealed that the problem was Schemas. On the source server, all tables were in a special schema, let's call it Custom. The Login I used to manipulate data and create the backup with was assigned to this schema and used it as default.

Transferring the database to the target server brought the user along, but not the login (that is stored on server level). So the administrator went along and created a new login that matched the user. That didn't work because the user already existed and SSMS tries to create the user in the database when creating a new login.

So he deleted the user, created it along with the new login, but didn't set the default schema to Custom.

That meant that any query and connection that used that login for some reason saw the table definitions, but not the data inside it - and apparently that included the identity columns as well.

So we switched the new user to use the Custom schema and now everything works as expected.

Related Question