Attach an In-Memory Database With T-SQL: Include the Container

To attach an In-Memory database with T-SQL, use CREATE DATABASE with FOR ATTACH and list the files of the database. The list holds the data file and the log file. It also holds the container folder when that folder has moved.

What Makes the Database Different

A database with memory-optimized tables has one more storage object. Besides the data file and the log file, it has a container folder. SQL Server keeps the checkpoint files of the memory-optimized tables there. The folder is not a single file. Still, sys.database_files lists it as the third file of the database, with the type FILESTREAM.

That third object explains why an attach statement needs care. When the folder moves, the attach statement has to say where the new folder is.

Build the Demo Database

The demo database is named InMemAttachDemo, so run the script on a test server. The first script builds the paths from the default data folder of your instance. It creates the database with a memory-optimized filegroup. The edition has to support the feature, and every edition since SQL Server 2016 SP1 does.

IF DB_ID(N'InMemAttachDemo') IS NULL
BEGIN
    DECLARE @data nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
    DECLARE @sql nvarchar(max) = N'CREATE DATABASE InMemAttachDemo ON PRIMARY
        (NAME = InMemAttachDemo_data, FILENAME = N''' + @data + N'InMemAttachDemo.mdf''),
        FILEGROUP InMemAttachDemo_mod CONTAINS MEMORY_OPTIMIZED_DATA
        (NAME = InMemAttachDemo_mod, FILENAME = N''' + @data + N'InMemAttachDemo_mod'')
        LOG ON (NAME = InMemAttachDemo_log, FILENAME = N''' + @data + N'InMemAttachDemo_log.ldf'');';
    EXEC (@sql);
END;

The second script creates two memory-optimized tables. Shipments keeps its data on disk, and ScanBuffer keeps only its structure. Each table gets rows. The last query lists the three files of the database.

USE InMemAttachDemo;
GO
DROP TABLE IF EXISTS dbo.Shipments, dbo.ScanBuffer;
CREATE TABLE dbo.Shipments (ShipmentID int NOT NULL PRIMARY KEY NONCLUSTERED, Label nvarchar(40) NOT NULL)
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
CREATE TABLE dbo.ScanBuffer (ScanID int NOT NULL PRIMARY KEY NONCLUSTERED, Label nvarchar(40) NOT NULL)
    WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
INSERT dbo.Shipments VALUES (1, N'Tea crate'), (2, N'Rice sacks'), (3, N'Herb pots');
INSERT dbo.ScanBuffer VALUES (1, N'Pending scan'), (2, N'Pending scan');
SELECT name, type_desc FROM sys.database_files ORDER BY file_id;

SELECT (SELECT COUNT(*) FROM InMemAttachDemo.dbo.Shipments) AS DurableRows,
       (SELECT COUNT(*) FROM InMemAttachDemo.dbo.ScanBuffer) AS SchemaOnlyRows;

Detach the Database

A detach needs a database that no session uses. Switch to master first, because your own session counts as a user. The procedure sp_detach_db removes the database from the instance and leaves its files on disk. The query that follows proves the database is gone.

USE master;
GO
EXEC sp_detach_db N'InMemAttachDemo';
GO
SELECT DB_ID(N'InMemAttachDemo') AS DatabaseIdAfterDetach;

Attach With the Data and Log Files

Now attach it. The statement is dynamic only because the path comes from SERVERPROPERTY. In your own script, type the paths. To attach an In-Memory database from its own folder, list two files first, the data file and the log file.

DECLARE @data nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @sql nvarchar(max) = N'CREATE DATABASE InMemAttachDemo ON
    (FILENAME = N''' + @data + N'InMemAttachDemo.mdf''),
    (FILENAME = N''' + @data + N'InMemAttachDemo_log.ldf'')
    FOR ATTACH;';
EXEC (@sql);

SELECT (SELECT COUNT(*) FROM InMemAttachDemo.dbo.Shipments) AS DurableRows,
       (SELECT COUNT(*) FROM InMemAttachDemo.dbo.ScanBuffer) AS SchemaOnlyRows;

The attach worked without a word about the container, because the data file remembers where the folder was. The folder had not moved, so SQL Server found it. The durable table kept its 3 rows. The schema-only table kept its structure and lost its 2 rows, which is exactly what that durability option promises.

Attach With the Container Folder

Move the files to a new folder or a new server, and the data file remembers the wrong place. Then the third FILENAME matters. It points the attach at the new location of the folder. Copy the folder together with the data and log files, because the checkpoint files live inside it.

The next script detaches the database again and attaches it with all three names. The container sits at its old location here, so the statement proves the three name form works and nothing more. A real move changes the three paths.

USE master;
GO
EXEC sp_detach_db N'InMemAttachDemo';

DECLARE @data nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @sql nvarchar(max) = N'CREATE DATABASE InMemAttachDemo ON
    (FILENAME = N''' + @data + N'InMemAttachDemo.mdf''),
    (FILENAME = N''' + @data + N'InMemAttachDemo_log.ldf''),
    (FILENAME = N''' + @data + N'InMemAttachDemo_mod'')
    FOR ATTACH;';
EXEC (@sql);

SELECT (SELECT COUNT(*) FROM InMemAttachDemo.dbo.Shipments) AS DurableRows,
       (SELECT COUNT(*) FROM InMemAttachDemo.dbo.ScanBuffer) AS SchemaOnlyRows;

The result is the same. Attach also loads the memory-optimized data back into memory. A large database takes longer to come online than the demo. Every durable row is read from the checkpoint files.

When the Attach Fails

When you attach an In-Memory database, three messages cover the failures that a move causes. Msg 5120, Unable to open the physical file, arrives with State 101 when a file is missing or unreadable. Msg 1802, CREATE DATABASE failed, follows with State 7.

Msg 5133, a directory lookup failure, arrives alone with State 1 when the folder in a path does not exist. The operating system error at the end of the first message tells you the cause. No database is created, so fix the cause and run the statement again.

For example, a FILENAME of D:\NoSuchFolder\InMemAttachDemo.mdf gives this message:

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "D:\NoSuchFolder\InMemAttachDemo.mdf" failed with the operating system error 2(The system cannot find the file specified.).

Files copied by a person get the permissions of the new folder, not the permissions of the old one. That is why error 5 shows up after a move. The service account name is in sys.dm_server_services.

You could argue that backup and restore is the safer way to move a database. For production data, it is. A backup is checked with a verify step and leaves the source database online. A detach takes the database offline until the attach finishes. Use detach and attach for planned moves and test copies, and keep a backup before you start.

What to Remember

A memory-optimized database has three storage objects, and the container folder is the one people forget. Detach with sp_detach_db from master. To attach an In-Memory database, run CREATE DATABASE … FOR ATTACH and add the container folder as a third FILENAME when it moved. Expect durable rows to return and schema-only rows to be gone.

When you finish testing, remove the example database. The drop also removes its container folder.

USE master;
GO
IF DB_ID(N'InMemAttachDemo') IS NOT NULL
BEGIN
    ALTER DATABASE InMemAttachDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE InMemAttachDemo;
END;

A database is not one file, it is every place its data lives.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.