Backup and Restore


SQL Server 2005 also sports several new features in the area of backup and restore technology. While many of the previous availability features were related to some of these new backup and restore features—particularly fine-grain online operations—this section focuses primarily on those changes that Microsoft has made to make the backup and restore process more robust and easier to implement.

Partial Restore

With SQL Server 2000, the database was not available during a restore operation. With SQL Server 2005, the database is online as soon as the primary filegroup is restored. Only the data being restored is unavailable. If a user accesses data that’s found in the primary filegroup, the operation will succeed with no indication to the user that the rest of the database is still being restored. If the user does attempt to access data from a filegroup that is currently being restored or that is still offline, then the database will return a message that the data is offline.

Media Reliability Enhancements

SQL Server 2005 also provides a number of enhancements in the way that it deals with media. In particular, it now allows backups to be performed to mirrored devices, it provides enhanced checksum integrity, and it enables a restore operation to continue even if errors are encountered.

Backup Media Mirroring

One of the new media reliability features found in SQL Server 2005 is the ability to support media mirroring. Media mirroring enables you to simultaneously perform a backup to multiple backup devices. For instance, you can back up your database to both tape1 and tape2 at the same time, obtaining two identical copies of the backup set. Redundant backup media provide an important safeguard that can help protect your organization from media errors and help to ensure the ability to perform a successful restore. The following example illustrates using the new media mirroring feature:

BACKUP DATABASE AdventureWorks TO  TAPE='\\.\tape1' MIRROR TO  TAPE='\\.\tape2' WITH FORMAT, MEDIANAME = 'ADWBackup'

This example shows the AdventureWorks database being backed up to tape1 and mirrored to tape2. Performance of the backup is not affected by adding a backup mirror. Up to four mirrors can be applied to a single backup.

Improved Verification of Backups

With SQL Server 2000, using the RESTORE VERIFYONLY command just caused SQL Server to read the tape without going any farther into the restore process. Using the RESTORE VERIFYONLY command in SQL Server 2005 does everything short of actually restoring the data in the restore media, giving you a much better idea of the viability of the data in the backup set.

Continue Past Restore Errors

Another new media reliability enhancement is the ability of the restore operation to continue past media errors. Prior versions of SQL Server would abort a restore operation if any error was encountered during the restore process. However, in cases where this was the only media version available, this could be a serious impediment to recovering whatever data was available. With this new feature, SQL Server 2005 will allow the restore process to continue as far as possible, ignoring the media errors it encounters. After the restore process has finished, the database can be manually repaired.

Database Page Checksums

The new Database Page Checksum feature enables the database to detect disk and I/O errors that are not reported by the disk subsystem. When the Database Page Checksum feature is enabled, SQL Server will calculate a checksum value as the page is written to disk and write that checksum along with the data. When the page is read, another checksum is calculated and compared to the original checksum written with the data. If they are different, an error is reported. The following command shows how Database Page Checksums can be added to an existing database:

ALTER DATABASE AdventureWorks  SET PAGE_VERIFY CHECKSUM

Concurrent Database and Log Backups

Another new feature in the SQL Server 2005 backup area is the ability to perform database backups and log backups concurrently. Using SQL Server 2000, you had to wait to back up the log until after the database backup had completed. With SQL Server 2005, database backups no longer block log backups. Likewise, you can also back up files and filegroups at the same time that you back up the transaction log. However, you are limited to performing one data file backup at a time per each database.

Backup of Full-Text Catalogs

One other backup enhancement that’s part of SQL Server 2005 is the ability to back up Full Text Catalogs. With SQL Server 2000, Full Text Catalog data was maintained outside of SQL Server by the Microsoft Search Service. Backing up the SQL Server database that contained full-text data didn’t automatically result in the full-text catalog being backed up as well. This opened up the possibility of the catalog being out-of-sync with the full-text data being restored. SQL Server 2005 fixes this problem by including the ability to automatically back up all of the external full-text catalogs at the same time that the database is backed up. This ensures that the full-text catalog and the data remain consistent between the backup and restore operations. The DATABASE ATTACH and DEATTACH commands also have an option to include any full-text catalogs. Links in the database point to the external files used. When the database is backed up, these external files are also backed up, ensuring database consistency.




Microsoft SQL Server 2005 New Features
Microsoft SQL Server 2005 New Features
ISBN: 0072227761
EAN: 2147483647
Year: 2005
Pages: 80
Authors: Michael Otey

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net