Restoring SQL Server 2014 database in SQL Server 2005

Question

I am trying to restore a SQL Server 2014 database in SQL Server 2005. I am doing by generating the complete script (with schema and data) from SQL Server 2014.

I get an error saying

'INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.'

and

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

I have searched for solution but have not been able to rectify the problem. I have tried the solution given in this link, but it didn't help me.

Can anyone suggest me some solutions for restoring the database.

Answer 1

Update: sorry - misread your question...

The first error means just that - for some operation you're trying to do, your SET ANSI_PADDING setting is wrong. Read the official documentation on ANSI_PADDING on MSDN to learn more about this setting. Check what your setting is now - obviously, if that error occurs, you need the other setting for that operation you're attempting to do.

The second error means that your table T_GASAdminNote has an identity column, and you're trying to insert values into that column, but without first enabling this by using SET IDENTITY_INSERT T_GASAdminNote ON (and don't forget to disable the option after you're done!)

• MaxPayne999: I generated scripts and i got those errors while running the script
• David Rushton: That can happen. It's always a good idea to review generated scripts before running them.