Can't Restore Database Because of Rogue Log File

Question

Someone, somehow, made a database disappear from SQL Server 2008 R2. I'm assuming it was dropped, because it doesn't show in SSMS in the Object Explorer's list of databases. Let's call the database "Rogue". Now, there is a rogue log file, "Rogue_log.ldf" on the server's file system. The file cannot be deleted via Explorer.

Here is the restore script. Note that I tried to run it without "MOVE" first, and then got the error further below in this post. Yes, it's "Logs" and not "Log" in the path (don't ask).

RESTORE DATABASE Rogue
FROM DISK = 'E:\Rogue.bak'
WITH MOVE 'Rogue' TO 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Rogue.mdf', 
MOVE 'Rogue_log' TO 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf',
RECOVERY, REPLACE
GO

Trying to run the script is giving the following error:

Msg 3634, Level 16, State 1, Line 1 The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf'. Msg 3156, Level 16, State 8, Line 1 File 'RGA_log' cannot be restored to 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 1 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I can restore a backup from Rogue.bak if I change the log file name to Rogue1_log.ldf (Star Wars puns NOT intended.) Can anyone help me figure out what's going on with this rogue Rogue_log.ldf on the server file system?

Answer 1

I'd bet the database didn't actually get dropped, and is still in use by SQL.

Not sure why it isn't showing up in SSMS, but run the following query to see what database is still using it:

SELECT database_id, DB_NAME(database_id) AS DBName, name AS LogicalName, type_desc,
physical_name, convert(numeric(10,1),(size/128.0/1024.0)) SizeGB
FROM master.sys.master_files
ORDER BY database_id, type_desc desc, name

Is your Rogue database in the list?

If you just do SELECT * FROM master.sys.databases, does it show Rogue? What does it show as the user_access_desc and state_desc?

What does the SQL errorlog say during the time in question? It should mention a database drop or change in status.

Answer 2

I would try

1. Restart the sql service and see whether the db re-appears in SSMS. If yes, just drop it using drop database
2. If step 1 does not work, stop the sql service and then delete the log file via File Explorer (better rename it for backup purpose), then restart the sql service, and try to do the restore again.

I once encountered a similar issue, and we ended by stopping the sql service and then copy both the data and log file to a similar sql instance and then attach them back to a new db and then using dbcc to fix some data corruption issue (with data loss) and then continue our work (it was a non-prod db and we did not have any backup)