Briefly about the reasons - problems with the repository led to the loss of the database file. Everything.

Checked - mdf database file - no. Log ldf is, model - full. There is a full backup. Almost fresh, from 09.2016 but having a log from 10/14/2016, I would like a fresh one.

What I tried to do myself:

  1. Created a backup of the preserved log: Created a KA database on another server, ALTER DATABASE [KA] SET OFFLINE . deleted files, uploaded your ka_log.ldf ALTER DATABASE [KA] SET ONLINE , got your Unable to open the physical file mdf, etc. made BACKUP LOG [ka] TO DISK = N'D:\Tail.bak' WITH NO_TRUNCATE;

During the backup, it was clear that the log also got at the time of the host crash - there was a BACKUP WITH CONTINUE_AFTER_ERROR успешно сформировал резервную копию поврежденной базы данных. Дополнительные сведения об обнаруженных ошибках см. в журнале ошибок SQL Server. message that BACKUP WITH CONTINUE_AFTER_ERROR успешно сформировал резервную копию поврежденной базы данных. Дополнительные сведения об обнаруженных ошибках см. в журнале ошибок SQL Server. BACKUP WITH CONTINUE_AFTER_ERROR успешно сформировал резервную копию поврежденной базы данных. Дополнительные сведения об обнаруженных ошибках см. в журнале ошибок SQL Server. In the log - Backup detected log corruption in database ka. Context is Bad Middle Sector Backup detected log corruption in database ka. Context is Bad Middle Sector

  1. Restored database from existing bk - RESTORE DATABASE [ka] FROM DISK = N'C:\2016.Bak' WITH NORECOVERY, REPLACE
  2. Tried to return the log - RESTORE LOG [ka] FROM DISK = N'D:\Tail.bak' WITH FILE = 1, NOUNLOAD, STATS = 1, NORECOVERY, CONTINUE_AFTER_ERROR

Result - An error occurred while processing the log for the database "ka". If possible, restore from backup. If the backup is not available, you may need to rebuild the log.

Everything. In this place I ended. I feel that there must be some way to get to the data, but I don’t understand how.

I can not rebuild the log - I'm sitting in the recovery state. I cannot get out of recovery - I have problems with the restored transaction log.

    1 answer 1

    If you restore the log with CONTINUE_AFTER_ERROR (or with STOP_ON_ERROR )

     RESTORE LOG [ka] FROM DISK = N'D:\Tail.bak' WITH FILE = 1, CONTINUE_AFTER_ERROR, NORECOVERY GO

    fails with an error after which it cannot be executed

     RESTORE DATABASE [ka] WITH RECOVERY GO

    then you can try to recover to some LSN in the log (the maximum possible), at which the RESTORE LOG still does not cause errors.

    To do this, read the headers of the database and log backups.

     RESTORE HEADERONLY FROM DISK = N'C:\2016.Bak' RESTORE HEADERONLY FROM DISK = N'D:\Tail.bak'

    in which we check the values ​​of the columns FirstLSN and LastLSN (we make sure that the LSN chain is not broken, and there really is additional information in the journal).

    Suppose we received:

     C:\2016.Bak FirstLSN LastLSN ----------------- ----------------- 64000000005600195 64000000017600001 D:\Tail.bak FirstLSN LastLSN ----------------- ----------------- 64000000005600195 66000000025600001

    comparing LSN: 64000000005600195 <= 64000000017600001 <66000000025600001 - OK.

    The LastLSN value from the database header (equal to 64000000017600001 ) is transferred from the decimal representation to the binary (see here , the fn_convertnumericlsntobinary function), we get 00000040: 000000B0: 0001 .

    Now, using sys.fn_dump_dblog we read the LSN sequence from the log dump (you can filter only transaction completion operations Operation = 'LOP_COMMIT_XACT' ):

     select [Current LSN] from sys.fn_dump_dblog( NULL, NULL, N'DISK', 1, N'D:\Tail.bak', default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default, default) where Operation = 'LOP_COMMIT_XACT';

    Suppose we received the following list:

     Current LSN ----------------------- 00000040:000000a0:0002 00000040:000000b8:0001 <-- сначала восстанавливаем журнал к этой отметке 00000040:000000c8:000b <-- потом к этой 00000040:000000d0:000a <-- ... 00000040:000000d8:000a <-- ... 00000040:000000e0:000a <-- ... 00000040:000000e8:000a <-- ... 00000040:000000f0:000b <-- ... 00000040:000000f8:0010 <-- ... 00000040:000000f8:0021 <-- и т.д, пока не встретим ошибку 00000040:000000f8:0027 <-- ERROR 00000040:00000110:0017 00000040:00000130:001b 00000040:00000130:001d 00000040:00000140:000a

    Re-initialize recovery:

     RESTORE DATABASE [ka] FROM DISK = N'D:\2016.bak' WITH NORECOVERY, REPLACE GO

    Now we take from the list the first LSN value, which follows later than LastLSN in the database backup (later than 00000040:000000B0:0001 ) and make RESTORE LOG to this mark:

     RESTORE LOG [ka] FROM DISK = N'D:\Tail.bak' WITH FILE = 1, STOPATMARK = 'lsn:0x00000040:000000b8:0001', NORECOVERY GO

    If the operation went without errors, repeat the same for the next mark, etc., until we reach the mark, the restoration to which will cause an error.

    If there are a lot of marks, then a dichotomous search can be applied, considering, however, that when moving forward it is enough to perform only RESTORE LOG to the new mark, but if you need to go back, then the recovery chain needs to be performed again ( RESTORE DATABASE ... WITH REPLACE ... , then RESTORE LOG ... to the desired mark).

    After the last mark that did not cause an error is determined, we will restore the database and log to this mark again:

     RESTORE DATABASE [ka] FROM DISK = N'D:\2016.bak' WITH NORECOVERY, REPLACE GO RESTORE LOG [ka] FROM DISK = N'D:\Tail.bak' WITH STOPATMARK = 'lsn:0x00000040:000000f8:0021', NORECOVERY GO

    After which we complete the restoration:

     RESTORE DATABASE [ka] WITH RECOVERY GO
    • Yes, I would gladly go to ʻEMERGENCY ', if I could perform at least some' ALTER DATABASE 'in the recovery state. - Anatoly
    • Add (early Enter pressed) that dbcc rebuild_log is banned, to rebuild a separate log now you also need ALTER DATABASE <dbname> REBUILD LOG . And DBCC CHECKDB is an unattainable dream for me at all :( And just RESTORE WITH RECOVERY doesn't release it, offers to rebuild the log :( - Anatoly
    • And now, when I finally read the answer to the end :) you need to add that RESTORE LOG ... WITH STOPATMARK=... also does not work. This infection (2014) in any case reaches the end of the file and gives the same result, even if I specify LSN, which is ALREADY in the restored full backup - Anatoly
    • @Anatoly, I tried to simulate the situation. I created a database with a table in it, inserted 1000 lines, zabekapil, inserted a couple of thousand more lines, translated it offline, smashed mdf, spoiled the log in the middle. Then the database is back online, tail backup, recovery of the database, recovery of the log with CONTINUE_AFTER_ERROR and STOP_ON_ERROR did not pass - you have an error like you are stuck in a state of recovery from which you can’t move. - i-one
    • @Anatoly However, recovery to a certain LSN worked (though not to the very last one, which I was able to extract from the log dump). After that I was able to read some lines that were inserted after the database backup. - i-one