January 21, 2015 at 2:49 pm
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
January 21, 2015 at 10:15 pm
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.
January 22, 2015 at 8:59 am
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
January 22, 2015 at 9:28 am
Can you post the code you're using for the attach?
January 22, 2015 at 9:54 am
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
January 22, 2015 at 10:27 am
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?
January 22, 2015 at 1:36 pm
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
January 22, 2015 at 2:09 pm
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
January 22, 2015 at 5:00 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply