I have come across an issue with executing DELETE statement in SQL Server 2012 in a database which has been restored
from SQL Server 2005 to SQL Server 2012,
This is the delete statement:
DELETE FROM [Table] WHERE Field1_ID='xxx'
I restored a SQL Server 2005 backup on to a new SQL Server 2012 server using the following restore command:
DECLARE @backupfile varchar(200)
SET @backupfile = 'E:\SQL_BACKUP\DB_FULL_20141211_182000.bak'
RESTORE DATABASE DB_T
FROM DISK = @backupfile
MOVE 'DB_T_Data' TO 'D:\SQL_DATA\DB_T.mdf',
MOVE 'DB_T_Log' TO 'E:\SQL_LOGS\DB_T.ldf'
The RESTORE message output indicated that 'upgrade' steps did occur as part of the restore.
Also I did the following to actually change the compatability level, from 2005 to 2012
ALTER DATABASE DB_T
SET COMPATIBILITY_LEVEL = 110;
I have seen some articles online which suggest that the compatability level is the root cause.
However, as I have indicated above, I have taken all steps to get the 2005 database to the 2012 compatibility level.
Please let me know if anyone has encountered anything similar.