Sql-server – Moving SQL Server database from old production server to new server

I figured out a few things

select * from sys.dm_exec_query_stats

will show all the cached query plans. Unfortunately, no SQL text is shown there.

However, you can join the SQL text to the plans like so:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

From here it's pretty trivial to add a WHERE clause to find the SQL I know is in the query, and then I can execute:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

to remove each query plan from the query plan cache. Not exactly easy or convenient, but it appears to work..

edit: dumping the entire query cache will also work, and is less dangerous than it sounds, at least in my experience:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

In SQL Server Management Studio right-click your database and select Tasks / Generate Scripts. Follow the wizard and you'll get a script that recreates the data structure in the correct order according to foreign keys. On the wizard step titled "Set Scripting Options" choose "Advanced" and modify the "Types of data to script" option to "Schema and data"

TIP: In the final step select "Script to a New Query Window", it'll work much faster that way.