Recovering Database when Database files lost but log still exists in SQL Server
In the event that we lose a database due to corruption / or
the volume is missing but still have the log file intact we may wish to
recover to the very latest point in time using that log file (even when
the log file hasn't been backed up).
To do this we need to back up the tail of the log file. This will back up all transactions since the last log backup and can be done even when the database is offline or not there, unlike a conventional log backup (note: cannot be taken if database taken offline gracefully).
BACKUP <log database_name> TO DISK = 'disk:\location\backup name' WITH no_truncate, continue_after_error;
Then we need to restore the latest full database backup in no recovery mode
Recover any subsequent transaction logs also in no recovery mode in the correct sequence
Recover the tail log in recovery mode.
Perform a new backup of the database as the log chain will be broken.
Finished
Note: The difference between a log backup and a tail log backup is the 'no_truncate' option which is selected for the tail log backup. This means it will not truncate the log and will allow this backup to work regardless of the state of the database. No_truncate will break the log chain so should only be used during a recovery situation where for example your database is corrupt and the log cannot be backed up using a normal transaction log backup. A full backup should be taken afterwards. Truncate_only will remove the inactive part of the log.
To do this we need to back up the tail of the log file. This will back up all transactions since the last log backup and can be done even when the database is offline or not there, unlike a conventional log backup (note: cannot be taken if database taken offline gracefully).
BACKUP <log database_name> TO DISK = 'disk:\location\backup name' WITH no_truncate, continue_after_error;
Then we need to restore the latest full database backup in no recovery mode
Recover any subsequent transaction logs also in no recovery mode in the correct sequence
Recover the tail log in recovery mode.
Perform a new backup of the database as the log chain will be broken.
Finished
Note: The difference between a log backup and a tail log backup is the 'no_truncate' option which is selected for the tail log backup. This means it will not truncate the log and will allow this backup to work regardless of the state of the database. No_truncate will break the log chain so should only be used during a recovery situation where for example your database is corrupt and the log cannot be backed up using a normal transaction log backup. A full backup should be taken afterwards. Truncate_only will remove the inactive part of the log.
Comments