locked
Log file missing after restore RRS feed

  • Question

  • Hi,

    Yesterday we migrated one DB from 2008 R2 to 2016. We did backup and restored.

    after an hour while we validaiton data at destination we found that some data missing. When we compare with 2008 R2 the data is there in 2008 R2.

    Now what to do to fix this issue. Please help us.

    Wednesday, January 9, 2019 11:54 PM

Answers

  • Yes, but please remember Vijay that if you are not going to do a point-in-time restore (to the last log backup) you have potentially lost data and MUST get the business to sign off on this. Otherwise you should start again and kick off the restore WITH NORECOVERY.

    It is always good practice to ALWAYS specify NORECOVERY which will mean when you are absolutely finished you can issue a RESTORE DATABASE dbname WITH RECOVERY command to bring it online (note you do not need to specify a backup file to do that).


    Regards,
    Mark Broadbent.
    Microsoft Certified Master | Microsoft Data Platform MVP
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it has been helpful.

    Interested in attending a free training? Why not attend SQLSaturday Cambridge and SharePoint Saturday Cambridge? And why not check out my Ultimate Training Resources Page?

    • Marked as answer by VijayKSQL Wednesday, January 30, 2019 11:32 PM
    Monday, January 14, 2019 11:36 AM

All replies

  • Hi VijayKSQL,

    Could you please share more information about your question? What kind of data is missing? And how do you perform the backup and restore? Please also check if you have restored all the backup files it needs.

    The backup and restore operations either work or not. Check the following thread.
    Missing chuncks of data from backup files

    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 10, 2019 6:24 AM
  • If the log file is missing then you can run the following:

    CREATE DATABASE TestDB ON
    (FILENAME = C:\Program Files\Microsoft SQL Server\..\MSSQL\DATA\TestDB.mdf’)
    For ATTACH_REBUILD_LOG
    GO

    This is the answer to your title "Log file is missing"

    Now, comes to the main point: As data is missing so, Run DBCC CHECKDB command and check the output. 

    Thursday, January 10, 2019 6:31 AM
  • Let me explain:

    we have to restore 

    Full backup file+20 Logfile (trn)

    While restoring at destination i only restored till 19th logfile and brought db online. After some time we realized that we missed 20th file.

    As this is a QA we can delete and restore again. But due to size in DB  it takes lot of time.

    I knew that, it is not possible to restore no more file when the DB online.

    In this cases how can we fix the issue?




    • Edited by VijayKSQL Thursday, January 10, 2019 8:25 PM add
    Thursday, January 10, 2019 7:09 PM
  • which option you are using to restore the database?

    Restore with Recovery | Restore with NoRecovery

    Friday, January 11, 2019 3:58 AM
  • That's simple and you don't have any other option than restoring whole backups again
    • Proposed as answer by Tom Phillips Monday, January 14, 2019 12:42 PM
    Friday, January 11, 2019 5:02 AM
  • Hi VijayKSQL,

    If you have put the database online, no additional transaction logs can be restored. You will have to do the migration or restore again. It is painful but it will guarantee data integrity. There are many tools and solution you can do this. 

    For migration from SQL Server 2008 R2 to SQL Server 2016, you can use the Data Migration Assistant (DMA). The tool can also help you detecting compatibility that can impact database functionality in your new version of SQL Server. For more information, please refer to Overview of Data Migration Assistant

    Another option is using SSIS. With SSIS, there is no need to create multiple backup files. You can create a SSIS package and load data into your new SQL Server instance. Please refer to Load data into SQL Server or Azure SQL Database with SQL Server Integration Services (SSIS).

    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Puzzle_Chen Friday, January 11, 2019 7:48 AM
    • Proposed as answer by Tom Phillips Monday, January 14, 2019 12:43 PM
    Friday, January 11, 2019 7:43 AM
  • Yes, but please remember Vijay that if you are not going to do a point-in-time restore (to the last log backup) you have potentially lost data and MUST get the business to sign off on this. Otherwise you should start again and kick off the restore WITH NORECOVERY.

    It is always good practice to ALWAYS specify NORECOVERY which will mean when you are absolutely finished you can issue a RESTORE DATABASE dbname WITH RECOVERY command to bring it online (note you do not need to specify a backup file to do that).


    Regards,
    Mark Broadbent.
    Microsoft Certified Master | Microsoft Data Platform MVP
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it has been helpful.

    Interested in attending a free training? Why not attend SQLSaturday Cambridge and SharePoint Saturday Cambridge? And why not check out my Ultimate Training Resources Page?

    • Marked as answer by VijayKSQL Wednesday, January 30, 2019 11:32 PM
    Monday, January 14, 2019 11:36 AM