Can I Revert Back from In-Memory OLTP Tables to Standard Disk-based Tables?

Yes you can revert a memory optimized table back to a standard disk-based table. While the preponderance of material you’ll find on the Internet will tell you how to migrate to In-Memory OLTP memory optimized tables there’s very little advice on how to move back if the new memory optimized tables didn’t work out. In-Memory OLTP is a great technology but there could be a number of reasons for moving back. In-Memory OLTP is best when you have a situation where the application is waiting for locks and latches. If that’s not the case you may get the performance gains you expect. It’s also possible the migration could have required database changes that caused your applications to break or that you simply picked the wrong table to migrate.

Reverting back to normal tables is fairly easy and there are different ways depending on whether you are willing to tolerate data loss or not. If you are will to tolerate some data loss then you can always restore your database to point-in-time just prior to the migration to In-Memory assuming of course that you sensibly backup up the database before the migration. Alternatively if your SQL Server instance is a VM you could have taken a snapshot just prior to the migration and reverted to the snapshot. If you need to save the data changes in the table then you’ll need to make a copy of the table.  You can copy the table in a number of ways but one of the easiest is using a SELECT INTO statement like you can see below.

SELECT * INTO dbo.T_target FROM dbo.T_source;

Next you need to drop the source table and rename the copy. Before dropping the table you need to drop any schema-bound objects. You can find them using the following query.

DECLARE @t nvarchar(255) = N' T_source '
SELECT re.referencing_schema_name, re.referencing_entity_name
FROM sys.dm_sql_referencing_entities (@t, 'OBJECT') AS re JOIN sys.sql_modules m ON re.referencing_id=m.object_id
WHERE re.is_caller_dependent = 0 AND m.is_schema_bound=1;

You can drop the source table and rename the target using the following T-SQL statements.

DROP TABLE T_source;
EXEC SP_RENAME ‘dbo.T_target’, ‘T_source’;

Finally you need to recreate whatever indexes, constraints or triggers the table may have been using before you performed the migration. If you used the Analysis Report Migrate (AMR) to perform your migration you should have table with the suffix of _old where you can see and script the old indexes, constraints and triggers.