Post reply

Recover DB without Log file.

  • July 10, 2015 at 1:48 am

    #302229

    Hello Guys,

    As my questions was related to SQL Server 2012 SP1 (64-bit), so i am posting here.

    Recently i got task from Senior DBA, as : Recover a database without LOG file

    Currently Data File is available.

    Log file is also available, but shows size as 0 KB. Means Log file is corrupted.

    Currently existing DB is shown as Offline Mode in SSMS.

    So i got below link:

    http://sqlsomething.blogspot.in/2013/06/recovering-database-with-only-mdf-file.html

    When i tried 1st/2nd solution from above link, i got below message:

    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.

    So i was trying below link's 3rd solution :

    To try that 3rd solution, I had to DELETE/Remove existing DB from SSMS, so i can create New DB with same name.

    When i tried to delete Log file from Command Prompt, using below command, Windows replied that File is corrupted, so it cannot be deleted.

    DEL /F /Q /A C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2012\MSSQL\DATA\MyDB.ldf

    Meanwhile my Senior DBA brought DB in Recovery Pending mode.

    So if i want to go for 3rd solution,then i need to DELETE file, But Windows is not allowing me.

    I also read below link:

    http://www.sqlservercentral.com/Forums/Topic832962-266-1.aspx

    So can you guys help me please...!!!

    Thanks

  • July 10, 2015 at 4:18 am

    #1811540

    got a backup of the database?

    if not, why not?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 13, 2015 at 3:43 am

    #1811895

    Hi,

    Maybe this is a long workaround but, in order to play it safe, would this workaround suitable for your issue?

    1.- Stop SQL Server service.

    2.- Perform a cold copy of the database data files.

    3.- Copy them to a temporary SQL Server instance server.

    4.- Perform a db recovery without log file using the standard procedure.

    5.- Backup recovered DB and restore on the original server using the same name and location or using an alternate name and location.

    Best regards,

  • July 13, 2015 at 4:35 am

    #1811907

    I believe the point that Perry is trying to make is that not much you can try in this case is going to work. About the only thing that's going to work is to restore from a known good backup. If you don't have that, then you're pretty much toast. If the data was important (heh... "IF"... all but true scratch, staging, or sandbox data is important), someone should have been doing full up PIT backups.

    There are companies that specialize in this type of thing if the data is really important. It's going to cost you, though.

    Also, the "Senior" DBA seems like he doesn't want to admit that he doesn't know how to do it either and is likely afraid to admit that it's his fault there isn't a recent viable backup. Beware a possible blame shift there.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply