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.

rory.ap
  • 143
  • 1
  • 6

1 Answers1

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.

Tara Kizer
  • 5,986
  • 1
  • 17
  • 28