asked on

How do I attach MS SQL Server 2008R2 database files to an MS SQL Server 2017?

I have experienced a hard drive crash while running MS SQL Server 2008R2. My database files were not damaged. I installed MS SQL Server 2017 Standard on a new machine and now would like to attach the 2008R2 mdf and ldf files to the new machine.

When I try to do so I get an error. The message says to click the link to get a description of the problem, but clicking on the link does not do anything.

I believe the error is being caused by the fact that the databases were never detached... I simply copied them to the new machine..

Now I can't attach the files to the new machine. Can someone tell me what to do to attach these files? Thanks

ASKER CERTIFIED SOLUTION

E C

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hello There

Another way might be restoring the database from backup and attaching it to a new server. This will work.

ste5an

See Attach a Database:

Prerequisites

•The database must first be detached. Attempting to attach a database that has not been detached will return an error. For more information, see Detach a Database.

pcalabria

ASKER

I ran the code provided by EC and received an operation successful message.
Next, I attempted to attach... right click database.. attach.. add and received the message:

An exception occurred while executing a Transact-SQL statement or batch.

CREATE FILE encountered operation system error 5 (Access is denied) while attempting to open or create the physical file '<myfilename>'

Restoring from a backup is not an option. Does this message mean I've hit a dead end?

E C

Access Denied sounds like you need to add permissions to the folder where it's trying to create the database. Have you checked that?

pcalabria

ASKER

when I looked at the error details the message indicates the file was already open in SSMS which it was not... I may have done something wrong so I’m trying to duplicate the results now... I should be able to report back within 20 minutes.

pcalabria

ASKER

Still having a problem.
I recopied the original mdf and ldf files to a new directory and then ran the code provided by E C.

USE master; GO CREATE DATABASE POC ON (FILENAME = 'C:\Path-to-mdf-file\MyDB.mdf'),   (FILENAME = 'C:\Path-to-log-file\MyDB_Log.ldf') FOR ATTACH; GO

Open in new window

Immediately afterwards I tried to attach the database but got a message that the operation can not be completed because the file (My.mdf) is currently in use.

Do I need to run some code to close the files after running the code snippet above?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\Data\MySQLfile.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Open in new window

SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pcalabria

ASKER

Thanks everyone. The create database code worked for me... but I was thrown by the fact that the screen did not refresh and thought the operation failed.. Thank you everyone!