Some basic backup and restore strategies have few restrictions; for example, a full database backup has no real restrictions—it can be performed anytime. However, the more advanced file and filegroup backup strategies have some basic requirements you need to understand. Primarily, you must perform log backups regularly. Log backups are a crucial component of restore and are required for recovery if you use the file and filegroup backup strategy. To allow log backups and thereby minimize your work loss exposure, you must first set the database recovery model to either Full or Bulk_logged. Because the Simple recovery model doesn't let you back up the transaction log, you can't use this recovery model with the file and filegroup backup strategy.
The second step—optional but beneficial in setting up the file and filegroup strategy—takes some planning. When you create objects, take time to place your objects strategically within the database. In most databases and especially in VLDBs, your data will likely vary in the way it's used. For example, you'll probably have some data that's predominantly read-only and some that's predominantly read/write. In some tables, new rows might be read/write to accommodate frequent inserts, whereas old rows (historical data used mainly for analysis) would be read-only. For other tables, the distribution might vary based on corporate policy (for example, price changes are infrequent, so price information would be predominantly read-only—until price increases were put into effect). After reviewing the overall usage of your data, you can determine table usage and strategically place read-only tables (or tables with isolated or batch updates) on one filegroup and read/write tables on another. Doing so will not only save you time during a restore but can also save money and time during backups because although the read-only portion of your database might be very large, it doesn't need to be backed up as often as the read/write portion.
Additionally, partitioning your larger tables to split read activity from write activity can improve maintenance performance. Operations such as rebuilding an index take less time on smaller tables. For example, consider a typical sales table that holds the online sales information for a large company. Sales for the current month are read/write (which requires frequent index maintenance), but sales from the previous month or quarter are read-only because they're used only for analysis (maintenance occurs only once a month, when the data is moved to the read-only portion of the database). For tables whose usage differs, consider using separate filegroups for each type of data. For example, creating four separate filegroups—one for read-only data, one for read/write data, one for text and image data, and one for extremely large tables—can help when you're determining your backup strategies. Not only will you create smaller tables (partitions) that reduce maintenance times, you'll add numerous backup and especially restore strategy options that can reduce downtime and backup costs. To determine the correct number of filegroups and files for your database, you need to know your data; the sidebar "Filegroup Usage for VLDBs" contains some recommendations.
Backup by the Numbers
Once you're using good design and the Full (or Bulk_logged) recovery model, you're ready to start taking advantage of the file and filegroup backup strategy. To demonstrate the syntax and exact usage of this strategy, let's look at the case study that Figure 1 illustrates. To create the PubsTest database and all the backups in the case study, run the FileFilegroupStrategiesCaseStudy.sql script, which you can download at http://www.sqlmag.com (see "More on the Web" for instructions). You can execute this script in its entirety, but I recommend working through the script slowly to review the syntax and understand the backup strategy.
The case study uses seven files: a primary file, three files in a filegroup named RWFG (used for read/write data), two files in a filegroup named ROFG (used for read-only data), and one transaction log file. After creating the PubsTest database, the script modifies data in between several backups. The diagram lists the backup types from left to right in this sequence (the number corresponds to the number along the time line):
- Full file backup of the primary file
- Transaction log backup
- Full filegroup backup of the RWFG filegroup
- Transaction log backup
- Full filegroup backup of the ROFG filegroup
- Transaction log backup
- Differential filegroup backup of the RWFG filegroup
- Transaction log backup
- Full file backup of primary file
- Transaction log backup
- Differential filegroup backup of the RWFG filegroup
- Transaction log backup
- Final transaction log backup after the disaster
Note that the time line, at number 13, includes a backup after the disaster. When a database becomes suspect, your first question should be, "Can I back up the tail of the transaction log?" The tail of the log is the up-to-the-minute information that lets you recover your database from the last backup until the database became suspect. For backing up the tail of the log, use BACKUP LOG with the NO_TRUNCATE option.
Prev. page
1
[2]
3
next page 
