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.
