Home / server / Questions / 218616
Accepted
Dylan Beattie
Asked: 2011-01-05 08:46:53 +0800 CST

How do I restore a SQL Server database from last night's full backup and the active transaction log file?

  • 772

I have been told that it's good practise to keep your SQL Server data files and log files on physically separate disks, because it'll allow you to recover your data to the point of failure if the data drive fails.

So... let's say that mydata.mdf is on drive D:, and my mydata_log.ldf is on drive E:, and it's 16:45, and drive D: has just died horribly.

So - I have last night's full backup (mydata.bak). I have hourly transaction-log backups that will bring the data back up to 16:00... but that means I'll lose 45 minutes worth of updates.

I still have mydata_log.ldf on the E: drive, which should contain EVERY transaction that was committed right up to the point where the drive failed.

How do I go about recreating the database and restoring data from the backup file and the live transaction log, so I don't lose any updates? Is this possible?

sql-server backup sql-server-2008 transaction-log

3 Answers

  1. Best Answer

    To complete imtiaz's answer:

    First thing to do is to immediately try to backup the tail-of-log. Even with a corrupted (suspect status) database you will be able to backup the tail of the log.

    Example:

    BACKUP LOG db_name TO DISK = 'C:\TESTS\db_name_log.trn' WITH INIT, NO_TRUNCATE;

    Restore Full backup (NO RECOVERY option) + restore 16:00 Log backup (NO RECOVERY option) + restore tail backup (RECOVERY option) -> you are able to restore the database to the last point of failure.

    • 3

  2. You need to have a tail backup of the log file (depending on your version of sql). If you can backup the tail then you are set! If you can't then you will have to deal with the data loss and increase the frequency of log backups. I back mine up every 5 mins due to the business requirements.

    From BooksOnline: BACKUP LOG database_name TO WITH CONTINUE_AFTER_ERROR

    If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes.

    • 2

  3. first you need to create a tail log backup (to prevent data loss) by running below command 

    BACKUP LOG <database_name> TO <backup_device> 
WITH NORECOVERY, NO_TRUNCATE;

    Then restore by running below command 

    RESTORE DATABASE <database_name> FROM <backup_device> 
WITH NORECOVERY;
    • 0