With each release of the software Microsoft has made great strides in improving their primary database platform. SQL Server 2005 is a true enterprise class database platform capable of supporting scalability and availability needs of the largest and most complex databases. Not the least of database availability improvements introduced with SQL Server 2005 is the piecemeal database restore functionality I will discuss in this tip.
SQL Server backup types
Before I talk about backup and restore functionality improvements with SQL Server 2005 let me discuss the three main types of backups available with all versions of software:
- Full backup – makes a copy of all data pages. A data "page" is basically a storage unit.
- Differential backup – makes a copy of only those data pages that have changed since the last full backup.
- Transaction log backup – makes a copy of all transactions that occurred since the last full, differential or transaction log backup.
A typical backup strategy takes advantage of the combination of these three types of backups. The full backups are the simplest; they give you a full snapshot of your data; your entire database can be restored using a single full backup file in a single step. Unfortunately full backups are not practical to for very large databases. If a full backup of your database takes 23 hours to complete you cannot afford to run such backups daily. Typically you'd try to create a full backup daily, weekly or monthly (depending on the size of your database) and supplement these with transaction log and differential backups.
Differential backups are convenient because they only make a copy of those data pages that have changed since the last full backup. Therefore they are considerably smaller than full backups. Note however, that a differential backup does not copy the pages changed since the last
differential backup – as many people mistakenly think; rather it copies all pages changed since the last FULL backup. So if you take a full backup on Sunday and then run differential backups during the week then Monday's differential backup will be smaller than or equal size of all other weekdays' differential backups. To restore a database you will need both full and differential backup files.
Transaction log backups take significantly less time to create than full or differential backups; they are also considerably smaller. On the other hand, to restore a database using transaction log backups you must have a full backup, the last differential backup (if you use differential backups at all) and all transaction log backups taken since the last full or last differential backup.
Filegroup backups
In addition to backing up the entire database you can backup individual files and file groups. A file group is simply a collection of data files. Transaction log files do not belong to any file groups. Each database contains a primary file group. In addition you can create one or more secondary file groups. As the best practice it is recommended that you place all user-defined (non system) objects into secondary file groups. The primary file group should be reserved for only system objects.
With previous versions of the software you could use file group backups to effectively shorten your backup window. You could backup a subset of the database much quicker than it would take to backup the entire database. However, when recovering a database you'd normally have to restore all file groups prior to making the database available to your users. The exception to this rule is the partial restore scenario which allows you to restore the primary file group and one or more secondary file groups.
The partial restore functionality with SQL Server 2000 has two drawbacks, however:
- It must be performed from a full database backup (you can specify which file groups you wish to choose from the full backup). You cannot use file group backups for performing the partial restore.
- Once you recover the subset of the database you cannot subsequently restore the rest of the database. As the name indicates a partial restore is meant for restoring a part of the database.
SQL Server 2005 extends the partial restore functionality with Piecemeal Restore. Piecemeal restore allows you to utilize file group level backups to restore each file group. Furthermore, as long as your primary file group is available users can resume their activities while the other file groups are being restored. Piecemeal restore enables you to recover the entire database as your schedule allows.
When using partial or piecemeal restore it is necessary to restore the primary file group first because it contains system level meta data; with previous versions meta data was stored in system tables, with SQL Server 2005 system tables (like syscolumns, sysindexes, etc) are implemented as views. They are also deprecated – for querying system meta data we should use dynamic management views instead. The secondary file groups can be restored in any order.
Please note that the piecemeal restore functionality is only supported by the Enterprise Edition of SQL Server 2005. The following section provides an example of how piecemeal restore is actually implemented.
SQL Server piecemeal restore
For our example scenario let's suppose we have a data warehouse that tracks sales data for transactions that occurred during each year from 1996 until now. Let's further suppose that all historical sales except ones that occurred within the current year are stored in the "history" file group. Historical data is static – we never change any records in that file group. As good database administrators who adhere to the best practices religiously we have placed all user-defined objects in secondary file groups and not in the primary file group. Since data in the "history" file group never changes we only back it up once a month. The "current" file group stores the sales for the current year and gets backed up daily.
One fine morning we get a phone call from the operation's department – database is down. Our server experienced some hardware issues and we need to recover the database to another server. The "current" and "primary" file groups are relatively small and could be restored within an hour. The "history" file group could take many hours to restore. Fortunately for us our users are mainly concerned about the recent transactions and only examine old data occasionally. So we decide to use the piecemeal restore functionality to make the database available to the users ASAP.
Let's suppose that we had created our data warehouse using statements similar to the following:
CREATE DATABASE [TeraWarehouse] ON PRIMARY
( NAME = N'TeraWarehouse', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\TeraWarehouse.mdf' , SIZE = 2048KB , MAXSIZE
= UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [Current]
( NAME = N'CurrentData', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\CurrentData.ndf' , SIZE = 2048KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [History]
( NAME = N'OldData', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\OldData.ndf' , SIZE = 2048KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TeraWarehouse_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TeraWarehouse_log.ldf' , SIZE = 1024KB ,
MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
Since no one is permitted to update data in the historical file group we mark it as read-only using the following command:
ALTER DATABASE [TeraWarehouse] MODIFY FILEGROUP [History] READONLY
Let's suppose we have created backups as follows:
1. Primary and current file group backups the evening prior to the hardware failure:
BACKUP DATABASE TeraWarehouse
FILEGROUP = 'primary'
TO DISK = 'c:\PrimaryBackup.bak'
BACKUP DATABASE TeraWarehouse
FILEGROUP = 'Current'
TO DISK = 'c:\CurrentBackup.bak'
Historical file group was backed up at the beginning of the month using a similar statement:
BACKUP DATABASE TeraWarehouse
FILEGROUP = 'History'
TO DISK = 'c:\HistoryBackup.bak'
2. Transaction log backup the night before, and morning of the hardware failure:
BACKUP LOG TeraWarehouse
TO DISK = 'c:\TeraWarehouse_log_52507_2200.trn'
BACKUP LOG TeraWarehouse
TO DISK = 'c:\TeraWarehouse_log_52607_0600.trn'
Since our database hosts a data warehouse we only take transaction log backups after each data load. No other transactions besides data transformation and loading are permitted. We load data from transactional sources every eight hours.
We get the glorious call from the operation's department at 6:10AM and need to bring the current data online as soon as possible. We will proceed as follows:
1. First restore the primary file group; note that we'll use the PARTIAL keyword to advise SQL Server that not all file groups will be restored:
RESTORE DATABASE TeraWarehouse_restored
FROM DISK = 'c:\PrimaryBackup.bak'
WITH NORECOVERY, PARTIAL,
MOVE N'TeraWarehouse' TO
N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\TeraWarehouse_restored.mdf' ,
MOVE N'TeraWarehouse_log' TO
N'C:\Program Files\Microsoft SQL>
Server\MSSQL.1\MSSQL\DATA\TeraWarehouse_log_restored.ldf'
2. Next let's restore the current file group. After this command the database still won't be fully recovered (or available) because we still need to restore transaction logs. If users attempt to query the database at this point they'll get an error that the database is in the middle of the restore:
RESTORE DATABASE TeraWarehouse_restored
FROM DISK = 'c:\CurrentBackup.bak'
WITH NORECOVERY,
MOVE N'CurrentData' TO
N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\CurrentData_restored.ndf'
3. Restore transaction log backups in the order they were created. The last transaction log backup will be restored using WITH RECOVERY option:
RESTORE LOG TeraWarehouse_restored
FROM DISK ='c:\TeraWarehouse_log_52507_2200.trn'
WITH NORECOVERY
RESTORE LOG TeraWarehouse_restored
FROM DISK ='c:\TeraWarehouse_log_52507_0600.trn'
WITH RECOVERY
At this point the database is online and users can resume querying it even though the historical file group has not been recovered. Mission accomplished, well, at least partly. If users attempt to query the objects in the historical file group they'll get a message similar to the following:
The query processor is unable to produce a plan for the table or view 'my_old_fact' because the table resides in a filegroup which is not online.
Sometime later, perhaps during limited user activity, we can go ahead and restore the offline file group as follows:
RESTORE DATABASE TeraWarehouse_restored
FROM DISK ='c:\HistoryBackup.bak'
WITH RECOVERY, MOVE N'OldData' TO
N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\HistoryData_restored.ndf'
Once this restore command succeeds users can query all data – historical as well as current.
In this tip I discussed the piecemeal restore scenario which can be extremely useful for enterprise scale databases, particularly for data warehouses where read-only historical data is segmented from somewhat volatile current data. Do yourself a favor and become familiar with this functionality – it may very well save your job one day.
| ABOUT THE AUTHOR: |
|
Baya Pavliashvili is a DBA manager overseeing database operations that support more than one million users. Pavliashvili's primary areas of expertise are performance tuning, replication and data warehousing.
Copyright 2007 TechTarget
|
|
