<div>
One database file can be attached to only one instance at a time so you would have to either detach MyDB_Copy from &nbsp;InstanceA then copy MyDB.mdf and ldf (that's the SQL T-Log) to F:\Program Files\Microsoft SQL Server\MSSQL13.Instance<strong>B</strong>\MSSQL\DATA &nbsp;assuming that's where InstanceB had the data folder configured under server properties OR to try get another copy of "MyDB.mdf and MyDB_log.ldf " from Unitrends backups to whatever default data folder location that is set for the InstanceB and try to attach it from there. <br>Long story short is that one MDF file cannot be attached to more than one SQL instance at any given time.
</div>


<div>
lcohan,<br>Thanks for your reply, I do know a .mdf file cannot to attached twice but I wasn't clear. MyDB.mdf that is on the backup appliance is not attached to InstanceA when I am trying to attach it to InstanceB. It does not even appear in the file when I browse to it. Neither do the other 10 or so mdf files that are in that backup.<br><br>ADDED this after answer was given: It was permissions on the folder. See my later comment.<br>&nbsp;
</div>


Accountant

loneieagle2

<div>
<blockquote>Neither do the other 10 or so mdf files that are in that backup.</blockquote><br>Is that "backup" folder on the SQL Server host/instance? I mean you can check what is the default database folder under InstanceB SQL Server properties or running a SQL command like below then copy the MDF files from wherever that "backup" location is into that folder then try to attach it from there. This way you won't have to deal with any permissions issues like Nakul mentioned above because SQL would have the permissions to its own set folders during the install.<br><pre><code class="code-1 nolinks" id="code-20-43295158-1"><span>SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath,</span>
<span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath;</span>
</code></pre>
</div>


<div>
Nakul Vachhrajani,<br>I thought about that, also, but it is the same credentials.<br>Here is what tells that it is the same.<br>I connect to InstanceA in SSMS as sa. I try to attach a DB on the backup device. If I &nbsp;browse to InstanceA &gt; MSSQL &gt; DATA I see all of the mdf files. If I browse to InstanceB &gt; MSSQL &gt; DATA, there are no files in the folder.<br>If I connect to InstanceB in SSMS as sa (same SSM session, just a different connection) and browse to InstanceB &gt; MSSQL &gt; DATA I see all of the mdf files. If I browse to InstanceA &gt; MSSQL &gt; DATA, there are no files in the folder.<br>So sa can see both InstanceA and InstanceB, but only when I am connected to the respective Instance. I can see every other file in the backup just not ,mdf files that were in a different Instance when backed up.<br>I see that this happens on the local drive, also. When I try to attach an mdf file, you can only see the mdf files for that instance. So the mdf seems to have the instance stored inside the file and SSMS will only allow it to be attached to the same instance. Is that correct? If it is, then it seems the only way to accomplish this is to actually restore the backup to a different location which is the instance I want. That is what I am trying to avoid, restoring a 200GB database to a server that is short on disk space.<br><br>ADDED this after answer was given: It was permissions on the folder. See my later comment <br><br>
</div>


<div>
lcohan,<br>That is what I am trying to avoid, making a new copy of the DB on the server. The server doesn't have the disk space to hold another copy of the DB. The backup is on a Unitrends appliance that is connected to the SQL server through disk management and looks just like a local drive F:<br>So it looks like there is no way to connect a mdf to a different Instance than where it was originally.<br>My reason for wanting to do it this way, is I want to be able to have a user access this "historical" data without giving them any access to the Production environment.<br>So unless someone knows a way to do this, I'll have to go to plan B. It just seemed like this would be so easy.
</div>


<div>
Nakul Vachhrajani,<br><br>You are correct. Just took a while to get it through my thick (old) skull.<br>The agent runs under MSSQL$InstanceA and MSSQL$InstanceB.<br>Those users have rights to their respective Instance folders including the DATA folder.<br>The reason I could see everything else in the MSSQL13.InstanceA was through another permission which is not inherited by the DATA folder.<br><br>In the DATA folder that MSSQL$InstanceA has access, I don't see how to add MSSQL$InstanceB. It can not find that user either locally or in the domain (even including service accounts).<br><br>This solves my problem to a certain extent, I just have to figure out what permissions to use.<br><br>Thanks for being patient with me.<br><br><br>
</div>


<div>
<div class="content wysiwyg-content fr-view">
I have a SQL Server 2016 installation with 2 named instances, let's say InstanceA and InstanceB. I make a file level backup of InstanceA on a Unitrends backup appliance which includes MyDB.mdf..<br>I can connect to the backup on the Unitrends appliance as a disk through disk management on the Windows server that SQL server is installed on.<br>In SSMS, I can attach the MyDB.mdf to InstanceA as MyDB_Copy and it works exactly as expected; I can access the data that resides on the backup appliance.<br>What I can't do is attach MyDB.mdf to InstanceB because it does not show up when I browse to mdf file in SSMS while attaching when connected to InstanceB. I can browse to the folder F:\Program Files\Microsoft SQL Server\MSSQL13.InstanceA\MSSQL\DATA, but it doesn't display the files in that folder.<br>Is there a way to do this?<br><br>&nbsp;&nbsp;
</div>
</div>



I have a SQL Server 2016 installation with 2 named instances, let's say InstanceA and InstanceB. I make a file level backup of InstanceA on a Unitrends backup appliance which includes MyDB.mdf..
I can connect to the backup on the Unitrends appliance as a disk through disk management on the Windows server that SQL server is installed on.
In SSMS, I can attach the MyDB.mdf to InstanceA as MyDB_Copy and it works exactly as expected; I can access the data that resides on the backup appliance.
What I can't do is attach MyDB.mdf to InstanceB because it does not show up when I browse to mdf file in SSMS while attaching when connected to InstanceB. I can browse to the folder F:\Program Files\Microsoft SQL Server\MSSQL13.InstanceA\MSSQL\DATA, but it doesn't display the files in that folder.
Is there a way to do this?
  

How do I attach a copy of a SQL mdf file to a different instance?

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.