Sql-server – Can’t restore SQL Server db from full backup, log processing fails, database in ‘restoring’ state

restoresql serversql server 2014transaction-log

I am trying to set up a database for development purposes on my PC's local SQL Server Developer Edition 12.0.2000.8. I've got a full database backup and separate transaction-log-only backup files available which were sent to me over the network.

When trying to restore from full backup, after some time (~1 hour maybe, the database is ~270 GB in size), I'm getting an error :

System.Data.SqlClient.SqlError: An error occurred while processing the
log for database 'database name'. If possible, restore from backup.
If a backup is not available, it might be necessary to rebuild the
log. (Microsoft.SqlServer.SmoExtended)

After this, the db is in 'Restoring..' state.

I wanted to run something like (got it from this question)

ALTER DATABASE recovery_test_2 SET EMERGENCY;
ALTER DATABASE recovery_test_2 SET SINGLE_USER;

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

against it, but naturally I can't as the database is in 'Restoring.." state.
Restarting the restore process on it leads to the same error message, dropping and restoring again didn't help too.

How do I get the db up and working? Transactional consistency doesn't matter to me.

The SSMS auto-generated restore script : 

  USE [master]
  RESTORE DATABASE [database_name] FROM  DISK = N'D:\database_name.bak' WITH  FILE = 1,
  MOVE N'database_name' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name.mdf',
  MOVE N'database_name_index' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name_index.ndf',
  MOVE N'database_name_log' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name_log.ldf',
  NOUNLOAD,
  STATS = 5

  GO

The result of query suggested by @Craig Efrein

The log cannot be rebuilt because there were open transactions/users
when the database was shutdown, no checkpoint occurred to the
database, or the database was read-only. This error could occur if
the transaction log file was manually deleted or lost due to a
hardware or environment failure.

Best Answer

From the comments moved to chat, this note from the OP:

I just tried attaching it via GUI. As I selected the .mdf file, the SSMS identified the database consisted of 3 files (data, index, log), but somehow even though I was restoring WITH MOVE, the database details about file path were saying it wrong! So I just pointed them at the old log/data/index file and... The database is online.

Related Question