Sql-server – Can’t Restore Database Because of Rogue Log File

logrestoresql serversql-server-2008-r2

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?

Best Answer

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.

Related Question