Where are my files?

Question

The DBA run a file relocation script to move the physical Database file from where it was running to I:.

ALTER DATABASE [cro01] MODIFY FILE ( name=cro01,filename=N'i:\SQLData\cro01.MDF') 

He take the Database offline ad copy the file. But for some strange reason he don't finnish the job correctly. We have now mdf files duplicated and we dont know which one is running. How do I know the "real" running location of the files via a Tsql query? Is it posible? I want to avoid checking manually each disk file location against each sys.master_files entry.

Answer 1

dbatools to the rescue - use Find-DbaOrphanedFile

This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.

By default, it looks for orphaned .mdf, .ldf and .ndf files in the root\data directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.

You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.

e.g. Below command will - Connect to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file.

$cred = get-credential -UserName yourUserThatHasAccessToYourServer -Message "Enter correct Password"
#above will pop up message box to enter the password !
Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred

Answer 2

You can use the following to find the real physical name

exec sp_helpdb <your db name>

-- or
select * from master.sys.master_files
where database_id = db_id('<your db name>')