current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 5 down vote favorite
2

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.

share|improve this question
    
Can you please check whether backup is consisten by running restore verifyonly from disk='backup_location.bak'. Please add the output of this query in question – Shanky Sep 3 '15 at 7:55
    
I used the GUI option 'verify backup media' on both of the backup files, it succeeded – evictednoise Sep 3 '15 at 7:57
    
Were you restoring database in norecovery mode? and do you have enough space available on your disk for data as well as transaction log? Just wanted to confirm...above...please refer this link which might help solve your problem. blog.sqlauthority.com/2008/07/21/… – MySQL DBA Sep 3 '15 at 8:16
1  
Are you sure the .ldf file is going where you think it is? Have you scripted out the restore to make sure its doing what you expect? – Craig Efrein Sep 3 '15 at 10:15
1  
Sounds like the source database is corrupt. Has anyone run DBCC CHRCKDB against the source server? – mrdenny Sep 4 '15 at 15:15
up vote 0 down vote

TRY... 

USE [master]

RESTORE DATABASE [database_name] FROM  DISK = N'D:\database_name.bak' 

WITH  REPLACE,

MOVE N'database_name' TO N'D:\~\DATA\database_name.mdf',

MOVE N'database_name_index' TO N'D:\~\DATA\database_name_index.ndf',

MOVE N'database_name_log' TO N'D:\~\DATA\database_name_log.ldf',

GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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