Database maintenance and when is it needed (Part 5: Back Up Database)

This week we are closing our discussion regarding database maintenance steps, with the most important database maintenance of all:

"Back Up Database"

How often should I backup my databases?

Well, this obviously depends on how critical is the database. Some test database that holds no important data can be backed up once in a while (or even not at all). A database of major importance should probably be backed up each day. A database that holds critical data (financial data, credit data, e.t.c.) should probably not only be backed up very often but you should also have high-availability safeguards in place (like failover clustering, database mirroring, log shipping, e.t.c.).

What do these Back Up Database steps mean?

Full database backup means that the entire database is getting backed up. Without a Full backup, other backup operations (Differential & Transaction log) are meaningless.

Differential backup means that only the data that was changed since the last Full backup will be backed up. A differential backup is of no use if you are not taking Full database backups in the first place.

Transaction log backup means that only the transaction log of the database will be backed up. Transaction log backups are also of little use without a valid Full backup.

What kind of backup should I use for my databases?

For a database of minor importance, you could probably set its recovery model to SIMPLE and capture a combination of daily or even weekly Full backups & Differential backups.

e.g. capture a Full backup every Sunday and a Differential backup on other days.

For a database of medium importance, you should probably set its recovery model to SIMPLE and capture a daily combination of Full backups and Differential backups.

e.g. capture a Full backup every Monday, Wednesday and Friday. Capture a Differential backup on other days.

For a database of critical importance, you should set its recovery model to FULL and capture a combination of daily Full and Differential backups. Additionally you will need to capture transaction log backups very often, to keep the transaction log growth under control.

e.g. capture a Full backup every Monday, Wednesday and Friday. Capture a Differential backup on other days. Capture a transaction log backup every ~1hour.

Do I really need to backup my databases if I have high-availability
solutions in-place?

Yes, definitely. There is no technology that replaces a good backup set:

• Failover Cluster/Failover Instance: If the database gets damaged in any way or if data is deleted from the database, a failover instance will do you zero good.

 

• Database Mirroring: Database mirroring will try to repair database corruption if only one of the databases gets damaged. But what if someone “accidentally” deletes some tables from
  the database? Mirroring will not help you in this scenario as the table deletion will be repeated at the mirror database.

 

• Log Shipping: If you are lucky enough to stop the transaction log backup before it is applied to the secondary server, then you won. If you don’t have enough time to stop the transaction log getting applied to the secondary server, then you lost.

 

• Replication: As with any technology that synchronizes databases, any data loss that occurs at the source database will be repeated at the destination database too.

Database backup saves lives - of the DBAs in particular :)

 

Next week I will start discussing about how you can easily troubleshoot SQL Server performance issues without having any developer skills, by using simple tools and straightforward methods. Stay tuned!