0

I have a production database. I am using FULL recovery model. I am taking regular FULL backup every week. I am planning to take LOG backup every day. Now, if my database crashes or failed to load due to a reason then what can I do? Restore the database backup and restore all the log backups or only the last log backup. Should I automate the FULL backup and LOG backup using Jobs? Any help will be appreciated

2

If your database is in the full recovery model, you should immediately start taking log backups along with the full backups. For simple backup recovery model, you will not need log backups. The frequency depends on your required SLA (RTO & RPO), more on it here.

Second, you should definitely be automating your backup tasks - both full and log backups (if you are taking log backups). There are hundreds of reasons that can justify automating the tasks, my favorite one is I don't have to run/repeat the task again in a well planned automation.

In order to automate your backups using SQL Server Agent jobs, you can either write your won scripts, test it and implement to suit your environment, or pick up one of the freely available scripts and modify to suit your need. My favorite is Ola Hallengren's free sets of scripts, which he publishes and updates on his website. These scripts have been around for a while, and are well tested in various SQL Server environments.

Restore

Assuming your database is in full recovery mode, and you have taken your full backups and log backups, following are a simplified steps for database recovery.

  1. Perform the tail-of-the-log backup to backup with NORECOVERY option. This is the log generated since the last log backup was executed.

  2. Restore the most recent full backup with NORECOVERY option.

  3. Restore all the log backups since last full backup beginning the first log backup done after the full backup to the most recent log backup with NORECOVERY option.

  4. Restore the tail-of-the-log backup with NORECOVERY option.

  5. Recover the database with RECOVERY option.

Note: If differential backup is part of your backup plan, you may have to restore the most recent differential backup before you restore the log backups, if the most recent differential backup was taken after the most recent full backup.

The database recovery could be more complex depending on the physical layout of your database files, and your recovery objectives etc. You can learn more about backup and recovery in SQL Server book online. Aslo, Paul Randal has a series of blog post on backup and recovery on his Accidental DBA series.

  • How to restore database using the FULL and LOG backups? Means, first I need to restore FULL Backup and then ? – user960567 May 3 '14 at 14:41
  • See my updates of the restore process. – user353gre3 May 3 '14 at 16:43
  • Thanks. Can you write the an example with TSQL? It will be a great for me and others. Also, if I have taken the full backup at 05-01-2014 00:00:000. Then I need to restore all log backups till 05-01-2014 00:00:000 but not before 05-01-2014 00:00:000, e.g, 04-30-2014 backups. – user960567 May 3 '14 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.