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-availabilitysolutions in-place?
Yes, definitely. There is no technology that replaces a good backup set:
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!