As we have discussed backup strategies we have not covered a few important things you should also know about database backups built into Sql Server.
We talked about the primary backup methods, full, differential, and transaction log backups. There is a fourth backup option where you can backup a single file group. This is called a file group option. This backup strategy is most often implemented for large databases where a full backup or differential backup may simply be too big, or take too long. To use this option your database consists of two or more data files associated with a file group. Then when you create a table, index or even a materialized view, you can direct the object to be contained in a specific file group. Using this dividing method you can backup a portion of your database by performing a file group backup. This is not a strategy for someone new to SQL Server, so I left it out in the earlier discussions.
There are configuration settings you can make for your disaster recovery strategy on a database level. You can set each database individually to use different logging implementations, each impacting the disaster recovery strategy. You can use a fully logged backup strategy, which is what we have been talking about the last couple of days. At the complete other end of a disaster strategy is a simple logging implementation. This implementation does not require transaction log backups to clear the transaction log. More on this later. The third option is a bulk logged backup strategy. This strategy is used when you have a lot of bulk data brought into your database frequently. It minimally logs bulk insert operations to keep the transaction log from bloating from large volumes of data. You set this in the database configuration settings.
If you right click on a database in Sql Server Management Studio, and choose properties you can see the Recovery model for that database under the options page. You’ll see Full, Bulk Logged and Simple.
We have covered how to use the Full recovery model with Full, Differential, and Transaction Log backups.
The Bulk Logged recovery model does not log fully transactions for bulk inserts. As a result, it is important when using this recovery model to make a full backup as soon as possible after importing data using bulk import techniques.
The simple recovery strategy requires a full and or differential backup strategy. It does not support transaction log backups. Instead of maintaining a long running transaction log, the transaction log is truncated each time a checkpoint occurs, which is defined at the database instance level as the max time allowed for data loss. You’ll see the setting in the Server Properties under Database Settings. It is labeled Recovery interval (minutes). It truncates transaction log records that have been committed completely to the database files. If you use simple recovery, you need to perform full or differential backups in order to have anything to restore.
This should provide you with a high level disaster recovery strategy as it is implemented in Microsoft SQL Server. It should help you focus your learning on areas where you would like to have more expertise.
Cheers,
Ben