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
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.
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.
Perform the tail-of-the-log backup to backup with
NORECOVERYoption. This is the log generated since the last log backup was executed.
Restore the most recent full backup with
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
Restore the tail-of-the-log backup with
Recover the database with
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.