Detach/Attach of an In Memory OLTP Database to another server

Question

Detach/Attach of an In Memory OLTP Database to another server

Paresh Motiwala

SSCommitted

Points: 1772
More actions
January 21, 2015 at 2:49 pm

#296803

So I have a use case where we need to backup/restore and detach/attach an InMemory OLTP Database to another/restore server.
When I detached the files and present it to the same server using ( it is missing the _mod files) I get the following error:
Let me know if we can preemptively clear the production server of the inmemory oltp configuration(Image attached).
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'WIN-C45O1PB6UO6'. (Microsoft.SqlServer.Smo)
------------------------------
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'FsFileHeader::Open' on 'C:\TEMP\AdventureWorks2014_mod'.
A file activation error occurred. The physical file name 'C:\TEMP\AdventureWorks2014_mod' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Could not open new database 'AdventureWorks2014_Attached'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 3634)
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

You need to attach the database with all of the checkpoint files (the files in the original mod folder). The in memory oltp tables are recreated from those checkpoint files.

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 2

Hi Andrew

you have a valid point. But how do I mount it to the "original" folder on another server.

So the bottom line is, I have a Virtual Copy Data Management tool.

We can present a copy of the database's files to any server on any drive.

Example: if it is on C drive on one server, I can present it to a M drive of another server using this tool.

The normal databases are available in less than 1 minute, no matter how big.

But in this case the database files are in default location, and the IMOLTP files are in c:\temp.

But these database files cannot be attached from the M Drive, as it keeps looking for c:\temp in the new server.

Thanks, I hope I was clear enough.

Paresh

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 734418

January 22, 2015 at 9:28 am

#1772288

Can you post the code you're using for the attach?

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 4

Hi Steve

Thanks for the response. Here are the steps:

1. The software my company makes takes a snapshot of db, via Quiescing the db.

2. I then present this snapshot as a drive letter M.

3. Run this script for attaching: If you are looking for a fresh beginning, please check it out:

GO

CREATE DATABASE AdventureWorks2014_Restored ON

(FILENAME='M:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.mdf'),

(FILENAME='M:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.ldf')

FOR ATTACH

GO

SET ANSI_PADDING OFF

GO

As you can see, there is nothing to attach or involve the _mod files created by the IMOLTP database; or say that attach without the IMOLTP info.

Cheers

Paresh

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

I suspect you're missing a filegroup somewhere, but I haven't done enough with this yet, especially in backup/restore/attach/detach.

Your software might not handle SQL 2014 correctly. There are new extensions to the filegroups for In-Memory OLTP based on the FILESTREAM extensions. If you look at the current definition of your database (maybe use SSMS to script out the create), there should be another filegroup. I think this is what needs to be moved to a new path.

If you add the c:\temp path in the new server, does it work?

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 6

Thnx again Steve:

So you are right, I moved it all to M drive but the database as defined looks for the IMOLTP files in the original location and that is c:\temp, without that it won't let me reattach the db.

ugh

Paresh

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Paresh Motiwala SSCommitted Points: 1772 More actions · Answer 7

I think I hacked it...

CREATE DATABASE AdventureWorks2014_Restored ON

(FILENAME='z:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.mdf'),

(FILENAME='z:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.ldf'),

(FILENAME='Z:\TEMP\AdventureWorks2014_mod') -- this is actually a folder...

FOR ATTACH

Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 8

that folder is the filegroup. For Filestream and In-Memory OLTP, you provide a folder for the filegroup and not a file. However you must include this in the attach