Sql-server – Problem with database migrated from SQL Server 2005 to SQL Server 2014

No. The compatibility level only affects some features that exists in both versions. To give an example from ALTER DATABASE Compatibility Level:

Compatibility-level setting of 90 or lower: MERGE is not enforced as a reserved keyword. Compatibility-level setting of 100: MERGE is a fully reserved keyword. The MERGE statement is supported under both 100 and 90 compatibility levels.

As you can see, you can still write a script that uses MERGE, a post SQL Server 2008 only feature, and run it in 90 compatibility mode, and it will work. However, the script will break on SQL Server 2005. The comptability level 90 only affects the enforcement of the keyword (which affects parsing, in 100 mode a table named MERGE would have to be enquoted in brackets as [MERGE]), but not the execution.

There are many more features like this. So your only option is to run against the target SQL Server.

There can be many things to address. Below is an outline of things to check before jumping on any conclusions :

First, your sequence of POST restore steps will mess up all the work you did. Never shrink your database and that too especially after doing Index maintenance.

Read up - Why you should not shrink your data files by Paul Randal.

Below are my common post restore steps for any lower version to higher version migration. In your case SQL Server 2005 to 2012.

• change compatibility level on the restored databases to 110 (with the caveat that it will require a full regression test)
• run DBCC UPDATEUSAGE on the restored databases DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS
• Update statistics on all tables with full scan : Update Statistics table_name with FULLSCAN
• Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's Index and Stats maintenance scripts.
• Recompile all SP's using sp_recompile 'procedureName'
• Refresh your views SP_REFRESHVIEW view_name

----------

As a side note:

• turn on Instant File Initialization on the new server.
• Have multiple tempdb data files with equal size.
• Enable Trace Flag 1118
• Configure max and min memory correctly. Especially Max memory away from default.
• Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
• Best is to install sp_Blitz from Brent Ozar. Run it and address the critical and high priority issues reported by it.
• You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.
• Enable Optimize for ad-hoc workloads and Default backup compression options.

Refer to UPGRADE and POST UPGRADE steps for more details.