Backup and restore strategy?

Question

Accepted Answer

A backup and restore strategy is essential for ensuring data recovery in case of system failure or disaster.

Regularly schedule full and incremental backups
Store backups in a secure offsite location
Test backups regularly to ensure data integrity
Have a documented restore plan in place
Consider using third-party backup and restore tools

Accepted Answer

Microsoft SQL Server supports five types of backup: full, differential, transaction log, tail log, and copy-only backup. 
Full backup is a complete backup of your SQL Server database. It backs up all of the objects of the database: tables, procedures, functions, views, indexes, etc.

Differential backup contains only the data that has been changed since the last full database backup was created. Creating differential backup usually takes less time than a full backup, because you back up only modified data instead of backing up everything.

Transaction log (T-log) backup is the most granular backup type in SQL Server because it backs up the transaction log which only contains the modifications made to the SQL Server database since the last transaction log backup. It’s effectively an incremental backup.

If you are performing regular transaction log backups, you can recover to a point right before a problematic transaction occurred, such as an incorrect deletion or update of data in a table. To perform a point-in-time restore you’ll have to do the following:

1- How long to keep data in the transaction log
2- Which types of backups you can perform
3- Which types of database restore you can perform

Simple Recovery

For databases using the Simple Recovery model, SQL Server automatically truncates the log on checkpoint operations, freeing up used space in the transaction log for additional transactions. When using Simple Recovery, transaction log backups are not supported.

Point-in-time restores are not supported and you can only restore the database up to the time of the latest full or differential database backup. The frequency of these backups determines how much data loss you may experience if a database using the Simple Recovery model needs to be restored.

Full Recovery

Under a Full Recovery model, all transactions remain in the transaction log file until you run a transaction log backup. The transaction log will never be auto-truncated as would occur under the Simple Recovery model.

With the Full Recovery model, you can recover your database to any point in time within a transaction log backup. As an example, if you run transaction log backups every 30 minutes, you can recover a database to the 15-minute mark within a transaction log backup; before that delete or update statement incorrectly changed data in the database. Data loss is minimized.

Bulk-Logged Recovery

The Bulk-Logged Recovery model is similar to Full Recovery except that certain bulk operations are not fully logged in the transaction log (this is called minimal logging). Operations like SELECT INTO, BULK import, and TRUNCATE operations are examples of minimally logged operations. With the Bulk-Logged Recovery model, your transaction logs may not become as large as they would under the Full Recovery model.

The downside, however, is that bulk-logged operations in this model prevent you from performing a point-in-time restore. So there is a potential for more data loss. If you’re not sure that Bulk-Logged is the right recovery model for your needs, it’s recommended you stick with Full Recovery.

Develop a backup schedule based on your business requirements
You need to back up your data regularly. At a minimum, schedule a weekly full backup and daily differential backups. If you use full or bulk-logged recovery models, schedule your transaction log backup frequency based on your RTO (Recovery Time Objective) and RPO (Recovery Point Objective). These metrics reflect how much data loss your business can deal with. If losing more than 30 minutes of database changes is a problem, then make sure to schedule your transaction log backups at least every 30 minutes.

Backup and restore strategy?

Popular interview questions of SQL Server Database Administrator