Get list of SQL server databases which files have been deleted

My goal is getting list of SQL server databases which files have been deleted. In other words. I attach a db from mount, then close the mount so actually I have the attached db without files.

At first it seemed to be easy. Just pretty easy select:

SELECT
   'DB_NAME' = db.name,
   'FILE_NAME' = mf.name,
   'FILE_TYPE' = mf.type_desc,
   'FILE_PATH' = mf.physical_name
FROM
   sys.databases db
INNER JOIN sys.master_files mf
   ON db.database_id = mf.database_id
WHERE
   --and specific condition here

But it turned out differently. Sql server has almost the same information about a regular database and a database which doesn’t have files. So I had to try something else.

Further I tried to use state of database. And it was quite strange. Unfortunately the following query gives me wrong(or not actual information):

SELECT state 
FROM sys.databases 
WHERE name = N'TestDB'

state
-----
0

And 0 means ONLINE according to this link But actually the database has RECOVERY_PENDING state. It looks like that sql server information about my TestDB us out of date and should be refreshed. But I have no idea how to achieve this. But after executing any of following query this info(db state) is being refreshed:

EXEC sp_helpdb N'TestDB'
ALTER DATABASE N'TestDB' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE N'TestDB'
--etc
--all requests are terminated with the same error

Msg 5120, Level 16, State 101, Line 10
Unable to open the physical file "C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
File activation failure. The physical file name "C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB_log.ldf" may be incorrect.
File activation failure. The physical file name "C:MOUNTb4c059e8-3ba6-425f-9a2a-f1713e7719caTestDB_log-2.ldf" may be incorrect.
Msg 5181, Level 16, State 5, Line 10
Could not restart database "TestDB". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 10
ALTER DATABASE statement failed.

So do you have any idea ? And also I’ve asked the question looks like this here differently.

Answer

Finally, I’ve found what i actually need. I can chech whether the specific file exists or not by sql server:

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

So i just need to execute the function above for each file which i can get by executing for example following query:

SELECT 
    DISTINCT 'FILE_PATH' = physical_name 
FROM sys.master_files

Advertisement

Answer