Restoring database backup makes identity column start at 1 again
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.
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
Our Awesome Free Tools
- Check your IP Address precisely
- Online JSON Formatter with Syntax Highlight
- Online CSS Minifier Compressor
- Online MD5 Hash Generator
- Online SHA-1, SHA-256, SHA-512 Generator
- Online Base64 Encoder/Decoder
- Online CRC-32 Calculator
- Online Triple DES Encryptor/Decryptor
- Best World Clocks
- Database Administration Tutorials
- Programming Tutorials & IT News
- Linux & DevOps World
- Entertainment & General News
- All the Free, Online Tools you need