You can back up a database in a number of ways. The different backup methods can be used together, separately, or as part of a complicated pattern designed to protect your data in the way that is best for your environment. To back up your database, you can use the following:

Each of these backups protects data in slightly different ways by making a protected copy of that data. All of these types of backup operations write your data out to a file. That file can be on a different disk on your server, can be on another server, can be on portable media such as thumb drives, or can be a specialized backup system such as a tape backup system that writes out to specialized media resembling old-fashioned cassette tapes (although tape backup through SQL Server has been deprecated in SQL Server 2008 and won't be supported at all soon). Part of deciding on a backup strategy for your system is also picking the media, or what is called a physical backup device, on which to write your backup files.

When backups are run against the databases on your SQL Server 2008 instance, the users and applications connected to those databases are not interrupted. Most of the time, in fact, you will see minimum effects from running backups on reasonably active systems. If the system is under heavier loads, you might see significant performance degradation during some of the backup operations. Because backups don't take the systems down or offline, it allows for your backups to be run fairly frequently since you don't have to wait for all users to exit the system or kick the users off the system while you run backups. It's still a good idea to run the most intrusive backup operations, such as full backups, during off-peak hours when the user load is less.

More often than not, you won't be running backups manually. You will need to schedule the backup so that it occurs on an automated basis. The most common method of automation is using the scheduling process of SQL Server Agent. This is covered in detail in the "Automating the Backup Process" section.

To better understand which of the backup types might be applicable in your situation, we'll describe each of them in more detail.

Full Backups

A full backup creates a complete copy of all the data, structures, and code that are contained within the database when the backup operation is run. This copy of the database is done at the lowest possible level within the system, so it's not like running a query to retrieve data where some could be missed. All the data, all the code, and all the structure within the database will get backed up to the backup file. It is not possible for a successful backup to be taken of a database and miss data or change a stored procedure.

The full backup is the primary means of backing up a database. It's the foundation from which other backup types will operate. It's also the foundation from which a database restore (covered in Chapter 9) will be run.

A backup can be initiated through direct calls using the T-SQL scripting language or by having the graphical user interface (GUI) supplied with SQL Server 2008—SQL Server Management Studio (SSMS)—generate and run the T-SQL scripts for you.

Full Backup Using SQL Server Management Studio

To initiate a backup in SSMS, connect to your SQL Server instance, and expand the Databases folder. Select the database you want to back up, and right-click that database. This opens a context menu. Navigate through the menu to Tasks and then select Back Up Database. This launches the Back Up Database window shown in Figure 8-1.

Figure 8.1. The Back Up Database window

There are two pages in the Back Up Database window that you can select on the left side of the screen. It opens on the default General page. The default values are the database you selected previously and a backup type of Full. The check box Copy Only Backup allows you to make a backup of the database that is separate from the normal chain of backups. We'll describe that in more detail in the section "Backing Up the Logs." You don't need to modify anything in the Source section of the window.

The next section of the window is the "Backup set" section. This is where you define the intended target of your backup. The options give you a full range of control over how you want to manage your backup. You can give it a name and a description to make it easier to understand the purpose of the backup when you go to restore it (restoring is covered in detail in Chapter 9). You can also specify that a backup set expires after a certain number of days or on a specific date. For a simple backup operation, leave all these settings at the default values.

Finally, you need to specify a destination. To do this, click the Add button. This will open the Select Backup Destination dialog box, as shown in Figure 8-2.

Figure 8.2. Select Backup Destination dialog box

You can select either a specific file location or a backup device. A backup device is a predefined file or a tape backup system that is available to your server. In the window shown in Figure 8-2, a specific file has not been defined. Click the ellipsis button (...), which will open the Locate Database Files window, shown in Figure 8-3.

Figure 8.3. Locate Database Files window

Here you can define the path and file name for the backup. Clicking the OK button for each of these windows will close them and take you back to the Back Up Database window shown in Figure 8-1. The Destination field will show the file you specified.

From here you can run a backup. Operations running against the database can continue uninterrupted, as described earlier. The backup operation itself will take longer to run as the size of your database increases. In addition, you can run one backup after another, all pointed to a single file. Since the default options are in operation, a new backup will be created within the file each time you run the backup operation. By default SSMS will not overwrite the file with each new backup; it will instead append the backup to the file along with the other backups in your backup set. If you would prefer to change this behavior, when the Back Up Database window is open, click the Options page option on the left side of the screen to see all the options available, as shown in Figure 8-4.

Figure 8.4. Back Up Database window's Options page

As you can see, the default option is to add, or append, the backup you're currently running to the existing file or device. The file or device in this context is referred to as a backup set. You can modify this so that instead it overwrites the file each time you run the backup. We prefer this methodology because it makes it easier to know exactly what is backed up where, visually. You don't need to open a file and peruse backups within it to see which backups are available, when they were created, and so on. We'll discuss looking through the backup sets in Chapter 9.

Other options that you should consider when running a backup include adding a verification that the backup was done completely and correctly. You can do this by selecting the check box in the Reliability section of the Options page labeled "Verify backup when finished." This is a function of the RESTORE command and will be covered in more detail in Chapter 9. The other options available here are not generally used when performing a standard full backup.

Full Backup Using T-SQL

Although you can perform all your backup operations through Management Studio, most DBAs use T-SQL instead. Running your backup operations through T-SQL enables you to automate backups through scripting, which increases the flexibility of the backup process. Even the backup operations in Management Studio use T-SQL under the covers. You can always see the T-SQL being issued by the Management Studio GUI by clicking the Script button visible at the top of the Backup Up Database window (shown earlier in Figures 8-1 and 8-4).

To use T-SQL to back up the database, open Management Studio, and connect to the server as outlined earlier. Now open a T-SQL editing window by clicking the New Query button visible by default on the toolbar. This will open a new window that looks like a blank white screen with a cursor blinking in it. This is the T-SQL editing window. Type the following to generate a full backup of the AdventureWorks2008 database:

BACKUP DATABASE AdventureWorks2008
TO DISK = 'c:dataAdventureWorks2008.bak';

Clicking the Execute button will immediately back up the database to the disk location specified. You could also back up to a device. To do that, first you need to create a device. The following script creates a backup device that will allow you to back up to a file:

sp_addumpdevice @devtype = 'disk', @logicalname = 'MyBackupDevice',
    @physicalname = 'c:dataAdventureWorks2008.bak'

And then you can use the device in a backup as follows:

BACKUP DATABASE AdventureWorks2008
TO MyBackupDevice;

Although this seems simple, it can quickly become somewhat more complicated. Normally we back up to a file that includes the date or date and time so that we can keep more than one backup file for a given database within a folder. A complete full backup script that includes the ability to overwrite a file looks like this (basicbackup.sql in the download):

DECLARE @BackupLocation NVARCHAR(100);
SET @BackupLocation = 'c:dataAdventureWorks2008_' +

CONVERT(NVARCHAR(8),GETDATE(),112) + '.bak';
BACKUP DATABASE AdventureWorks2008
TO DISK = @BackupLocation
WITH INIT;

This command uses a variable, @BackupLocation, to define where we intend to store the backup file. We then get the current date and use the CONVERT function to turn into a format that looks like 'MMDDYYYY'. The output looks like this: c:dataAdventureWorks2008_20090228.bak.

You are probably beginning to see the types of control you can exercise over your backup operations using T-SQL. For the rest of the chapter, with a few exceptions, we'll focus on using T-SQL to perform the backup operations.

DECLARE @BackupLocation NVARCHAR(100);
SET @BackupLocation = 'c:dataAdventureWorks2008_' +
CONVERT(NVARCHAR(8),GETDATE(),112) + '_diff.bak';
BACKUP DATABASE AdventureWorks2008
TO DISK = @BackupLocation
WITH INIT, DIFFERENTIAL;

File and Filegroup Backups

For smaller databases, less than 200GB–300GB in size, a full backup or a full backup with differentials should work just fine. As databases get bigger still, you might need to consider backing up smaller parts of the database only rather than backing up the entire database. You would still perform file or filegroup backups in conjunction with full backups. Backing up a file or a filegroup allows you to back up smaller pieces of the database, which means that the backup process runs faster. This approach does make the management of the backups more difficult. When you start backing up individual database files, you have to plan on restoring only individual files. This makes recovery operations much more difficult. However, the cost may be worth the benefit because it is possible to restore only the damaged file to a database, rather than restoring the entire database. Files can be stored in groupings called filegroups. These can also be backed up individually from the database.

When a database is in simple recovery mode, this means that the log data of completed transactions is not being kept; you can perform a file backup only on those files that are marked read-only. You can only back up a file or filegroup of a database that has more than one filegroup in it.

To back up a file for a given database, you will need to know the logical file name of the file you want to back up, not the actual file name. When backing up a filegroup, you just need the name of the filegroup. You can get the logical file names for a database through the Database Properties window. Right-click the database in question, and select Properties from the pop-up menu. The Database Properties window will open. Click the Files tab to see the logical file names, as shown in Figure 8-5.

Figure 8.5. Database Properties window displaying the logical file names

You can see the Logical Name column in the "Database files" section of the window. Another way to get the information is to go to the system management view sys.database_files and query it as follows:

SELECT  df.name
FROM    sys.database_files AS df;

Once you have the logical file name, you can use it in the backup script. Before a file backup of the AdventureWorks2008 database can be run, it will need to be put into a recovery model other than simple. We'll use the full recovery mode since that is the most commonly used in production systems:

ALTER DATABASE AdventureWorks2008
SET RECOVERY FULL;

Now you can run the file backup like this (filebackup.sql in the download):

BACKUP DATABASE AdventureWorks2008
FILE = 'AdventureWorks2008_DATA'
TO DISK = 'c:dataAdventureWorks2008_DATA.bak'
WITH INIT;

This will back up just the file specified, AdventureWorks2008_DATA. If you want to back up multiple files in a file group, the syntax changes only slightly:

ALTER DATABASE AdventureWorks2008
SET RECOVERY FULL;
GO
BACKUP DATABASE AdventureWorks2008
FILEGROUP = 'Primary'
TO DISK = 'c:dataAdventureWorks2008_Primary.bak'
WITH INIT;

This will back up as many files as there are within the filegroup as a single backup file. It's important to remember that backing up files individually should be done only with the more easily controlled mechanisms of the full backup or differential backup.

A backup is just a moment in time in the database. When you restore a backup, the restore can restore to the point only when a backup was run. But your data is changing all day long. You can't run full backups all day, and the differential or file backups help only so much. To capture the changes in data over the day, you need to put log backups to work.

As data is manipulated within the database, a record of the edits that have successfully completed is kept in the database log. These edits are wrapped by what are called transactions. A transaction can contain a series of data changes or just one. These transactions are what are stored within the log. The log is maintained by the system in order to allow for the recovery of the database in the event of some type of shutdown of the system. Not all data will be automatically recorded to the disk; some may be in memory, waiting its turn to get to the disk, but the completed transactions will always be in the logs.

The data in the log can be kept until the next time everything gets written out to disk, an event referred to as a checkpoint. Keeping the log information only until a checkpoint is referred to as the simple recovery model. It's simple because to restore a database, you need only the backups, whether full, differential, or files. You can't use the log data in the recovery in any way. This means that you will not be able to restore transactional data. It will be lost forever in the event of a catastrophe. Simple recovery is almost never used in production systems and certainly wouldn't be used in mission-critical systems.

The data in the log can be kept indefinitely, depending on disk space, until a special backup operation, called a log backup, is run. This approach of retaining the log is referred to as the full recovery model. There is also a recovery model called bulk-logged; however, it behaves like full enough for our purposes in this chapter that we'll ignore it. It's the full recovery model that allows for a more complete database recovery plan because the log allows for recovering to a point in time. Point-in-time recovery will be covered in detail in Chapter 9. Log backups are not cumulative. Each one captures the information in the log since the last log backup. As each backup is run, the entries backed up are removed. You can store log backups in individual files, but you need to think about how many of those files you might have to manage. Unlike full backups or differential backups, the log backup is a good place to keep your backup files in a backup set, appending each new log backup to the first one.

Initiating Fully Recovery Mode

To get started with log backups, first you need to set your database to the full recovery model. In Management Studio, right-click the database to bring up the context menu, and then select Properties. This will open the Database Properties window for the database in question. Select the page Options, and you will see something similar to Figure 8-6.

Figure 8.6. Database Properties window showing the Options page

Near the top of the page is a drop-down for the recovery model. Select Full from the list of three available models. Clicking the OK button will switch the database to the full recovery model. From this point forward, completed transactions will be kept in the log. Before you run out of disk space, it's time to learn how to back up the log.

Log Backup Using Management Studio

To back up the log for the database using Management Studio, perform the same actions as for backing up a file to open the Backup Database window. Right-click the database in question, and then select Tasks and Backup from the menu choices. When the Backup Database window opens, select Transaction Log from the "Backup type" drop-down list. You will then have a window that looks something like Figure 8-7.

Figure 8.7. Back Up Database window ready for transaction log backup

To set the destination for the location you want to use, you may need to remove the most recently used location by clicking the Remove button. After that, you can click the Add button and select a disk or device to receive the backup file. The options are the same, and the Options page would look like it does in Figure 8-4. If you click OK, the log backup will run, and the log itself will be truncated, because all the entries that get backed up are removed.

BACKUP LOG AdventureWorks2008
TO DISK = 'c:dataAdventureWorks2008_log.bak'

BACKUP DATABASE AdventureWorks2008
TO DISK = 'c:dataAdventureWorks2008_Copy.bak'
WITH COPY_ONLY

Now that you have an understanding of the basics of database and log backups, you're going to want to automate the backup process. You'll have to decide the best schedule for your system to get maximum coverage through the various backup methods available to you. There are any number of mechanisms for running processes automatically on Windows Server systems; some are built in, and some are available through third-party resources. SQL Server comes with its own scheduling software that we'll focus on using here, SQL Server Agent. Agent is a piece of software that runs separately from the SQL Server software, yet it's an integral part of the SQL Server system.

To see SQL Server Agent, open SQL Server Management Studio, and connect to the server as previously explained. Once connected, you will see an icon at the bottom of the Object Explorer window, which is visible by default, labeled SQL Server Agent. If you have not configured the Agent to run automatically, you'll need to start it. Right-click the icon, and select Start from the context menu. Expanding this icon will show the information available as part of SQL Server Agent, as shown in Figure 8-8.

Figure 8.8. SQL Server Agent viewed through Management Studio

A number of options are available in SQL Server Agent, but we're concerned only with Jobs in this chapter. You can expand the Jobs folder and see any that have already been created on your server. To create a new one in order to schedule backups, right-click the Jobs folder, and select New Job from the pop-up menu. This will open the New Job window, as shown in Figure 8-9.

Figure 8.9. New Job window showing the General page

You can see a list of possible pages on the left and details regarding the page you have selected on the right. It opens by default on the General page. At the top of the window on every page is the opportunity to generate a script from the job by clicking the Script button; you can also open Books Online by clicking the Help button. In this window you can also define a name for the job. The name should be descriptive of exactly what functions you expect the job to perform. In this case, type in Backup User Databases. You also pick the owner of the job. The owner of the job defines the default user that the job will run as. You need to be careful that the user you pick has the appropriate privileges on the SQL Server system and on any of the drives, file shares, or backup devices you will be using in this automated job to back up your databases. We're setting the owner as sa, which means this job will run, by default, as the system administrator. You can also define a category, which will help you manage the various jobs that you create. We've selected Database Maintenance from the drop-down menu. You can also provide a more detailed description of the job that you are creating. Finally, you can set the entire job to be enabled or not through the Enabled check box. When you've completed defining the information on the General page, don't click the OK button because that will create the job, and this job doesn't do anything yet.

Click the Steps page on the left, which will change the New Job window to look like Figure 8-10.

Figure 8.10. New Job window showing the Steps page

Since there have been no steps created yet, there is nothing on display in this window. When steps have been created, this window will allow you to manipulate the steps by rearranging the order using the Move Step arrows and defining which step is the first step through the "Start step" drop-down box. You can add a step by clicking the New button or by clicking the Insert button when you have at least one step created. You can edit a script by clicking the Edit button or by double-clicking the step. Finally, you can delete a script through the Delete button.

Click the New button at this time to create a new step. This will open the New Job Step window visible in Figure 8-11.

Figure 8.11. New Job Step window

In the New Job Step window you will give each step a step name by typing it into the appropriate box. We chose Backup Databases as the name of this step. You also have to define the type of command that the step represents. A large number of server-related subsystem types are available, but in this instance you can use the default, which is Transact-SQL script (T-SQL). You have the option of changing the context under which this step is run, meaning which system user the step is executed as, by manipulating the "Run as" drop-down list. No changes were necessary for this step. You then pick the database where the script will run. Since we're running a query that will back up all the user databases in the system, we need to run it from the master database where information that relates to the entire system is stored. Finally, you have to supply it with a command. You can type it in, open it from a file, or paste it in. You can parse the script to verify that it will run correctly. Here is the script used to back up all the user databases in the system (backupalldatabases.sql in the download):

--very simple query to back up user databases
DECLARE @backupscript NVARCHAR(MAX)
   ,@dbname NVARCHAR(100)
   ,@dbpath NVARCHAR(100) ;

DECLARE DBList CURSOR FAST_FORWARD
    FOR SELECT  name
        FROM    sys.databases
        WHERE   NAME NOT IN ('master','model','tempdb','msdb')
        AND state = 0 ;

OPEN DBList ;

FETCH NEXT FROM DBList INTO @dbname ;

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @dbpath = 'C:DATA' + @dbname + '_' + CONVERT(NVARCHAR, GETDATE(), 112)
            + '.bak'' WITH INIT' ;
        BACKUP DATABASE @dbname TO DISK = @dbpath WITH INIT ;
        FETCH NEXT FROM DBList INTO @dbname ;
    END

CLOSE DBList ;
DEALLOCATE DBList ;

This script retrieves a list of databases from the system catalog view sys.databases. It eliminates the four basic system databases using their names, and it makes sure that the databases it is backing up are online by checking that state = 0. It loads this list into a cursor and steps through the rows of the list, backing up the user databases one at a time.

Some other options are available in the New Job Step window. You can access them by clicking the Advanced page. The options this makes available are visible in Figure 8-12.

Figure 8.12. New Job Step displaying the Advanced page

The first thing visible at the top of the Advanced page is a flow control action. This allows you to control the behavior of jobs with multiple steps. By changing the "On success action" setting using the drop-down menu, you can control the next action. These are the values available and what they do:

For a single-step job like this, the default will work just fine. If the step were to fail to execute, you can have it automatically attempt to run again a defined set of times by changing the "Retry attempts" value to something greater than 0. You can have the retry wait a certain number of minutes by changing "Retry interval (minutes)" to a value greater than 0. If the number of retries has been exhausted or the step is not set to retry and it does fail, the next flow control point is encountered, "On failure action." The drop-down has the same choices as the "On Success action" list, and they perform the same functions. They're just in a different order and respond to a failure of the step rather than a success. Since this is a T-SQL script, the section Transact-SQL script (T-SQL) is enabled. The first value is an output file, which will enable you to log the events that occurred during the execution of this step. In the case of the backup script, it will show all the databases backed up, or it will show any errors that occurred during the backup. You can create a new file every time it executes or have it add the data to an existing file, if any, by selecting the "Append output to existing file" check box. You can direct the output to a table in a database directly for later access. This will place additional load on your server to write and maintain the table. A job writes certain data, such as whether it succeeded, how long it took, and when it was run, to a log called the history. You can decide to have the output of the step included with the job history. This can make the history larger and hard to read, so we don't recommend it.

With the step finally defined, click the OK button. This will write it back to the job, and you will then see it in the New Job window, as shown in Figure 8-13.

Figure 8.13. New Job window showing the Steps page with a step visible

The window has changed, and you can see that the step has a number and has been defined as the start step for the job. You can add other steps as needed to this job, but in this instance, this is the only step needed.

Click the Schedules page to determine when this automatic process will be run. The Schedules page will be empty as the Steps page was before, as shown in Figure 8-14.

Figure 8.14. New Job window with a blank Schedules page displayed

You can create more than one schedule for a job so that it runs at varying times depending on the needs of your system. To create a new schedule, click the New button. You can also pick from a list of existing schedules. This allows you to create one schedule that, for example, executes once a week at 2 a.m. on a Sunday and apply that schedule to multiple jobs, all of which you want to start at 2 a.m. on a Sunday. However, it can be problematic to start lots of jobs at the same time. You can edit schedules on the list by clicking the Edit button. You can remove schedules from the list by clicking the Remove button. To see how to define a new schedule, click the New button. Figure 8-15 shows the New Job Schedule window that opens.

Figure 8.15. New Job Schedule windowTo begin defining a new schedule, you have to give it a name, in this case Weekly.

You then select the schedule type, which can be any of the following:

If it was a one-time occurrence, then that section would be enabled, and you could define a date and time for the job to execute. This is a recurring job, so the Frequency section is enabled. The first option you have to choose is the frequency definition of the occurrence by selecting from the Occurs list:

You can then pick the frequency of the occurrence. In this case, it's running every week on Sunday, but it could run on multiple days in the week with a varying number of weeks separating each execution. You then determine the daily frequency. In this case, it's executing once at 2 a.m., but you could have it run multiple times by choosing the "Occurs every" radio button. If this is selected, you need to determine the intervals between occurrences, either hours, minutes, or seconds. And you can determine a start and stop time for multiple occurrences within the day or days that the schedule is firing. You can determine the day on which the process begins to run by selecting the Start date. You can also define a time for the job to stop running by defining an end date. In this case, you select the "No end date" radio button. This job will run once a week on Sunday at 2 a.m. and continue to do so until you do something about it. Click OK to close the New Job Schedule window.

You will now see that the Schedules page has the schedule that you just finished defining, as shown in Figure 8-16.

Figure 8.16. New Job window showing a schedule in the Schedules page

There are other pages available to the job—namely, Alerts, Notifications, and Targets—but they are outside the scope of this definition. With the completion of the schedule, you now have everything you need to have a backup of your user databases run once a week automatically. Click the OK button.

You can see the job you created by clicking the Jobs folder in the Object Explorer window just like in Figure 8-17.

Figure 8.17. List of jobs in the Object Explorer window

You can execute the job manually too by right-clicking the job and selecting the "Start Job at step" option. This will open a window with the steps on display. Just accept the default, the first step, and click OK. The job will start executing. Otherwise, it will execute based on the schedule defined.

New with SQL Server 2008 is the ability to encrypt your data using transparent data encryption (TDE). The idea behind this new tool is that you can encrypt the database without having to modify applications or change structures within your database. You would use this because it makes it impossible for someone to simply steal a copy of your disk and then have access to all your data. When a database has TDE enabled, the backups are also secured. With TDE, the encryption of the data as it's written to the database and the decryption of the data as it comes back out both happen in real time at a very low level within the system, namely, the page level. Pages are how data is stored on the disk, so as data is read or written on the disk, it goes through this decryption or encryption process prior to the information getting into memory or being sent to your application.

Once a database has been set to be encrypted, any backups of that database are also encrypted, automatically, through the use of certificates. (A certificate is a security object that is generated, either by an external source or by SQL Server.)

The syntax for backing up the database doesn't change. To see encrypted backups in action, use the following script to enable encryption on a test database. In the following example, we're using the AdventureWorksLT database (enableencryption.sql in the download):

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Chapter8Backup';
GO
CREATE CERTIFICATE Chapter8Certificate WITH SUBJECT = 'Chapter 8 Certificate'
GO
USE AdventureWorksLT

GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE Chapter8Certificate
GO
ALTER DATABASE AdventureWorksLT
SET ENCRYPTION ON
GO

Once this script is run, the database is encrypted. It's very important that you have a backup copy of the certificate and the private key that protects the certificate for use when restoring this database (covered in detail in Chapter 9). To back up the certificate and the private key, use the following script (backupcertificate.sql in the download):

USE [master];
GO
BACKUP CERTIFICATE Chapter8Certificate TO FILE =
'c:dataChapter8Certificate'
WITH PRIVATE KEY (FILE = 'c:datapkChapter8Certificate',
ENCRYPTION BY PASSWORD = 'Chapter8Backup') ;

You can run a normal backup to back up the database because, just as the encryption and decryption of the data are invisible to the users as they query the data, it's invisible to you as you manipulate backups. This will work just fine:

BACKUP DATABASE AdventureWorksLT
TO DISK = 'c:dataAdventureWorksLT.bak'

The real work begins when you go to restore an encrypted backup. Read Chapter 9 for details.

Backups can take up quite a bit of space. You may have more than one full backup of databases in your system stored on disk. Besides that, you could have multiple incremental backups for several of those databases. The log backups also take up quite a bit of room. Basically, it all adds up. Although disk drives are cheap and getting cheaper, you're unlikely to have an unlimited supply at hand. You also have to consider the time it takes to bring a new drive or mount point online, configure it, modify the backups to use it, and so on. Lots of time and money are being spent in maintaining these backup files. What if you could just make them smaller? SQL Server 2008 Enterprise introduces a mechanism for compressing backup files.

SQL Server 2008 Enterprise native backup compression can save you disk space. Because the backups are smaller and because writing the backup is the longest part of the backup process, the backup will be performed faster. Unfortunately, there is no free lunch here. You gain this at the sacrifice of processing power. There will be an added load on the CPUs of your system to support the backup compression process. There are a couple of other restrictions on the use of backup compression:

Even though backup compression is available only in the Enterprise edition of SQL Server, all the versions of SQL Server can restore a compressed backup file (restores are covered in detail in Chapter 9).

Backup compression is disabled by default, so it's necessary to turn it on in order to use it. It's very common to make compression available automatically on all enterprise systems, and we recommend it. You can enable backup compression on the server. This means that all database backups have compression enabled by default. To enable compression on the server, you need to change the server options. You can do this through a T-SQL command like this:

EXEC sp_configure 'backup compression default', '1';
RECONFIGURE WITH OVERRIDE

The command sp_configure allows you to change server options. In this case, the query is changing the option backup compression default. It's setting the value to 1, which means true. If you wanted to turn backup compression off, you would set the value to 0.

You also have the ability to set the backup compression through SQL Server Management Studio. After connecting to the server, right-click the server in the Object Explorer window. This will open the Server Properties window. Select the Database Settings page, and you will see something similar to Figure 8-18.

Figure 8.18. Server Properties window with Database Settings page displayed

You can enable or disable backup compression by selecting or deselecting the "Compress backup" option. Backup compression is enabled in Figure 8-18.

You can enable or disable compression directly during a single backup using T-SQL. You only need to introduce another WITH command to the T-SQL statement. Since the database is set to run compressed backups by default, you can use this command to back up the AdventureWorks2008 database without compression:

BACKUP DATABASE AdventureWorks2008
TO DISK = 'c:dataAdventureWorks2008_uncompressed.bak'

WITH INIT, NO_COMPRESSION;

If you have previously enabled default compression using one of the two mechanisms described earlier, use it again to make sure default compression is disabled. Now you can run a backup with compression using this command:

BACKUP DATABASE AdventureWorks2008
TO DISK = 'c:dataAdventureWorks2008_compressed.bak'
WITH INIT, COMPRESSION;

For these two databases, the uncompressed file was 71767KB, and the compressed file was 14218KB for a compression ratio just slightly over 5:1. This is a real cost savings for data storage.

Backup compression should not be combined with other database compression schemes such as row or page compression. Because the data is already compressed, you won't realize any space savings by compressing it again, but you will incur the overhead of processing the data in the attempt to compress it further.

With a more complete understanding of the options available to protect your databases through backups, you need to design a backup plan. The driving factors behind a backup plan are very seldom technical. The business has to provide information defining the service-level agreement (SLA) for the implementation of a backup plan. The information you need to gather from the business includes the following:

Once you've gathered all this information, and probably more, from the business, you'll need to examine your environment to ensure you can meet those requirements:

After you've determined that you have the technology in place to support the SLA for the business, you need to design a backup plan to support the SLA.

As an example, imagine a medium-sized database at about 200GB. The business has determined that the data in this database needs as much protection as you can provide through backup processes. The system is an online transaction processing (OLTP) system that either modifies, deletes, or inserts about 3GB to 5GB of data each day. You need to keep about a week's worth of data online. The business principals agree that they could deal with a five-minute data loss if they must.

To start with, 200GB will take a little while to back up on your system. Because of this, you decide to run one full backup a week, every Sunday. You can set up an Agent job to back up this database once a week. Because this satisfies the week of data online, you decide to overwrite the backup each week. Since you need to be able to recover down to as close as possible to any kind of crash, but with no more than a five-minute data loss, you will need more backups. You couldn't run an entire week's worth of log backups. Instead, since you need to worry only about 3GB to 5GB of data changing each day, for a max of 35GB in a week, you decide to run an incremental backup every 12 hours during the week starting 12 hours after the full backup and ending 12 hours before. To track these backups over time, you create a new incremental backup with the date and time in the name to make it as easy as possible when you have to recover the data. You can create a second Agent job to manage this operation. Finally, in order to get as close to the five-minute limit as you can, you set the recovery model of the database to the full model. Since you need to keep only 12 hours of log available, you set the log backups to go to a single backup set with a 12-hour expiration so that it cleans itself out. Once the log is being kept, you need to set up a log backup routine to run every five minutes. All this data is a little more than you want to keep on your drives, so you take advantage of the fact that you're already running the Enterprise version of SQL Server to enable compression. You'll need to create one other job that cleans out last week's incremental backups since you chose to keep the files separate from a backup set. You now have a backup plan that looks something like the plan in Table 8-1. This shows the type of backup being done followed by the frequency, and if there is an expiration for the backup, it outlines when that should occur.

There are other ways that this backup process could be laid out. It really depends on your system and the needs of your business.

Backups are a vital part of protecting the data that runs your business. Because of all the different types of backups, you have a lot of flexibility and power in establishing a backup plan that meets the needs of your business. You need a full backup as the foundation from which you can build more sophisticated backup routines. Remember that losing a backup of a database, even a well-secured database, could cause data loss unless the backup itself is encrypted. Data compression helps with speed and storage but comes at the cost of CPU.

As important as backups are, they're useless unless you know how to restore them. Chapter 9 shows how to get the data from your backups.