When you restore from backup, your data loss will be everything since the last backup you restore. Ergo, to minimize data loss, you have to minimize the time between backups. If a database is small enough, you may be able to take full and differential backups with a high frequency, but not in all cases. Even if a database is small enough to perform full and differential backups frequently, it still will not provide you as many recovery possibilities as log backups. To truly ensure that you are minimizing data loss, you have to take log backups.
Taking log backups rules out the Simple Recovery Model. You cannot back up the log in this recovery model.
Bulk-logged Recovery Model allows log backups to be taken and restored. This recovery model works like Full Recovery Model except it allows certain transactions to be minimally logged. If a log backup contains a minimally logged transaction, it can still be restored in its entirety, but it cannot be restored to a point-in-time. However, if a log backup does not contain any minimally logged transactions, it can be used for point-in-time restores. (for proof, see T-SQL Tuesday #31 – Bulk-Logged Recovery Model and Point-in-time Restore)
Only full recovery model allows for log backups and ensures that all restore options, like point-in-time restores, are available to you.
All user databases and system databases (except for tempdb) should be backed up regularly. This includes the hidden system database known as the resource database (see Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the Resource Database for details on how to back up the resource database)
You should run integrity checks regularly on all databases including all system databases. System databases can become corrupt as well (which is why they should also be backed up). Even tempdb should have integrity checks run on them. Better to find out before it's the busiest point of the day that tempdb is corrupt so you can fix it before it affects your business processes.
Every half hour is a good starting point when you don't yet know what your RPO and RTO are yet, but that's not where you stop.
Recovery Time Objective (RTO) is how long you can be down in the event of a disaster. This is key in determining your restore plan as it helps you decide on what types of backups to employ to ensure recovery within the specified time.
Recovery Point Objective (RPO) is the amount of data you are willing to lose in the even of a disaster. Since your potential for data loss is the time since your last backup, this determines your maximum frequency for backups. For example, if your RPO is 30 minutes, then your maximum backup frequency is every 30 minutes. There are reasons why you may want to backup more frequently than the specified RPO, but RPO is your maximum frequency.
Best practice regarding auto-shrink is for it to always be disabled. As such, there are no best practices for log backups related to triggering auto-shrink.
If not specified in the backup command, backups will use the default server setting to determine whether or not backups should be compressed. By default, this option is disabled, but it is a general best practice to enable backup compression unless you have a reason not to enable it. Either way, it is not possible to say it will ALWAYS be compressed or uncompressed unless you specify it in the backup command.
Prior to SQL Server 2016, TDE and backup compression did not play well together. This was immensely improved in SQL Server 2016, and it is not a best practice to use backup compression with TDE encrypted databases. (see SQL 2016 Backup Compression with TDE Enabled Databases for details about this change)
Compression requires slightly more CPU than normal backups. This slight increase in CPU is more than offset by the savings in the amount of data that has to be written to the files. Unless your SQL Server has no overhead for even a slight increase in CPU, you should use backup compression by default. This is true for restoring compressed backups as well.
You likely will end up restoring from backup, but if you do so, you will incur some data loss unless you take a backup of the tail of the log. In many cases, if the log file is still available, you can back up the active portion of the log to include in your eventual restore process. If you are successful in backing up the log, you can then restore from backup without data loss.
Never detach a database that is damaged (suspect, corrupt, etc). The chances of reattaching it is slim to none. (see Creating, detaching, re-attaching, and fixing a SUSPECT database for more details)
Running CHECKDB is often the right answer in many situations (not this one), but running it with the repair options should NEVER be the first thing you do for any situation.
When developing a backup plan, it is important to start with the restore process. To ensure a smooth restore in a time of disaster, you should make the process consistent by having a single location where backups are located no matter where the backup process was run. Using a remote share so that the path is always the same no matter where you perform the backup or restore is the best practice.
The CHECKSUM option for backup will validate the checksum of all pages that contain a checksum value when writing them to the backup and raise an error if the checksum values do not match as well as writing a checksum for the entire backup to the header of the backup file. Likewise, using the option for restores will recheck all checksum values present on pages when writing them to the database file as well as the checksum for the whole backup file.
The best way to know that a backup is restorable is test it by doing a restore.
The recommended and default page verification method is CHECKSUM.
If a log backup runs while a full backup is running, the log backup will run and the log backup will be created. However, because the full backup is using the log file for its backup purposes, the log file will not be truncated and the starting log sequence number for log backups will not progress. The next log backup will back up the same data as the previous log backup.
Share your Results :
Share your Results :
Share your Results :
Share your Results :
Please share this quiz to view your results.