4

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.

7
  • Could you post the table definition for one of the tables in the database you are having trouble with? I suspect that the problem is something with the ORM software though, or you are doing something unusual since the behavior you are describing is not normal. Are you doing the restore by script? If so, please provide that. Feb 10, 2017 at 13:37
  • 2
    Weird idea: have you performed multiple full backups like this? Try running RESTORE FILELISTONLY FROM DISK = 'path\to\file.bak' and see if there are multiple backup sets in there. It could be that you're restoring an old version of the database. Feb 10, 2017 at 13:43
  • @JonathanFite The thing is that this has worked before. All backups/restores are done by SSMS.
    – F.P
    Feb 10, 2017 at 14:05
  • @sp_BlitzErik But then the data would also be old, correct? Because all accounts are there, all IDs are there, but when I add new rows, it starts with 1 instead of 2000something
    – F.P
    Feb 10, 2017 at 14:23
  • 1
    @FlorianPeschka yep, you'd have old data too. So next suggestion would be to run DBCC CHECKIDENT ('dbo.YourTable', NORESEED) to make sure the identity seed is correct to begin with. Feb 10, 2017 at 14:29

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.