-2

There is an article here that I tried following:

https://stackoverflow.com/a/52325761/22019322

It includes these steps:

ALTER DATABASE [DBName] SET EMERGENCY;
GO

ALTER DATABASE [DBName] set single_user
GO

DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO 

ALTER DATABASE [DBName] set multi_user
GO

When I get to this step:

DBCC CHECKDB ([LocalDatabaseName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO

I get the following error:

Msg 1853, Level 16, State 1, Line 7 The logical database file 'LocalDatabaseName_log' cannot be found. Specify the full path for the file. Msg 5123, Level 16, State 1, Line 7 CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'E:\Logs\MSSQLSERVER\LocalDatabaseName_Primary.ldf'. Msg 5024, Level 16, State 2, Line 7 No entry found for the primary log file in sysfiles1. Could not rebuild the log. Msg 5028, Level 16, State 2, Line 7 The system could not activate enough of the database to rebuild the log. DBCC results for 'LocalDatabaseName'. CHECKDB found 0 allocation errors and 0 consistency errors in database 'LocalDatabaseName'. Msg 7909, Level 20, State 1, Line 7 The emergency-mode repair failed. You must restore from backup.

This part of the error:

while attempting to open or create the physical file 'E:\Logs\MSSQLSERVER\LocalDatabaseName_Primary.ldf'

That troubles me because I tell it to import the database to my local hard drive:

screenshot

The log file exists on my hard drive, and I have a 1TB hard drive with about 700GB of free space, so space isn't an issue. Why does it keep trying to go to the mapped drive? (that drive isn't mapped on my PC - that's from the database admin who creates the backups for us)

Several times I have restored the database from a backup that I have, and this enables the SQL Server to run fine for about an hour or so, but then it reverts back to "Recovery Pending" and I cannot access the database.

Dale K
  • 25,246
  • 15
  • 42
  • 71
podrick
  • 35
  • 6
  • 1
    You seem to have a splinter of things going on here, crossing between troubleshooting an existing database, to restoring a database, to attaching files that already exist. I would strongly recommend writing a manual `RESTORE DATABASE dbname FROM DISK = 'path_to.bak' WITH REPLACE, RECOVERY, MOVE 'filename' TO 'C:\...\path.mdf', etc.` instead of pointing and clicking your way through that UI which is quite possibly 99% of the problem. – Aaron Bertrand Aug 30 '23 at 23:17
  • You say you have a BAK file already, what does `RESTORE FILELISTONLY FROM DISK = 'path_to.bak';` yield? What about `RESTORE HEADERONLY...`? I would also suggest trying to restore it as a different name, because from the description of "it runs fine for an hour" it sounds like something else is swooping in and trying to restore the database on top of the one you manually restored. – Aaron Bertrand Aug 30 '23 at 23:18

0 Answers0