SQL Server Magazine

Most companies that are in business for a while eventually encounter a disastrous event that has the potential to put the company out of business. And every company that uses databases will at some point experience a database crash. A database backup is a copy of the data, structures, and security objects contained within a database. Each database should be backed up on its own schedule, based on the number of write transactions that occur each day. To minimize data loss when disaster hits, you must back up your databases—all of them. And to ensure that your backups are good, you should test them by using them in a restore operation. At the very least, you need to have copies of your databases that you can quickly restore, and you need to be comfortable with the restore operation itself.

After people, data is a company’s most valuable asset. Your responsibility as a DBA is to ensure that your company’s data is safe—that is, that you have a copy of the data that you can reinstate even if the entire data center is reduced to rubble. Database backups are the simplest, most cost-effective means of safeguarding your company’s data.

Don’t be lulled into a false sense of security by a high-availability system you might have recently purchased. If you’ve virtualized and consolidated, then you might have actually increased your risk. Life was easy when you ran just one SQL Server instance per computer—but 10 SQL Server instances running on virtual machines (VMs) can all come crashing down when the physical box fails. If you can afford the additional investment, you can avoid a disaster of such large proportions by clustering the virtual server hosts. High-availability schemes are crucial when your data and systems must be available all the time. But even high-availability systems can be affected by fire, flood, and earthquake. You still need to do backups. Not just anyone in a company can do a database backup. For information about who should be backing up your databases, see the Web sidebar “Who Can Do Backups?” www.sqlmag.com, InstantDoc ID 98372.

How often you should back up a database depends on how long you have to restore it. In general, the more often you back up a database and the type of backup you take, the shorter the restore time. You can tailor backups and restores for each database. The kind of backup you decide to use will depend on the size of the database and the amount of transactional activity. The three most common types of backups are full, log, and differential. (For information about recovery models, see the Web sidebar “Database Recovery Models,” www.sqlmag.com, InstantDoc ID 98373; for information about SQL Server backup commands, see the Web Sidebar “Standard Backup Commands,” www.sqlmag.com, InstantDoc ID 98374.)

Full Backups
The full backup strategy is the easiest to understand and implement. At the end of every business day, or during whatever time window you’ve allocated for database backups, you simply perform a full backup of the database, as Figure 1 illustrates. You perform no separate log backups, and you don’t have to remember any special parameters. Backup file management is simple, because you only need to manage the full backup file. In addition, restoring from a full backup is extremely easy because you have only one full backup file to apply. Full backups are especially useful in organizations with a limited or relatively new IT staff.

A full backup works best for a “small” database—which you can define as a database whose full backup can be completed in the time allowed. When SQL Server performs a full database backup, it first backs up all the extents on the hard disk (an extent is eight contiguous pages, with each page being 8K in size). Then, SQL Server backs up the transaction log so that any user changes made during the database backup are also captured in the full backup file.

If you’re performing only full backups, you might lose some data in the event of a system crash—specifically, any changes made since the last full backup. If your database is updated infrequently, such as by high-speed bulk operations, then you can plan full backups to run only immediately after the bulk data modifications, and your data should be protected.

Full backups aren’t appropriate for production systems that have anything other than a few transactions. After you use the full backup strategy to restore a database, you must redo any transactions or bulk data loads that were applied to the database after the backup. If your most current backup file is damaged, you need to use the next previous full backup to restore the database—and you’ll have to ensure that all transactions applied to the database after that backup are manually redone.

To perform a full backup, run the following code:

BACKUP DATABASE AdventureWorks
TO DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.bak’
WITH INIT, NAME = ‘AdventureWorks Full Db backup’,
DESCRIPTION = ‘AdventureWorks Full Database Backup

DISK is the destination for the backup file. You can back up to disk or to tape; in this case, you’re backing up to the hard disk. Make sure the folder you’ll use as a store for the backup files exists before you begin. In most cases, backing up to hard disk is faster but more expensive than backing up to tape. For an extra level of protection, you can first back up to hard disk, then perform a file-level backup to write the database backup file to tape. WITH INIT specifies that the backup file should be overwritten. This method works well as long as a Windows backup occurs after every database backup. NAME is the name you give the backup file, which can contain as many as 128 characters. If you don’t specify a name, it’s left blank. DESCRIPTION is a longer, friendly description that makes identifying the file weeks or months after the backup was made a relatively straightforward process.

To perform a full restore of the database, run the following code:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘E:\SQLdata\BACKUPS\AdventureWorks_FullDbBkup.BAK’
WITH RECOVERY, REPLACE

WITH RECOVERY instructs the restore operation to roll back any uncommitted transactions that might be on the transaction log and leave the database in operational mode, ready to resume work. REPLACE means overwrite any existing file with the same name. For more information, see the Web sidebar “Replacing a Database,” www.sqlmag.com, InstantDoc ID 98375.

If you use the full backup strategy, you need to monitor the size of the transaction log. A full backup doesn’t truncate (remove inactive entries from) the transaction log. If you perform only full database backups, you should follow the full backup with a log backup using the TRUNCATE_ONLY option, as the following code shows:

BACKUP LOG AdventureWorks
WITH TRUNCATE_ONLY

TRUNCATE_ONLY doesn’t back up the transaction log; it simply forces SQL Server to take a checkpoint, which then truncates the log, getting rid of inactive entries and shrinking the size of the log file. Because this option will be dropped in future releases of SQL Server, you might instead use the simple recovery model and let SQL Server automatically rid the transaction log of inactive entries.

   Prev. page   [1] 2     next page