# Microsoft SQL Server 7.0 Administrator's Pocket Consultant

### Overview

Ideal at the desk or as a portable reference for the office, this book provides database administrators with hands-on reference needed to solve problems with a concise, direct approach, and delivers quick reference support for infrequently used steps and hands-on tasks.
... See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
• All (9) from $1.99 • New (4) from$2.49
• Used (5) from $1.99 Close Sort by Page 1 of 1 Showing All Note: Marketplace items are not eligible for any BN.com coupons and promotions$2.49
Seller since 2010

Feedback rating:

(1867)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
0735605963 BRAND NEW. We are a tested and proven company with over 900,000 satisfied customers since 1997. We ship daily M-F. Choose expedited shipping (if available) for much ... faster delivery. Delivery confirmation on all US orders. Read more Show Less

Ships from: Nashua, NH

Usually ships in 1-2 business days

• International
• Standard, 48 States
• Standard (AK, HI)
• Express, 48 States
• Express (AK, HI)
$23.99 Seller since 2008 Feedback rating: (175) Condition: New 0735605963 BRAND NEW NEVER USED IN STOCK 125,000+ HAPPY CUSTOMERS SHIP EVERY DAY WITH FREE TRACKING NUMBER Ships from: fallbrook, CA Usually ships in 1-2 business days • Standard, 48 States • Standard (AK, HI)$31.99
Seller since 2015

Feedback rating:

(348)

Condition: New
Brand New Item.

Ships from: Chatham, NJ

Usually ships in 1-2 business days

• International
• Standard, 48 States
• Standard (AK, HI)
• Express, 48 States
• Express (AK, HI)
\$45.00
Seller since 2015

Feedback rating:

(218)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

• Standard, 48 States
• Standard (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

### Overview

Ideal at the desk or as a portable reference for the office, this book provides database administrators with hands-on reference needed to solve problems with a concise, direct approach, and delivers quick reference support for infrequently used steps and hands-on tasks.

### Product Details

• ISBN-13: 9780735605961
• Publisher: Microsoft Press
• Publication date: 10/29/1999
• Edition number: 1
• Pages: 500
• Product dimensions: 5.56 (w) x 8.02 (h) x 1.19 (d)

### Meet the Author

William R. Stanek is an award-winning author who's written more than 100 books, including Windows Server 2012 Inside Out, Windows 8 Administration Pocket Consultant, and Microsoft SQL Server 2012 Pocket Consultant. He is the series editor for the Pocket Consultant line of books.

Part I: Microsoft SQL Server 7.0 Administration Fundamentals
Chapter 1: SQL Server Administration Overview
Chapter 2: Configuring And Tuning SQL Server
Part II: Microsoft SQL Server 7.0 Administration
Chapter 3: Managing The Enterprise
Chapter 5: Microsoft SQL Server 7.0 Security
Part III: Microsoft SQL Server 7.0 Data Administration
Chapter 6: Database Backup And Recovery
Chapter 7: Importing And Exporting Data
Chapter 8: Distributed Data And Remote Servers
Chapter 9: Data Replication
Part IV: Performance, Optimization, And Maintenance
Chapter 10: Monitoring And Profiling Microsoft SQL Server 7.0
Chapter 11: Creating Tables And Indexes
Chapter 12: Database Automation And Maintenance
Index

### First Chapter

Information is the fuel that drives the enterprise, and often the most criticalinformation is stored in databases. Databases are where you'll find an organization's customer account information, partner directories, product knowledge base, and other important data. To protect an organization's data and to ensure the availability of its databases, you need a solid database backup and recovery plan. Backing up databases can protect against accidental loss of data, database corruption, hardware failures, and even natural disasters. It's your job as a database administrator to make sure that backups are performed and that they're stored in a safe and secure location.

### Creating a Backup and Recovery Plan

Creating and implementing a backup and recovery plan is one of your most important duties as a database administrator. Think of database backup as an insurance plan for the future-and for your job. Important data is accidentally deleted all the time. Mission-critical data can become corrupt. Natural disasters can leave your office in ruins. With a solid backup and recovery plan you can recover from any of these situations. Without one, you're left with nothing to fall back on.

Creating and implementing a backup and recovery plan takes time. You'll need to figure out what databases need to be backed up, how often the databases should be backed up, and more. To help you create a plan, consider the following:

• What type of database are you backing up?  System and user databases often have different backup and recovery needs. For example, the master database is essential for all Microsoft SQL Server operations. If the master database fails or becomes corrupt, it takes the whole server down with it. But you don't need to back up master every hour or every half hour-as you might have to do with a critical user database that handles real-time customer transactions. You only need to backup master after you create a database, change configuration values, configure SQL logons, or perform similar activities that make changes to the database.
• How important is the data in the database?  How you judge the data's importance can go a long way toward determining when and how you should back up the database. While you may back up a development database only once a week, you would probably back up a production database at least daily. The data's importance also drives your decision about the type of backup. With that development database, you'd probably do a full backup once a week. With an in-house customer order database that is updated throughout the weekday, you'd probably perform full backups twice a week and supplement this with daily differential backups and hourly backups for the transaction logs.
• How often are changes made to the database?  The frequency of change can drive your decision about how often the database should be backed up. Because a read-only database doesn't ordinarily change, it doesn't need to be backed up regularly. On the other hand, a database that's updated nightly should be backed up after the nightly changes are posted. A database that's updated around the clock should be backed up continually.
• How quickly do you need to recover the data?  It's important to consider time when you create a backup plan. For mission-critical databases, you may need to get the database back online swiftly; to do this, you may need to alter your backup plan. Instead of backing up to tape, you may want to back up to disk drives or use multiple backup devices. Both options are many times faster than restoring from a single tape device.
• Do you have the equipment necessary to perform backup?  You need backup hardware to perform backups. If you don't have the hardware, you can't perform backups. To perform timely backups, you may need several backup devices and several sets of backup media. Backup hardware includes a tape drive, optical drives, removable disk drives, and plain-old disk drives. Generally, tape drives are less expensive but slower than other types of drives.
• What is the best time to schedule backups?  You'll want to schedule backups when database usage is as low as possible. This will speed the backup process. However, in the real world you can't always schedule backups for off-peak hours. So you'll need to carefully plan when key databases are backed up.
• Do you need to store backups off-site?  Storing copies of backup tapes off-site is essential to the recovery of your systems in the case of a natural disaster. In your off-site storage location, you should also include copies of the software you may need to install in order to restore operations on a new system.

Backing up a database is a bit different than backing up a server or a workstation. Primarily, this is because you'll often need to combine all (or nearly all) of the available techniques to ensure that you can recover a database completely. The basic types of backups you can perform include

• Complete database backups  Perform a full backup of the database, including all objects, system tables, and data. When the backup starts, SQL Server copies everything in the database and also includes any needed portions of the transaction log as the backup is in progress. Because of this, you can use a complete backup to recover the complete state of the data in the database at the time the backup operation finishes.
• Differential backups  Designed to back up data that has changed since the last complete backup. Because you store only the changes, the backup is faster and can be performed more often. As with complete backups, differential backups include needed portions of the transaction logs, which allow you to restore the database to the time when the backup operation finishes.
TIP:
You can only use differential backups in conjunction with complete backups, and differential backups can't be performed on the master database. Don't confuse differential backups with incremental backups. Differential backups record all changes since the last full backup (which means the size of the incremental backup grows over time). Incremental backups record changes since the most recent full or incremental backup (which means the size of the incremental backup is usually much smaller than a full backup).
• Transaction log backups  Transaction logs are serial records of all database modifications and are used during recovery operations to commit completed transactions and to roll back uncompleted transactions. When you back up a transaction log, the backup stores the changes that have occurred since the last transaction log backup and then truncates the log, which clears out transactions that have been committed or aborted. Unlike complete and differential backups, transaction log backups record the state of the transaction log at the time the backup operation starts (not when it ends).
• File and filegroup backups  Allow you to back up database files and filegroups rather than the entire database. This is useful with large databases where, to save time, you want to back up individual files rather than the entire database. Many factors affect file and filegroup backups. When you use file and filegroup backups, you must back up the transaction log as well. Because of this dependency, you can't use this backup technique if Truncate Log On Checkpoint is enabled. Furthermore, if objects in the database span multiple files or filegroups, you must back up all the related files and filegroups at the same time.

Once you determine the backup operations you'll use on each database and how often you'll back up each database, you can select backup devices and media that meet these requirements. The next section covers the selection of backup devices and media.

### Selecting Backup Devices and Media

Many different solutions are available for backing up data. Some are fast and expensive. Others are slow but very reliable. The backup solution that's right for your organization depends on many factors, including
• Capacity  The amount of data that you need to back up on a routine basis. Can the backup hardware support the required load given your time and resource constraints?
• Reliability  The reliability of the backup hardware and media. Can you afford to sacrifice reliability to meet budget or time needs?
• Extensibility  The extensibility of the backup solution. Will this solution meet your needs as your organization grows?
• Speed  The speed with which data can be backed up and recovered. Can you afford to sacrifice speed to reduce costs?
• Cost  The cost of the backup solution. Does the solution fit within your budget?

Capacity, reliability, extensibility, speed, and cost are the issues that will influence your choice of a backup plan. If you determine the relative value of these issues to your organization, you'll be on the right track to selecting an appropriate backup solution. Some of the most commonly used backup solutions include

• Tape drives  Tape drives are the most common backup devices. They use magnetic tape cartridges to store data. Magnetic tapes are relatively inexpensive but aren't highly reliable. Tapes can break or stretch. They can also lose information over time. The average capacity of tape cartridges ranges from 100 MB to 2 GB. Compared with other backup solutions, tape drives are fairly slow. Still, the key selling point is the low cost of tape drives and magnetic tapes.
• DAT drives  DAT (digital audio tape) drives are quickly replacing standard tape drives as the preferred backup devices. DAT drives use 4 mm and 8 mm tapes to store data. DAT drives and tapes are more expensive than standard tape drives and tapes, but they offer higher speed and more capacity. DAT drives that use 4 mm tapes typically can record over 30 MB per minute and have capacities of up to 16 GB. DAT drives that use 8 mm tapes typically can record more than 10 MB per minute and have capacities of up to 10 GB (with compression).
TIP:
To perform faster backup and recovery operations, you can use multiple backup devices with SQL Server. For example, if it normally takes four hours to perform a full backup or restoration of the database, you can cut the backup and restoration time in half using two backup devices; with four backup devices you could fully back up or restore the database in an hour.
• Auto-loader tape systems  Auto-loader tape systems use a magazine of tapes that create extended backup volumes capable of meeting the high-capacity needs of the enterprise. With an auto-loader system, tapes within the magazine are automatically changed as needed during the backup or recovery process. Most auto-loader tape systems use DAT tapes. The typical system uses magazines with between 4 and 12 tapes. The key drawback to these systems is the high cost.
• Magnetic optical drives  Magnetic optical drives combine magnetic tape tech.nology with optical lasers to create a more reliable backup solution than DAT. Magnetic optical drives use 3 ½" and 5 ¼" disks that look similar to floppies but are much thicker. Typically, magnetic optical disks have capacities of between 1 GB and 4 GB.
• Tape jukeboxes  Tape jukeboxes are similar to auto-loader tape systems. Jukeboxes use magnetic optical disks rather than DAT tapes to offer high-capacity solutions for the enterprise. These systems load and unload disks stored internally for backup and recovery operations. The key drawback to tape jukeboxes is the high cost.
• Removable disks  Removable disks, such as Iomega Jaz, are increasingly being used as backup devices. Removable disks offer good speed and ease of use for a single drive or single system backup. However, the disk drives and the removable disks tend to be more expensive than standard tape or DAT drive solutions.
• Disk drives  Disk drives provide the fastest way to back up and restore databases. With disk drives, you can often accomplish in minutes what a tape drive takes hours to do. When your needs mandate a speedy recovery, nothing beats a disk drive. The drawbacks to disk drives, however, are high cost and low extensibility.

Although backup device selection is an important step in implementing a backup and recovery plan, it isn't the only step. You also need to purchase the tapes, disks, or both, that will allow you to implement your backup and recovery plan. The number of tapes, disks, or drives you need depends on

• How much data you'll be backing up
• How often you'll be backing up the data
• How long you'll need to keep additional data sets

The typical way to implement backups is to use a rotation schedule whereby you rotate through two or more sets of tapes, disks, or files. The idea is that you can increase media longevity by reducing media usage, and at the same time reduce the number of actual tapes, disks, or files you need to ensure that you have data on hand when necessary.

BEST PRACTICE:
For important databases, I recommend using four media sets. Use two sets for regular rotations. Use the third set for the first rotation cycle at the beginning of each month. Use the fourth set for the first rotation cycle of each quarter. This technique allows you to recover the database in a wide variety of situations.

Table 6-1 lists backup strategies you may want to use. As you can see, these backup strategies are based on the type of database as well as the type of data.

TIP:
The size of a database can change automatically, and master may get updated without your knowing about it. Because of this, often the best backup strategy for master is to schedule backups every other day and to rotate through several backup sets so that you can go back to several different versions of master if necessary.

Table 6-1.Backup Strategies for System and User Databases

 Database Type Details Strategy User Up to the minute recovery Run complete backups twice a week, if possible. Use nightly differential backups and back up the transaction log every 10 minutes during business hours. Do not use Truncate Log On Checkpoint, as this will make it impossible to recover some transactions. To improve backup/restore speed, use multiple backup devices whenever possible. Up to the hour recovery Run complete backups twice a week, if possible.Use nightly differential backups and back up the transaction log every 30 minutes during business hours. Do not use Truncate Log On Checkpoint. To improve backup/restore speed, use multiple backup devices whenever possible. Recovery of daily changes Run complete backups at least once a week. Use nightly differential backups and back up the transaction log every four hours during business hours. Do not use Truncate Log On Checkpoint. Read-only database Schedule a complete backup of the database every 30 days and supplement this with an additional complete backup if the database is modified. System distribution Available when you configure replication and the server is acting as a distributor. Schedule complete backups after snapshots. With transactional replication, schedule regular log backups. master Run complete backups immediately after creating or removing databases, changing the size of a database, adding or removing logons, or modifying server configuration settings. Don't forget to maintain several backup sets for master. msdb If you schedule jobs through the SQL Server Agent, back up this database regularly because this is where the job schedule and history is maintained and backup history is stored. model Treat like a read-only database. tempdb Normally doesn't need to be backed up. This database is re-created each time you start SQL Server.

### Creating a Backup Device

Previous versions of SQL Server required you to configure backup devices before you could back up databases. With SQL Server 7.0, this is no longer the case, and you don't need to explicitly define backup devices. Nevertheless, backup devices do provide an easy way to ensure that you create backups that have the same filename and location time after time. With consistent names and locations, you can more easily manage the backup and recovery process.

To create a backup device using Enterprise Manager, complete the following steps:

1. Start Enterprise Manager and then access the server you want to work with.
2. Click the plus sign (+) next to the server's Management folder.
3. Right-click Backup and then, from the shortcut menu, select New Backup Device. This opens the dialog box shown in Figure 6-1.
4. In the Name field, type the name of the logical backup device. Use a short but descriptive name, such as Customer Device or Master Device, to make the device easier to work with.
5. If you've installed a tape drive and want to back up to the tape drive, select the Tape Drive Name field and then use the related drop-down list box to select the target drive.
6. If you're backing up to a file, select the File Name option button and then enter the full path to the backup file you want to associate with this device, such as E:\MSSQL\BACKUP\CUSTOMER.BAK.
7. Click OK.

With Transact-SQL, you create backup devices using SP_ADDUMPDEVICE. The syntax and usage for this command is shown in Listing 6-1. This command uses many different arguments, including device_type, logical_name, physical_name, controller_type, and devstatus. The device_type is the type of device you're using-disk, tape, or pipe. The logical_name is the name of the backup device. The physical_name is the full path to the backup file. The controller type is 2 for a disk, 5 for a tape, and 6 for a named pipe. The devstatus is either noskip, to read ANSI tape headers, or skip, to skip ANSI tape headers.

Listing 6-1. SP_ADDUMPDEVICE Syntax and Usage

Syntax
sp_addumpdevice [@devtype =] 'device_type',

[@logicalname =] 'logical_name',

[@physicalname =] 'physical_name'

[, {

[@cntrltype =] controller_type

|

[@devstatus =] 'device_status'

}

]

Usage
EXEC sp_addumpdevice 'disk', 'Customer', 'c:\mssql\backup\cust.bak'

EXEC sp_addumpdevice 'disk', 'Customer on Backup Server',

'\\omega\backups\cust.bak'

EXEC sp_addumpdevice 'tape', 'Customer on Tape',

'\\.\tape0'


### Performing Backups

Backups are an essential part of database administration. They're so important that SQL Server provides multiple backup procedures and several backup-related wizards-all designed to help you better manage database backup and recovery. In this section, I'll look at standard backup procedures, the Backup Wizard, and the Transact-SQL backup process. The final ingredient for backups involves database maintenance plans, which you'll learn about in Chapter 12, "Database Automation and Maintenance."

### Creating Backups in Enterprise Manager

In Enterprise Manager you can start the backup process using either of these techniques:
• Select the server you want to work with and then, from the Tools menu, choose Backup Database. This opens the SQL Server Backup dialog box.
• Select the server you want to work with and then, from the Tools menu, choose Wizards. This opens the Wizards dialog box. Click the plus sign (+) next to Management and then double-click Backup Wizard.

Regardless of which technique you use, the steps you follow are similar. Rather than covering the same information twice, I'll focus on the SQL Server Backup dialog box and how you can use it to perform backups in these situations:

• Creating a new backup set
• Adding to an existing backup set

You can use these techniques with the Backup Wizard as well. The key difference is that you'll need to go through a series of dialog boxes.

Creating a New Backup Set

Whenever you back up a database for the first time or start a new rotation on an existing backup set, follow these steps to create the backup:

1. Select the server you want to work with and then, from the Tools menu, choose Backup Database. This opens the SQL Server Backup dialog box shown in Figure 6-2.
2. Use the Database drop-down list box to select the database you want to back up.
3. In the Name field, type a name for the backup set you're creating. This is an ordinary, English name that'll help you tell at a glance what the backup contains. For example, name the first backup set for the customer database Customer Backup Set 1. Then you can add the complete, differential, and transaction log backups for this rotation to the set.
4. Type a description of the backup, such as "Set 1 contains the weekly complete, daily differential, and hourly transaction log backups."
5. In the Backup area, use the option buttons to select the type of backup. Because this is your first time running a backup on the database, some of the options may be unavailable. Don't worry, once you run a complete or differential backup, the other options should become available.
NOTE:
The only available backup option for the master database is Database - Complete. That's because you can run only complete backups on master.
6. If a backup set exists and is listed in the Destination area, select it and click Remove.
7. Click Add to display the Choose Backup Destination dialog box shown in Figure 6-3. To use a file as the backup destination, select the File Name option button and enter the full path to the backup file, such as e:\DATA\ BACKUPS\CUST.BAK or \\OMEGA\BACKUPS\CUST.BAK. To use a backup device, select the Backup Device option button and then choose the backup destination using the drop-down list box. Click OK when you're ready to continue.
8. To schedule the backup, click Schedule and then configure the backup schedule as discussed in Chapter 12, "Database Automation and Maintenance."
9. To set additional options for the backup, click the Options tab. The available options are used as follows:
• Verify Backup Upon Completion  Reads the entire backup and checks for errors.
• Eject Tape After Backup  Set to eject the tape after the backup (only valid with tape devices).
• Remove Inactive Entries From The Transaction Log  Cleans out entries that are no longer needed after the backup. These entries are for transactions that have been committed or rolled back. (Set by default for transaction log backups.)
TIP:
You'll usually want to perform one last log backup before trying to restore a corrupt database. When you do, you'll want to clear the option and perform the log backup without truncation. This option is the same as running BACKUP LOG NO_TRUNCATE.
• Check Media Set Name And Backup Expiration  Ensures that you're writing to the correct tape set and that the tape expiration date has not been met.
• Backup Set Will Expire On  When backing up to a tape device, you can check this option and then set an expiration date. This option allows the backup to overwrite the media after a specified date or period.
• Initialize And Label Media  When backing up to tape devices, you can use this option to erase the previous contents of the media and then add a new label and description.
10. Click OK to start the backup or confirm that you want to schedule the backup. For an immediate backup, you'll see the Backup Progress dialog box, which displays a progress bar as the backup proceeds. If you opted to verify the data, the verification process starts immediately after the backup ends.

Adding to an Existing Backup Set

When you want to add to an existing backup set, complete the following steps:

1. Select the server you want to work with and then, from the Tools menu, choose Backup Database. This opens the SQL Server Backup dialog box shown previously in Figure 6-2.
2. Use the Database drop-down list box to select the database you want to back up.
3. In the Backup area, use the option buttons to select the type of backup you want to perform:
• Database - Complete
• Database - Differential
• Transaction Log
• File And Filegroup
4. A backup set should be listed in the Destination area. If one isn't, click Add to display the Choose Backup Destination and then enter the location of the existing backup. Click OK when you're ready to continue.
5. If you want to add data to the existing backup set, select the Append To Media option button.
REAL WORLD:
Whether you back up to a tape or disk drive, you should use the tape rotation philosophy. Create multiple tape sets and then write to these sets on a rotating basis. With a disk drive, for example, you could create these backup files on different network drives and use them as follows:

//omega/data1drive/backups/cust_set1.bak  Used on week 1, 3, 5, and so on for complete and differential backups of the customer database.

//omega/data2drive/backups/cust_set2.bak  Used on week 2, 4, 6, and so on for complete and differential backups of the customer database.

//omega/data3drive/backups/cust_set3.bak  Used on the first week of the month for complete backups of the customer database.

//omega/data4drive/backups/cust_set4.bak  Used on the first week of the quarter for complete backups of the customer database.

Don't forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups on week 1. Then, when starting the next rotation on week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.

6. Click the Options tab to double-check the backup options. For transaction log backups, you'll usually want to select the Remove Inactive Entries From Transaction Log check box. This ensures that inactive entries are cleared out of the transaction log after a backup.
7. To schedule the backup, click Schedule and then configure the backup schedule as discussed in Chapter 12, "Database Automation and Maintenance."
8. Click OK to start the backup or to confirm that you want to schedule the backup. For an immediate backup, you'll see the Backup Progress dialog box, which displays a progress bar as the backup proceeds. If you opted to verify the data, the verify process starts immediately after the backup ends.

### Using Striped Backups with Multiple Devices

Through a process called parallel striped backups, SQL Server can perform backups to multiple backup devices simultaneously. As you can imagine, writing multiple backup files at the same time can dramatically speed backup operations. The key to this speed, however, lies in having physically separate devices, such as three different tape devices or three different drives that you're using for the backup. You can't write parallel backups to a single tape device and you can't write parallel backups to the same drive.

Multiple devices used in a backup operation are referred to as a media set. SQL Server allows you to use from 2 to 32 devices to form the media set. These devices must be of the same type. For example, you can't create a striped backup with one backup tape device and one backup drive device.

The two main operations you'll perform are

• Creating a new media set
• Adding to an existing media set

Creating a New Media Set

To create a new media set using multiple devices, complete the following steps:

1. Select the server you want to work with and then create each of the backup devices you need in the media set as described in the section of this chapter entitled "Creating a Backup Device."
2. Open the SQL Server Backup dialog box by going to the Tools menu and choosing Backup Database. (You can't create a multiple device backup with the Backup Wizard.)
3. Follow the steps outlined in the section of this chapter entitled "Creating a New Backup Set." Repeat step 7 for each backup device you want to use in the media set.

Adding to an Existing Media Set

To add to an existing media set, complete the following steps:

1. Open the SQL Server Backup dialog box by going to the Tools menu and choosing Backup Database. (You can't create a multiple backup with the Backup Wizard.)
2. Follow the steps outlined in the "Adding to an Existing Backup Set" section of this chapter. The only change is that in step 4 you should see a list of all backup devices used in the media set. If you don't, you'll need to add them one by one using the Add button and the related Choose Backup Destination dialog box.

### Transact-SQL Backup

An alternative to the backup procedures in Enterprise Manager is to use Transact-SQL BACKUP. You'll use BACKUP DATABASE to back up databases and BACKUP LOG to back up transaction logs.
TIP:
If you back up databases using Transact-SQL, you lose one of the biggest benefits of SQL Server-the automated recovery process. With automated recovery, you don't have to worry about which backup to apply when, which command flags to use when, and more. Furthermore, because you can schedule automated and unattended backups, you really don't need to run backups manually through SQL as often as you used to. So I recommend using the Enterprise Manager backup and restore process whenever possible.

BACKUP DATABASE has dual syntax. Listing 6-2 shows the syntax and usage for complete and differential backups. A complete backup is the default operation.

Listing 6-2. BACKUP DATABASE Syntax and Usage for Complete and Differential Backups

Syntax
BACKUP DATABASE {database_name | @database_name_var}

TO <backup_device> [,...n]

[WITH

[BLOCKSIZE = {blocksize | @blocksize_variable}]

[[,] DESCRIPTION = {text | @text_variable}]

[[,] DIFFERENTIAL]

[[,] EXPIREDATE = {date | @date_var}

| RETAINDAYS = {days | @days_var}]

[[,] FORMAT | NOFORMAT]

[[,] {INIT | NOINIT}]

[[,] MEDIADESCRIPTION = {text | @text_variable}]

[[,] MEDIANAME = {media_name | @media_name_variable}]

[[,] [NAME = {backup_set_name | @backup_set_name_var}]

[[,] {NOSKIP | SKIP}]

[[,] [RESTART]

[[,] STATS [= percentage]]

]

Usage
USE master

EXEC sp_addumpdevice 'disk', 'Customer Backup Set 1', 'f:\data\backup\Cust2.dat'

BACKUP DATABASE 'Customer' TO 'Customer Backup Set 1'

Listing 6-3 shows the BACKUP DATABASE syntax for file and file group backups.

Listing 6-3. BACKUP DATABASE Syntax and Usage for File or Filegroup Backups

Syntax
BACKUP DATABASE {database_name | @database_name_var}

<file_or_filegroup> [,...n]

TO <backup_device> [,...n]

[WITH

[BLOCKSIZE = {blocksize | @blocksize_variable}]

[[,] DESCRIPTION = {text | @text_variable}]

[[,] EXPIREDATE = {date | @date_var}

| RETAINDAYS = {days | @days_var}]

[[,] FORMAT | NOFORMAT]

[[,] {INIT | NOINIT}]

     [[,] MEDIADESCRIPTION = {text | @text_variable}]

[[,] MEDIANAME = {media_name | @media_name_variable}]

[[,] [NAME = {backup_set_name | @backup_set_name_var}]

[[,] {NOSKIP | SKIP}]

[[,] [RESTART]

[[,] STATS [= percentage]]

]

Usage
USE master

EXEC sp_addumpdevice 'disk', 'Customer Backup Set 1', 'f:\data\backup\Cust2.dat'

BACKUP DATABASE Customer

FILE = 'Customer_data',

FILEGROUP = 'Primary',

FILE = 'Customer_data2',

FILEGROUP = 'Secondary'

TO 'Customer Backup Set 1'

Listing 6-4 shows the syntax for BACKUP LOG. By default, this command truncates the log after the backup.

Listing 6-4. BACKUP LOG Syntax and Usage

Syntax
BACKUP LOG {database_name | @database_name_var}

[WITH

{ NO_LOG | TRUNCATE_ONLY }]

TO <backup_device> [,...n]

[WITH

[BLOCKSIZE = {blocksize | @blocksize_variable}]

[[,] DESCRIPTION = {text | @text_variable}]

[[,] EXPIREDATE = {date | @date_var}

| RETAINDAYS = {days | @days_var}]

[[,] FORMAT | NOFORMAT]

[[,] {INIT | NOINIT}]

[[,] MEDIADESCRIPTION = {text | @text_variable}]

[[,] MEDIANAME = {media_name | @media_name_variable}]

[[,] [NAME = {backup_set_name | @backup_set_name_var}]

[[,] NO_TRUNCATE]

[[,] {NOSKIP | SKIP}]

[[,] [RESTART]

[[,] STATS [= percentage]]

]

Usage
USE master

'f:\data\backup\Cust_log.dat'

BACKUP LOG Customer

TO Customer_log1


### Performing Transaction Log Backups

Transaction logs are essential to the timely recovery of SQL Server databases. Unlike database backups, which can be complete or differential, transaction log backups are usually incremental. This means that each transaction log backup has a record of transactions only within a certain time frame. Transaction logs are always applied in sequence-with the completion time of the last complete or differential backup marking the beginning of a transaction log sequence.

Consequently, in order to restore the database you must apply each transaction logon sequence up to the point of failure. For example, if you run a complete backup at 1 p.m. and the database fails at 1:46 p.m., you would restore the last complete backup and then apply each transaction log created after that time, such as the backup at 1:15 p.m., 1:30 p.m. and 1:45 p.m. As you can see, without the incremental transaction log backups, you would lose all the transactions that took place after the 1 p.m. complete backup.

You can perform transaction log backups like any other backup. Still, there are a few details that you should know beforehand and these details are covered in the sections that follow.

Options and Commands that Invalidate Log Sequences

Although the normal backup process for transaction logs is fairly straightforward, SQL Server throws a few curveballs by providing option flags that you can set for the backup or the database, or both. The following database options prevent you from using a transaction log sequence to recover a database:

• Truncate Log On Checkpoint  A database option that clears out inactive entries in the transaction log on checkpoint.
• Using Non Logged Operations  Commands that bypass the log invalidate a log backup sequence.
• ALTER DATABASE  Adding or deleting files with ALTER DATABASE invalidates a backup sequence.
TIP:
As I stated earlier, the completion time of the last complete or differential backup marks the beginning of a transaction log sequence. If you use any of the previous commands and invalidate a log sequence, perform a complete or differential backup to start a new sequence.

Log Truncation Options

When you back up transaction logs, you have several options that determine how the backups are made. With SQL Server Backup in Enterprise Manager, you can use the Remove Inactive Entries From The Log option. Setting this option clears committed transactions out of the log after a log backup. The BACKUP LOG command normally clears out committed or aborted transactions after a log backup as well. However, you can override this behavior with these options:

• TRUNCATE_ONLY  Removes inactive entries from the log without creating a backup. This invalidates the log sequence.
• NO_LOG  Same as TRUNCATE_ONLY but doesn't log the BACKUP LOG command in the transaction log. This option is designed for a situation where the transaction log or its home drive is full, and you must truncate the log without writing to the log device.
• NO_TRUNCATE  Writes all the transaction log entries from the last backup to the point of failure. Use this option when the database is corrupt and you're about to restore it.

TIP:
After you use TRUNCATE_ONLY or NO_LOG, always perform a complete or differential backup. This revalidates the log sequence. Additionally, because you can grow logs automatically, you should rarely encounter a situation where you need to truncate the log without logging. The log can only run out of space if you set a maximum size or the drive(s) the log uses runs out of space....

## Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

(0)

(0)

(0)

(0)

### 1 Star

(0)

Your Name: Create a Pen Name or

### Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

### Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

### What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

### Reviews should not contain any of the following:

• - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
• - Time-sensitive information such as tour dates, signings, lectures, etc.
• - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
• - Comments focusing on the author or that may ruin the ending for others
• - Phone numbers, addresses, URLs
• - Pricing and availability information or alternative ordering information

### Reminder:

• - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
• - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
Search for Products You'd Like to Recommend

### Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?