2

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?

  • Did the account you used to try to delete the leftover log file have sufficient permission to do so? – Forrest Jul 24 '17 at 19:35
  • 2
    Run Process Explorer to see what is using that file. – Tara Kizer Jul 24 '17 at 19:42
  • Did you put a different location for log file when tried with move? – SqlWorldWide Jul 24 '17 at 19:46
  • 1
    If rogue_log.ldf doesn't work but rogue1_log.ldf works, then it's almost certain that there's already a file in that folder named rogue_log.ldf. Try moving it first (and if moving it fails, then you have two similar databases and should decide for yourself which one should be able to have a log file with that name). REPLACE only works if that file is currently attached and belongs to the same database. Does anything show up with that name in master.sys.master_files? Could it be attached to a different instance? – Aaron Bertrand Jul 24 '17 at 21:20
  • Are you certain SSMS is connected to the server using an account that is a member of the sysadmin group? Perhaps the account simply doesn't have access to "see" the database? – Max Vernon Jul 24 '17 at 23:22
2

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.

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)

  • Did both. Even with the services stopped, I can't delete it from the file system, even though I can create files in that location, etc (I have local admin). – HardCode Jul 26 '17 at 15:02
  • If you cannot delete it after sql service stops, can it be probably your permission issue for that folder, i.e. you can create new files but cannot delete any existing file? Can you ask your system admin people to do it? or can you rename it? – jyao Jul 26 '17 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.