[IMOLTP] Detaching/Attaching from one server to another.

So, I was trying to get an in-memory database moved from one server to another (long story, involving IMOLTP melting down on me and resulting in a 2 terabyte log file).

I thought it'd be simple: copy the files over, along with the Filestream folders, and attach.  Nope!  Various errors, including "5(Access is denied.)".

Thanks to Simon


Test:

Create DATABASE demo
go
ALTER DATABASE demo ADD FILEGROUP demo_mod CONTAINS MEMORY_OPTIMIZED_DATA
go
ALTER DATABASE demo ADD FILE (name='demo_mod1', filename='M:\temp\demo_mod1') TO FILEGROUP demo_mod
go
Use Demo

go

  CREATE TABLE dbo.ShoppingCart (   

    ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,  

    UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),   

    CreatedDate DATETIME2 NOT NULL,   

    TotalPrice MONEY  

    ) WITH (MEMORY_OPTIMIZED=ON)   

  GO  

Now take offline, copy the Data file, Log file, and the in-memory folder (demo_mod1).

On the target server, right-click, go to security, and add the SQL Server service user and grant FULL CONTROL.

Now, issue this to mount it.  I just tested, and the commented out line doesn't APPEAR to be needed, but that's because my new location matched; if all the locations match, it will grab the demo_mod1 location, probably from the MDF. So in theory, if all your drive letters match, you only need the two FILENAMES. 

CREATE DATABASE [demo] ON 

( FILENAME = N'M:\Data\demo.mdf' ),

( FILENAME = N'L:\Logs\demo_log.ldf' ),

--FILEGROUP [demo_mod] CONTAINS FILESTREAM DEFAULT

(NAME = N'demo_mod1',FILENAME='M:\Data\demo_mod1') -- this is actually a folder...

FOR ATTACH