Miscellaneous Backup Notes
This week I have been teaching my understudy about backups and restores. Here are some important things we went over this week:
- Checking “Verify backup when finished” does not ensure that the backup is good.
- Checking “Perform checksum before writing to media” does not ensure the backup is good.
- Checking both 1 and 2 also does not ensure that the backup is good.
- Checking both 1 and 2 and performing a RESTORY VERIFYONLY does not ensure that the backup is good.
- The only way to ensure without a shadow of a doubt that a backup is good is using 1 and 2 and then restoring it somewhere else and running a DBCC CHECKDB on the database.
- Unless you are regularly restoring your backups you do not have a backup solution.
- If you switch from simple recovery mode to full recovery mode then you need to kick off a backup to start the chain.
- In full recovery mode (or bulk-logged recovery) you must backup the transaction log regularly enough to keep the log from filling up. This will truncate the log and allow you to provide point in time recovery. Mileage may vary with how often to perform this backup depending upon the size and requirements of your database.
- When using full recovery mode and you need an ad hoc backup make sure that you check the “Copy-only backup” so that you do not disrupt the backup chain by moving that backup to another location. You will screw up your recovery options at that point.
- In the full recovery model, using differential backups can reduce the number of log backups that you have to restore.