Archive for the ‘Database’ Category

Recovering a Database

In my post named “Database Recovery Techniques”, I talked about performing different kinds of backups but the recovery process was only in theory. Now I will see show you how to restore a database in different recovery scenarios.

Retrieving Backup Information

Before you can start restoring databases, you need to know which backups you need to restore. SQL Server stores backup history about every single backup performed on databases in the msdb database. The msdb database can be queried to find the backups to restore.

Retrieving Simple Backup Information

1. To get information about which backups have occurred on the AdventureWorks database, execute the following SELECT statement:

USE msdb
SELECT backup_start_date,type, physical_device_name,backup_set_id
FROM backupset bs inner join backupmediafamily bm
ON bs.media_set_id = bm.media_set_id
WHERE database_name =’AdventureWorks’
ORDER BY backup_start_date desc

The results pane shown below indicates that the most recent backup was of backup type I, a differential backup. As you know, to restore a differential backup, you first need to restore the most recent full database backup. This backup can be found on line 2, with type D indicating a full database backup.

2. Every backup gets a unique id called a backup set id that can be seen in the results window, as shown below:

Using the above information, you can find out which data and log files were affected by the backup and what their original locations were.

Restoring a Database Using SQL Server Management Studio

In many cases, the easiest way to restore a database is to use SQL Server Management Studio. SQL Server Management Studio uses the backup history stored in the msdb database to show us the best way to restore a database.

Performing a Restore Using SQL Server Management Studio

1. To restore the database, open Object Explorer by selecting Object Explorer in the View menu or by pressing the F8 key.

2. In the tree view shown, expand your SQL Server instance, open the Databases folder and right-click the AdventureWorks database. In the context menu, select Tasks | Restore | Database.

3. The Restore Database dialog box opens and you will see that the most recent backup sets are already selected for restoration, as shown in the below figure. To complete the restore, simply click the OK button.

Note: Make sure no connections are open to AdventureWorks because no connections to the database are allowed while a restore is in progress.

4. A message box should appear to inform you that the restore was successful.

Read Full Post »

Preventing data loss is one of the most critical issues involved in managing database systems. Data can be lost as a result of many different problems:

  1. Hardware failures
  2. Viruses
  3. Incorrect use of UPDATE and DELETE statements
  4. Software bugs
  5. Disasters, such as fire or flood

To prevent data loss, you can implement a recovery strategy for your databases.

Full Database Backups

A very common backup strategy is to back up the whole database in a predefined time series (once each night, for instance). With such a backup strategy, it is possible to recover a database to the state it had when the last backup occurred. This strategy is implemented by using full database backups. A full database backup contains all data and database Meta information needed to restore the whole database, including full-text catalogs. When you restore a full database backup, it restores all database files yielding data in a consistent state from the time the backup completed.

Using Differential Backups

The main advantage of a full database backup is that it contains all the data needed to rebuild the entire database. But this advantage can also be a disadvantage. Consider a database where full database backups are performed each night. If you need to recover the database, you always have to use the backup from the previous night, resulting in the loss of a whole day’s work. One way to reduce the potential period of time that can be lost would be to perform full database backups more often. But this itself can be a problem. Because all data and parts of the transaction log are written to the backup device, it can be very time-intensive to make a backup. Also, you need a lot of storage space to hold these backups, and a full backup can decrease the performance of your database as a result of the large amount of I/O it requires. Wouldn’t it be better to perform one full database backup at night and only take backups of data changes made during the day? This sort of functionality is provided by the differential backup.

The differential backup stores only the data changes that have occurred since the last full database backup. When the same data has changed many times since the last full database backup, a differential backup stores the most recent version of the changed data. Because it contains all changes since the last full backup, to restore a differential backup, you first need to restore the last full database backup and then apply only the last differential backup.

Performing Differential Backups

Performing a differential backup is very similar to performing full database backups. The only difference is that you state in the WITH option of the backup that you want to perform a differential backup. The syntax of the BACKUP DATABASE statement to perform a differential backup of AdventureWorks to a physical device, overwriting other existing backups on the backup device, is as follows:

USE master;
TO DISK=’t:\adv_diff.bak’

Using Transaction Log Backups

With the combination of full database and differential backups, it is possible to take snapshots of the data and recover them. But in some situations, it is also desirable to have backups of all events that have occurred in a database, like a record of every single statement executed. With such functionality, it would be possible to recover the database to any state required. Transaction log backups provide this functionality. As its name suggests, the transaction log backup is a backup of transaction log entries and contains all transactions that have happened to the database. The main advantages of transaction log backups are as follows:

  1. Transaction log backups allow you to recover the database to a specific point in time.
  2. Because transaction log backups are backups of log entries, it is even possible to perform a backup from a transaction log if the data files are destroyed. With this backup, it is possible to recover the database up to the last transaction that took place before the failure occurred. Thus, in the event of a failure, not a single committed transaction need be lost.

Combining Transaction Log and Differential Backups

Another possible backup strategy is to combine full database, differential, and transaction log backups. This is done when restoring all transaction log backups would take too much time. Because restoring from a transaction log backup means that all transactions have to be executed again, it can take a great deal of time to recover all the data, especially in large databases. Differential backups only apply data changes, which can be done faster than re-executing all transactions.

To recover a database when you have a combined backup strategy, you need to restore the last full database backup, the last differential backup, and then all subsequent transaction log backups.

The Full Recovery Model

As mentioned before, you need to tell SQL Server in advance which backup strategy you plan to implement. If only full database and differential backups are used, the database has to be set to the simple recovery model. If you also want to use transaction log backups, the recovery model must be set to FULL (or BULK_LOGGED). The full recovery model tells SQL Server that you want to perform transaction log backups. To make this possible, SQL Server keeps all transactions in a transaction log until a transaction log backup occurs. When the transaction log backup happens, SQL Server truncates the transaction log after the backup is written to the backup device. In simple mode, the transaction log is truncated after every checkpoint, which means that committed transactions (which are already written to the data files) are deleted from the transaction log. Thus, in simple mode, transaction log backups cannot be created.

To set the recovery model to FULL, use the ALTER DATABASE statement again. The following code sets the recovery mode of AdventureWorks database to FULL:

USE master;

To set recovery model to SIMPLE, use the following command
USE master;

Read Full Post »