3

Good morning, everyone!

I am using SQL SERVER 2008, and a needed to use the database of our client. When I was going to do a backup in case something went wrong I misclicked to do a restore and didn't notice that I was in the restore screen and clicked the "OK" button (without changing any configurantion on the screen). Right when it began doing I hit the "Stop Action Now". It stoped and a message appears saying that the user had canceled the action. So far so good, but when I was going to do the backup for real the SQL didn't allow saying that already had a restore going on. When I saw the database state was "Restoring". I searched on internet and saw that the query:

DATABASE NomeDatabase WITH RECOVERY

would resolve my problem but before I execute I asked my boss if there was any problem executing this query and he said to don't run the query because it would restore to the most recent backup (and the most recent backup was on 08/23). So we only have the MDF e LDF files, and when we try to do an attach with this query

EXEC sp_attach_db @dbname = N'DBName', 
    @filename1 = N'C:\PATH\DBName.mdf', 
    @filename2 = N'C:\PATH\DBName_log.ldf'

it gives this answer:

Msg 1813, Level 16, State 2, Line 1 Could not open new database 'DBName'. CREATE DATABASE is aborted. Msg 9004, Level 21, State 1, Line 1 An error occurred while processing the log for database 'DBName'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

I also search this error message and saw that is necessary rebuild the log but nobody here knows how to do it. I would like to know if you could help me or if anyone already had been through that.

Here is the screen that I did the restore, just clicked the "ok" not changing any configuration: Image

(My boss detached and deleted the database to try to do the attachment query, so we don't have the DB on the SQL anymore only MDF and LDF files)

I saw some posts on the site but none of them worked with me.

Thanks and sorry if there is any gramatical mistake!


Thanks for the answer. I also tried this query, but got no lucky.

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.

Message 1813: Could not open new database 'dbname'. CREATE DATABASE is aborted.

So I guess there is nothing to do but restore the August 23rd now? All the things done after it are completely lost, that's it?

10

Use sp_attach_single_file_db to attach the MDF file without the LDF file since sp_attach_db didn't work. This will likely fail too though as the restore had already kicked in and overwrote those files (check the SQL Server error log to see if the restore started). If it does fail, you are stuck with restoring from the August 23rd backup.

Get the backups in order after you are done with this emergency.

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.