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:

  1. Checking “Verify backup when finished” does not ensure that the backup is good.
  2. Checking “Perform checksum before writing to media” does not ensure the backup is good.
  3. Checking both 1 and 2 also does not ensure that the backup is good.
  4. Checking both 1 and 2 and performing a RESTORY VERIFYONLY does not ensure that the backup is good.
  5. 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.
  6. Unless you are regularly restoring your backups you do not have a backup solution.
  7. If you switch from simple recovery mode to full recovery mode then you need to kick off a backup to start the chain.
  8. 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.
  9. 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.
  10. In the full recovery model, using differential backups can reduce the number of log backups that you have to restore.

Enjoy!

 

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on December 4, 2012, in Maintenance and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: