4

I have a strange circumstance where I have a production database and a restored copy as another database on the same SQL Server instance, and both of them are using the same MDF and LDF files (i.e. they have the same "Logical" files in SQL Server Management Studio). From what I've found while researching this, it is possible, but all indications are that they would both be pointing to the same data, a reasonable assumption.

However, in my case, both databases actually contain different data. If I run a select statement on two tables with the same name, one in each database, they return different data. I've double, triple, and quadruple verified this. How is this possible? None of the MS docs or anything I've found have talked about two DBs sharing the same MDF files but having different data.

There are no NDF files involved, and there is one file group in each DB with only one file in each file group.

closed as off-topic by Kin Shah, joanolo, LowlyDBA, mustaccio, Colin 't Hart Jun 28 '17 at 20:39

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Kin Shah, joanolo, LowlyDBA, mustaccio, Colin 't Hart
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 8
    Run this query and show us the output (add a WHERE clause for the 2 databases so you don't have to post everything): select database_id, name, physical_name from master.sys.master_files – Tara Kizer Jun 28 '17 at 15:06
  • Ahh yes, they're pointing at different files. I'm looking at the properties of each DB in SSMS, but they show them as the same (logical) files. I guess you can't see the "actual" files in SSMS? – rory.ap Jun 28 '17 at 15:13
  • Logical File can be same, physical files are always different. – Kin Shah Jun 28 '17 at 15:14
  • For comparison, you can run a similar query on each database: SELECT name, physical_name FROM sys.database_files. I've found a few cases (like if a database is READONLY) where the file might get moved and is out of date at the db level, but correct in MASTER. – BradC Jun 28 '17 at 15:15
  • 4
    Haha, man I feel like an idiot now. It was just a scrollbar issue. Oops! – rory.ap Jun 28 '17 at 15:23
11

It is not possible to have two databases point to the same database files, except in the weird Autoclose case that you linked to, which no one should be using that setting anyway.

Run the below query to view what physical files the databases are pointing to:

SELECT database_id, name, physical_name 
FROM master.sys.master_files

With that information, you should be able to see that the databases are in fact not pointing to the same database files.

You also can see this information in the SSMS GUI. Right click on the database, go to properties and then to Files. Now SCROLL to the right as the physical files aren't visible yet. The combination of Path and File Name will be unique.

  • 1
    Thank you so much for your help. Even though I'm just an idiot :) – rory.ap Jun 28 '17 at 15:46
  • 1
    Nah, you just didn't know where to look. Now you do. – Tara Kizer Jun 28 '17 at 15:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.