Backups and Maintenance
Backups and
Maintenance
Objectives
• Learn how to create a backup strategy to suit your needs. • Learn how to back up a database.
• Learn how to restore from a backup.
Creating a Backup Strategy
In the real world, data can be lost in many different ways and through all kinds of disasters from power spikes to fires and floods, viruses, incompetent or malicious users, corruption or other damage to the physical disk device, and theft. You need to plan in advance how you are going to handle any of these situations should they arise, because it is too late after the data is lost. Backing up and restoring both system databases and user databases is an important part of administering a SQL Server. The Database Maintenance Plan Wizard (covered later in this chapter) makes setting up regular backups very easy. But you have to have an idea of where you’re going with a backup strategy before you start.
Asking the Right Questions
Part of designing your backup strategy involves asking a couple of questions, the answers to which may vary on a database-by-database basis:
• How much data loss is acceptable?
• How long can I afford for the system to be down in the event of system failure?
A sound backup strategy allows you to restore data with minimal downtime and with only the amount of data loss that is acceptable to the business. You need to design a backup strategy that balances the amount of time it takes to back up and restore against the acceptable amount of data loss.
NOTE SQL Server also supports standby servers and failover servers when absolutely no data loss or downtime is acceptable. A standby server holds a second copy of a database that is automatically and periodically refreshed with transactions from the primary server. A standby server can be quickly activated if the primary server fails. A failover server is a second server that shares the same physical hard drive with the primary server, via Microsoft Clustering Services, and that will be instantly activated if the primary server fails.
Creating a Backup Strategy
Understanding the SQL Server 2000
Recovery Models
SQL Server 2000 introduces the concept of recovery models: • Full Recovery model
• Bulk-Logged Recovery model • Simple Recovery model
These models provide different degrees and methods of recovering data in the event of server or disk failure.
Full Recovery
Use the Full Recovery model when full recovery or point-in-time recovery is necessary. SQL Server 2000 also supports the insertion of named marks into the transaction log to allow recovery to a specific mark, but this consumes log space and should be used sparingly. The Full Recovery model copies all database and log information. All committed transactions are recovered using this model. The Full Recovery model consumes the most disk space, and can take the most time to restore. However, your data loss exposure is limited to uncommitted transactions.
Bulk-Logged Recovery
The Bulk-Logged Recovery model is similar to Full Recovery, but uses less log space. The details of certain operations, such as CREATE INDEX, bulk copy or bulk load operations, or working with BLOB (binary large object) data are not stored in the log, only noted that they occurred. The benefit of this recovery model is that the log is smaller and bulk operations run faster. The downside is that you can’t restore part of a backup, to a point in time or to a mark.
Simple Recovery
Log Shipping
A new feature of the SQL Server 2000 Enterprise Edition is log shipping, which feeds transaction logs continuously from one database to another. This keeps the destination database synchronized with the source database, allowing you to have a backup server always at the ready. The destination server can also be used to offload data-retrieval query processing from the source server. Log shipping requires the full recovery model and is only available with the Enterprise Edition of SQL Server.
Try It Out!
You can set or change the database recovery model using the Enterprise Manager by following these steps:
1. Expand the Databases node and right-click on the Shark database. Choose Properties from the menu.
Creating a Backup Strategy
Figure 1. Setting the database recovery model to Full.
3. Click OK when finished.
Changing the Recovery Model Using Transact-SQL
To change the recovery model using Transact-SQL, use the ALTER DATABASE syntax. The three constants used with the SET RECOVERY statement are FULL, SIMPLE, and BULK_LOGGED. The following Transact-SQL statement sets the Shark database to use the bulk logged recovery model:
ALTER DATABASE Shark SET RECOVERY BULK_LOGGED
Analyzing Your Needs
It can be difficult to analyze your needs, particularly if the database is large or complex. You need to analyze your business requirements and your
environment and then test out your backup and restore scenario to see which balance of “data loss” vs. “time consumed performing backups and restore” scenario is acceptable. When analyzing your needs, ask yourself the following questions:
• How large is the database you need to back up? The more data you have, the more system resources will be consumed when performing backups.
• Does the data change very often? If so, you’ll need more frequent backups and may need full recovery rather than simple.
• Are some tables modified more often than others? You may want to put some tables in separate file groups that get backed up more
frequently.
• How much data can your business afford to lose? An hour’s worth? A day’s worth? Could the lost data be recreated easily? This will affect the frequency of your backups and the model you choose.
• Is it important to never lose a single change? If so, full recovery is your best option.
• Is there any particular time during production that is more critical than other times? Is the database more heavily used during certain time periods? Try to schedule backups during off-peak hours, if possible.
• Do users need to access the database during backup operations? If so, then more frequent backups will be needed to capture transactions that were uncommitted during the previous backup.
Creating a Backup Strategy
The time it takes to back up and restore depends a lot on whether you use tape or disk to store the backups. You need to consult your device documentation to calculate the time it’s going to take to back up your entire database. You will probably need to perform timing tests to figure out how long backing up and restoring is going to take since hardware and network configurations vary widely. Bear in mind that system performance may be noticeably worse while a backup is in progress.
Test the Plan
Once you have decided on a plan that is suitable for your database, test and time it thoroughly. A backup is no good if you can’t recover from it quickly and efficiently. You need to simulate failures and test your restoration procedures for any weaknesses. You also need to determine whether the frequency of backup is adequate for your needs and how long it takes to restore, then adjust your strategy accordingly.
Backing Up User Databases
You need to start performing database backups as soon as a database is created and loaded with data. You can’t restore transaction log backups if you don’t have a database backup to apply the log to.
A database backup should also follow other types of operations:
• Creating indexes. This ensures that the database backup contains the data and index structures. The transaction log will only contain the fact that the index was createdif it’s not in the full database backup, then it will need to be rebuilt, which consumes an excessive amount of time during a restore.
• Clearing the transaction log. You won’t be able to recover any changes from that point forward, unless you back up the database.
• Any non-logged operation, such as a SELECT…INTO statement creating a permanent table, running bcp (bulk copy program), or executing WRITETEXT or UPDATETEXT statements. If the system goes down after one of these operations, you won’t be able to recover the database fully to a consistent state, so a database backup at this point may be necessary.
Backing Up the System Databases
following databases should be backed up based on the frequency with which they are used.
master
If the master database is damaged due to media failure, then SQL Server may not be able to start. It should be backed up after any procedure that changes information in the system tables in the master database. For example, if you create a new database after backing up the master, when the master is restored the new user database won’t exist because there won’t be any entries in the systems tables in the restored master for the new database.
TIP: If you need to rebuild the master database, use the rebuildm command prompt utility by running it from 80\Tools\Binn\Rebuildm.exe, which rebuilds all of the system databases, including the master. These new system databases will be in the same state they’d be in if you performed a fresh installation of SQL Server.
msdb
The msdb database stores data such as jobs, alerts, and operators. If msdb can’t be restored then you’ll have to manually recreate each job, alert, and operator.
model
Implementing Backups
Implementing Backups
You can back up databases by using Transact-SQL commands, or by using the Enterprise Manager’s graphical tools. This section covers how backups work and how to implement them.
How Database Backups Work
There are two types of database backups:
• Complete: When you back up a database, all of the data in the database gets backed up regardless of whether or not it was changed since the last database backup. Each database backup is self-contained and does not rely on any other backup medium to be restored.
Consequently, it also uses the most storage space and takes the most time both to back up and restore. When you restore from a database backup, the database is recreated exactly as it was when the backup operation was completed.
• Differential: A differential database backup only backs up data that has changed since the last full database backup, and is a good choice when backing up the database would take too long.
SQL Server allows you to perform database backups while users are still working in the database. The backup contains the database files, locations, schema and file structure, and the data and portions of the transaction log. All this is necessary for SQL Server to be able to restore a database to its original state. However, the following operations are not allowed while a backup is in progress:
• Creating or altering databases • Creating indexes
• Performing non-logged operations
A combination of complete and differential database backups is recommended for databases running under the Simple Recovery model.
Backup Locations and Devices
You can perform backups to the following locations:
• A file on a hard disk. This is the quickest and easiest method. • A tape. If you choose tape, the tape drive must be attached to the
machine SQL Server is installed on.
• A named pipe. Backing up to a named pipe allows you to use third-party backup software.
A backup device is a permanent file or location that you designate for your backups. The device can use a disk file, tape, or named pipe.
Devices are used to automate the task of backing up and restoring your database. However, you’re not required to create a backup device if you only need to back up a database as a one-time-only operation.
Try It Out!
Follow these steps to create a new backup device using the Enterprise Manager:
1. Expand the Management node in the main console. Right-click on the Devices node and choose New Backup Device from the menu. This loads the Backup Device Properties dialog box.
2. Fill in the properties for the backup device. It should never be located in the same location as the database data files. Figure 3 shows
specifying a file device for the Shark database named Shark.BAK.
Implementing Backups
3. Click OK when finished.
Backing Up a Database
There are several different ways to back up a database from the Enterprise Manager, all of which load the SQL Server Backup dialog box:
• Right-click on the Backup icon in the Management folder and choose Backup a Database.
• Or, right-click on the database you want to back up, and choose All Tasks|Backup Database.
• Or, from the Main Console menu, choose Tools|Backup Database. Any of these methods will load the Backup dialog box, shown in Figure 4. The Backup to option displays the device name created in the previous section.
Note the four different backup types you can perform:
• Database – complete. This backs up the entire database.
• Database – differential. This backs up only the parts of the database that were changed since the last complete backup. Differential backups have smaller backup sets and a faster restore time.
• Transaction log. This backs up only the transaction log changes since the last log backup. Transaction logs cannot be restored unless they have a corresponding database backup to build upon.
• File and filegroup. This backs up only the specified file or filegroup. This is useful where you have a very large database that is partitioned into multiple files or filegroups. Backing up the entire database at once may not be an option because it would take too long. You can set up a schedule to back up each file on a rotating basis.
The Schedule option allows you to set up a schedule, as shown in Figure 5.
Implementing Backups
If you do not set up a schedule, the backup will be performed when you click the OK button in the main dialog box. The Options tab, shown in Figure 6, allows you to set up additional options, such as verifying the backup on completion. Click OK to create the backup.
When the Transaction Log Becomes Full
Restoring from a Backup
Restoring from a Backup
Whenever SQL Server fails or shuts down, and is then restarted, it will start its own automatic internal recovery process. It ensures that your database is consistent by examining the transaction log from the last checkpoint to the point that SQL Server failed or shut down. Any committed transactions in the transaction log are rolled forward and written to the database. Any
uncommitted transactions are rolled back so that your data is guaranteed to be in a consistent state.
What Happens During a Restore?
When you restore a database, SQL Server performs a safety check that prevents you from accidentally overwriting a database with a backup from a different database. For example, you won’t be able to overwrite Northwind with your Shark backup by mistake. It will also not restore in the following situations:
• If the database files on the server are different from the database files in the backup set
• If some of the files needed to restore a database are missing SQL Server recreates the original database files and places them in the
locations that they were in when they were backed up. All database objects are recreated automatically and all of the data is restored.
Try It Out!
Follow these steps to restore a database from a backup:
2. Delete the Shark database by selecting it in the Enterprise Manager and clicking the Delete key. This loads the dialog box shown in Figure 7. Make sure that the Delete backup and restore history for the database checkbox is cleared. Click Yes.
Figure 7. Clear the Delete backup and restore history for the database checkbox.
3. Note that you won’t be able to delete it if there are any open connections pointing to the Shark database.
Restoring from a Backup
5. Type in Shark in the Restore as database textbox and select Shark in the Show backups of database listbox. This displays the backup you created earlier, as shown in Figure 8.
6. On the Options tab, set the option to leave the database operational, as shown in Figure 9. Click OK when finished. This restores the Shark database in its entirety and rolls back any uncommitted transactions.
Figure 9. Setting the option to leave the database operational.
If you wanted to follow this operation with restoring a transaction log, then you would select one of the other recovery completion state options, both of which leave any uncommitted transactions in place. Then you could restore from a transaction log backup (or from a series of log backups) and recover data changes that occurred since the database backup. This use of transaction log backups is also necessary if you want to restore to a particular point in time. When performing a recovery of the latest transaction log, select the first recovery completion state, as shown in Figure 9, to leave the database
The Database Maintenance Plan Wizard
The Database Maintenance Plan
Wizard
Although the backup and restore dialog boxes are handy, the Database Maintenance Plan Wizard allows you to set up a maintenance plan that encompasses a lot, including these operations:
• Run database integrity checks. • Update database statistics.
• Perform and schedule database backups.
• Ship transaction logs to another server (log shipping is available in the Enterprise edition only).
You need to run the wizard to create a maintenance plan, but once created, you can modify it by using the Enterprise Manageryou only need to run the wizard once to get it started.
Try It Out!
Follow these steps to set up a new database maintenance plan: 1. Right-click on the Database Maintenance Plans icon in the
2. Select the database you wish to create the maintenance plan for. Figure 10 shows selecting the system databases. Click Next when finished.
The Database Maintenance Plan Wizard
3. Next select the data optimizations you want performed, as shown in Figure 11. Data and index pages can be reorganized, making them more efficient. Unused space can be removed from database files. For user databases, you can also choose to update statistics. Click the Change button to modify the default schedule and click Next to continue.
4. Select the database integrity options you want checked, and whether they should be performed before backups (see Figure 12). Click the Change button to alter the default schedule, and Next to continue.
The Database Maintenance Plan Wizard
5. The next dialog box is for the backup itself, as shown in Figure 13. Click the Change button to alter the default schedule and the Next button to continue.
6. Specify the location of the backup files, which should be on a different device from the main data files, as shown in Figure 14. Note than you can also specify removing files older than a certain time frame. This is to keep the backups from overflowing the available disk space. Click Next.
The Database Maintenance Plan Wizard
7. The next dialog box is for backing up the transaction log, as shown in Figure 15. For the system databases, this isn’t necessary. For user databases, you’d want to select the Full Recovery model for this option to make sense. If your database employs the Simple Recovery model, there won’t be much log to back up, because the log will not retain information on transactions that have already been committed. Using a full recovery model, you could schedule one or more transaction log backups to occur in between database backups. Click Next to continue.
8. The next dialog box (see Figure 16) allows you to write a report to a text file or send an alert to an operator, or to document the success or failure of the maintenance operations. Click Next to continue.
The Database Maintenance Plan Wizard
9. Specify the location of the maintenance plan records, as shown in Figure 17. You can store them on the local server, or on a remote server. Click Next when finished.
10. The final wizard dialog box (see Figure 18) displays the final screen. Name the plan and click the Finish button, and the plan will be created with all the specified options.
The Database Maintenance Plan Wizard
Once the maintenance plan is completed, it can be easily modified. Right-click on the plan name in the Database Maintenance Plans folder and choose Properties from the menu. This loads the Properties dialog box as shown in Figure 19, with all of the pages that you went through using the wizard, displayed on separate tabs. Make any desired changes and click Apply or OK.
Summary
• SQL Server 2000 supports three recovery models. • Use Bulk-Logged Recovery to save on log space.
• Change the recovery model in the Database Properties dialog box. • Use Simple Recovery when only complete database backups are
needed.
• Use Full Recovery for both database and log backups.
• The Enterprise Manager allows you to create backups and restore databases.
• Back up the systems databases whenever any changes are made to them.
• Differential backups can be used to back up only changes since the last complete database backup.
• Transaction log backups record the full history of changes allowing you to restore to a point in time.
• File and filegroup backups are useful for partitioning backups of very large databases.
The Database Maintenance Plan Wizard
Questions
1. What are the three recovery models available in SQL Server 2000? 2. For what type of application would you want to back up the transaction
log?
3. Can you recover to a point in time with the Simple Recovery model? 4. What are backup devices used for?
5. What is the difference between a complete database backup and a differential database backup?
Answers
1. What are the three recovery models available in SQL Server 2000? Simple, Full, and Bulk-Logged
2. For what type of application would you want to back up the transaction log?
For any application with a high volume of changes and where any data loss is unacceptable.
3. Can you recover to a point in time with the Simple Recovery model? No, because the transaction logs are not maintained with a full history of data changes.
4. What are backup devices used for?
Backup devices are used for scheduling backups. It is not necessary to create a device for a one-time backup.
5. What is the difference between a complete database backup and a differential database backup?
A complete database backup saves the entire database, and a differential database backup only records the portions of the database that have been changed since the last complete database backup.
6. Name two maintenance tasks that the Database Maintenance Plan Wizard helps you configure.
The Database Maintenance Plan Wizard
Lab 10:
Lab 10 Overview
In this lab you’ll learn how to backup and restore a database and to create a database maintenance plan.
To complete this lab, you’ll need to work through two exercises: • Backup and Restore a Database
• Create a Database Maintenance Plan
Each exercise includes an “Objective” section that describes the purpose of the exercise. You are encouraged to try to complete the exercise from the
Backup and Restore a Database
Backup and Restore a Database
Objective
In this exercise, you’ll create a backup of the Northwind database. You’ll then delete it and restore the original from the backup.
Things to Consider
• Are there any open connections to the Northwind database? If so, you’ll need to close them.
• How do you create a full database backup? • How do you restore from a backup?
Step-by-Step Instructions
1. In the Enterprise Manager, right-click on the Northwind database and choose All Tasks|Backup Database from the menu.
2. Name the backup Northwind Backup and select Database – complete from the menu.
3. Click the Options tab and select the Verify backup on completion option. Click OK to create the backup.
5. To restore Northwind, right-click on the Databases node in the Enterprise Manager and choose All Tasks|Restore Database from the menu.
6. Type in Northwind in the Restore as database textbox and select Northwind Backup in the Show backups of database listbox. This displays the backup you created earlier in this exercise.
Create a Database Maintenance Plan
Create a Database Maintenance Plan
Objective
In this exercise, you’ll create a database maintenance plan to back up all user databases.
Things to Consider
• How do you create a new database maintenance plan? • Which options are appropriate for user databases?
• Do you need to backup the transaction log as well as the database?
Step-by-Step Instructions
1. Right-click on the Database Maintenance Plans icon in the
2. Select the All user databases option as shown in Figure 21. Click Next when finished.
Figure 21. Choosing the user databases.
3. Next select all of the data optimizations for reorganizing data and index pages and removing unused space. Choose to update statistics. Click the Change button to modify the default schedule and click Next to continue. 4. Select all the check database integrity options, and have them performed
before backups. Click the Change button to alter the default schedule, and Next to continue.
5. Choose to have the database backed up as part of the plan, and to verify the backup on completion. Click the Change button to alter the default schedule and the Next button to continue.
6. Specify the location of the backup filesyou can accept the defaults for this exercise. Remove files older than four weeks. Click Next to continue. 7. Because you are backing up user databases, the transaction logs may also
Create a Database Maintenance Plan
8. Write a report to the default file location and clear the Send e-mail to operator checkbox. If necessary, use the New Operator button to create an operator you can send e-mail to. Click Next to continue.
9. Accept the default location for the maintenance plan records. Click Next when finished.
10. Name the maintenance plan User Databases, and click the Finish button. The plan will be created with all the specified options.
11. Locate the plan in the Management folder. Right-click and choose Properties from the menu. This loads the properties for the plan you just created, as shown in Figure 22. Select the various tabs to see how the options you selected using the wizard are organized. Click OK or Cancel when finished.
