Create a Backup Strategy
Protecting data is job 1 for DBAs, and the most important step in protecting that data is to create backups. To suitably protect and back up your system's data, you must understand SQL Server's three recovery models and your database-backup options. In basic terms, the recovery model sets the balance between logging overhead and being able to completely recover data. SQL Server 2000 provides three recovery models: Simple, Full, and Bulk-Logged.
- The Simple recovery model offers the lowest logging overhead but can't recover any data past the end of the last backup. All data modifications made since the last backup are considered expendable and, in the case of a restore, must be redone.
- The Full recovery model considers all data to be critical and therefore recoverable to the point of failure. All data modifications are logged. By default, SQL Server uses the Full recovery model.
- The Bulk-Logged recovery model lies midway between the other two models. In this model, the vast majority of typical database transactions are logged and fully recoverable, but bulk operations such as bulk copy and SELECT INTO aren't logged and must be redone. The Bulk-logged model logs all other transactions and can recover to the end of the last database or log backup.
You can back up SQL Server database data to disk, tape, or other media. Performing disk backups is the fastest mechanism for backing up and restoring data. However, when you back up to disk, you should protect against drive failure by directing backups to a separate drive and, ideally, a separate controller from your database data. SQL Server supports three basic types of database backup: full, differential, and log. A full backup creates a full copy of the database. A differential backup copies only the database pages modified after the last full database backup. A log backup copies only the transaction log. You can also perform a partial database backup by backing up only file groups, but that technique is beyond the scope of this article.
How you choose recovery models and a backup strategy involves many considerations that are specific to your business. Some of the primary questions you must answer are
- What's your availability requirement?
- How much downtime is acceptable?
- What's the financial cost of downtime?
- Are some databases more critical than others?
- How frequently does data change?
- Can data be recreated?
If you don't already have a basic backup plan in place, the sample backup schedule in Table 1 can give you some ideas for creating one. The basic idea behind this sample plan is that the full database backup gives you a known point from which to begin the restore process. Frequent differential backups minimize the number of transaction-log backups that you need to apply to bring your restore process up to the last current transaction. In the sample backup plan, the maximum number of transaction-log backups that you might need to apply is eight. Your basic restore strategy is to restore the last full database backup followed by the last differential backup. Then you apply all the transaction-log backups since the last differential backup. Naturally, you'll probably need to adjust the frequency of each backup type to fit your organization's requirements. Using this example, you perform the backup to disk and institute a separate process for archiving the data.
You're Ready to Go
I've given you information that can help you get started administering a new SQL Server installation or get a better handle on administering the SQL Server systems that you already have. Of course, there's plenty more to know about managing SQL Server. Two good resources for more in-depth information about SQL Server are SQL Server Magazine (http://www.sqlmag.com) and the Microsoft SQL Server Web site (http://www.microsoft.com/sql). Also check out the sidebars "More SQL Server Tools," page 57, and "Importing and Exporting Data" for additional information about helpful SQL Server tools. Have fun diving in to SQL Server!
End of Article
