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?
master.sys.master_files? Could it be attached to a different instance?