none
Restore database with last tail log transaction

    Question

  • Hi Our production DB was on "Suspect" mode and try  following action and still no luck.

    DBCC CHECKDB('dbname')

    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS);

    Now plan is to restore FULL backup, recover using last log backup which was taken 3.00pm. This incident was happen 9.00pm and no log backup can found. Therefore I need to recover the DB using tail log. Now as I cant access the DB for get tail log backup, how to recover up to last transaction. Any help will be greatly appreciated .

    Many Thanks

       
    Wednesday, May 9, 2018 11:23 PM

All replies

  • hi there,

    take the database offline and copy the mdf and ldf files to some other server and try to attach to the database.

    and run the consistency check to find out the damage or corruption. fix the correction then you will have a good database. 

    back up this and restore back to your production server.

    it is not as easy as advised.

    good luck

    Kumar

    Thursday, May 10, 2018 12:27 AM
  • Hi Kumar

    Thank you for your advice. Its a huge DB . No space can accommodate  in any where else. I am trying to restore the recent full backup and recent log backup then recover. My issue is how to get recent tail log backup to apply to recovery until fail time.

     Thanks

    Thursday, May 10, 2018 1:52 AM
  • >>>My issue is how to get recent tail log backup to apply to recovery until fail time.

    Run 

    restore headeronly from disk='logfilebackuplocation\log.bak'

    Take a look at Bsackup Finishe Date column


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 10, 2018 5:00 AM
    Moderator
  • hi there,

    take the database offline and copy the mdf and ldf files to some other server and try to attach to the database.

    Oh God that would be blunder, never never detach a suspect database you would loose all possibility to recover it. You might take backup with continue_after_error if that is possible but detaching is worst thing you can do.

    Ashwan for tail log backup to happen the transaction log should be intact did you tried taking tail log backup ? If that is not possible the transaction log may be corrupt and you would loose some data.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, May 10, 2018 6:15 AM
    Moderator
  • hi there,

    take the database offline and copy the mdf and ldf files to some other server and try to attach to the database.

    Oh God that would be blunder, never never detach a suspect database you would loose all possibility to recover it. You might take backup with continue_after_error if that is possible but detaching is worst thing you can do.

    Ashwan for tail log backup to happen the transaction log should be intact did you tried taking tail log backup ? If that is not possible the transaction log may be corrupt and you would loose some data.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Hi shanky,

    you do not know where the corruption is at this stage. how do you secure your mdf and ldf file with out copying to a safe location.

    unfortunately aswhwan do not have a place to work separately away from his production database.

    "Ashwan for tail log backup to happen the transaction log should be intact did you tried taking tail log backup ? If that is not possible the transaction log may be corrupt and you would loose some data."

    Ashwan already mentioned that he cannot access the database. will you advise give away the data.

    look his scenario: he already executed the below command.

    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    that means uncommitted transaction in the log have rolled back. now the log contains only committed transaction. no data should be lost in his case.

    every one have different approaches to solve the issue. but the final output should be no data loss by fixing the corruption.

    thanks

    kumar


    Friday, May 11, 2018 12:59 AM
  • I could only sense you are trying to prove your point but again that is wrong. Aswan did not said he does not have spare server, did he ? You can also try to backup the same database with continue_after_error clause but it succeeds only in rare cases. Ashwan did not did correct thing by immediately putting database in single user mode and running repair_allow_data_loss I am sure he is not aware what a evil command that is.

    No matter how deep and small is corruption you never detach database, have you ever tried attaching a corrupt database in 99% cases you would not be able to only in few cases and subject to condition you are expert like you may be able to. Just because he is not able to access DB you would suggest to detach it and move it to different server and attach ? I bet he wont be able to.

    The scenario as I think here would be 

    1. Recover database with whatever backup you have at least you have database with some amount of data loss, considering OP was saying he cannot access database at all

    2. Put database in emergency mode, look out for additional data in tables which were updated after 3:00 PM try to extract as much data as possible.

    There might be other scenarios to extract data but detach would not be recommended by anyone.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, May 11, 2018 6:44 AM
    Moderator
  • Hi Shashank , Kumar, Uri

    Thank you all for the comment.

    Shashank : On you last comment said, "Ashwan did not did correct thing by immediately putting database in single user mode and running repair_allow_data_loss I am sure he is not aware what a evil command that is." I already done as I commented.  Are you expecting different command  which I didn't executed ?

    Hi When DB is on Emergency mode I able to get backup  on single user mode

    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    BACKUP LOG myDatabase TO myDatabase_FullRM_log1.trn
    WITH norecovery;

    I am not sure will that back has all last transactions which committed ?(Tail log backup) to restore and open.

    Thanks

    Sunday, May 13, 2018 10:49 PM

  • Hi When DB is on Emergency mode I able to get backup  on single user mode

    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    BACKUP LOG myDatabase TO myDatabase_FullRM_log1.trn
    WITH norecovery;

    I am not sure will that back has all last transactions which committed ?(Tail log backup) to restore and open.

    Thanks

    Is this really in emergency mode or single user mode, AFAIK backup database is not possible in emergency mode. I am not sure though but if backup is possible the restore should work but again with countine_after_error. The best thing here is to try this on your own. Please note above backup which you took might still have some corruption so after you restore database completely please run checkdb again.

    Other thins as I told you is in emergency mode you can actually access the database, script  its structure and move data out. Since you want least amount of data loss why not just script out whole structure and move data in this way you can salvage lot of things.

    Please also see Paul Randal's Emergency Mode repair


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Monday, May 14, 2018 6:48 AM
    Moderator
  • In most cases you can produce a log backup for a broken database, you just need to know how to do that. You use the NO_TRUNCATE option of the BACKUP LOG commands:

    BACKUP LOG dbname TO DISK = 'C:\x.trn'

    Now, if the problem is with the ldf file, somehow, you are in for more troubles. But above is the first thing you should try. Some more thoughts here: http://sqlblog.com/blogs/tibor_karaszi/archive/2010/03/27/restore-database-to-the-point-of-disaster.aspx


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, May 14, 2018 2:51 PM