Running Generated scripts throws Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF

0
votes

I am trying to move a database from a server with SQL Server 2017 Express edition to another server with SQL Server 2014 Standard Edition. Database backup/restore was not an option because of version problem so I had to generate scripts for entire database and run them on new server.

Since my total database is about 4 GB (with data) so I first created the schema scripts only and ran them on target server, which worked perfectly fine. Then I created the data only scripts with database compatibility set to SQL Server 2014 Express.

When I run these scripts on the target system, I get a lot of errors like below.

Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF.

I know the problem is because identity column is set to true for the tables for which I am facing this issue and then I am supplying values for identity column. I want to use the same values for identity columns that are their in my old server's database.

How do I successfully restore this database?

2
sqlsql-serversql-server-2014sql-server-2017
Set IDENTITY_INSERT ON - Dale K

2 Answers

0
votes

Before inserting to an identity column you must allow it with set identity_insert <table name> on. Check this command. After you are done, set identity insert off.

Maybe there is some option to restore identities. In regular SQL Server restore runs OK.

0
votes

Maybe it is possible to temporarily remove an identity property from the source table before generating scripts and restore it later. If it is possible.