Restoration process helps users to restore SQL Server database from backup (full, differential, log) & then recovering the database but every SQL Server recovery models support different restore options. SQL Server 2008 supports three restore options:
1.Complete Database Restore: In this option, we can restore full database. During restore operations, database should be offline.
2.File Restore: In this option, we can restore a file or multiple files. During this process, the filegroups that contain the files that are being restored are always offline.
3.Page Restore: This option can be performed on any database. Page restore can be performed while the database is offline.
As we all knows that SQL Server Recovery supports three types of recovery models: Simple, Bulk-Logged & Full. All recovery models support different restore options. Here is the small overview:
Simple Recovery Model: This model supports complete database restore, File restore options. Page restore is not applicable in this model. In this model complete restore option used full database backup & differential backup to restore complete database. We can use File restore option only if the database has at least one read-only filegroup.
Full/bulk-logged recovery models: All options criteria are same for both full/bulk-logged models. The models support all three restore options: complete, file & Page options. In both models complete restore option used full database backup, differential backup & all subsequent log backups to restore complete database. As I have already mentioned that during file restore & page restore database should be offline. In case if database remains online then the pages that are being restored are always offline.
I always prefer File Restore & Page Restore options because I can restore files or pages, instead of restoring the full SQL Server database. These options are applied for SQL Server 2008. Hope this article will help you to choose best restore option.
No comments:
Post a Comment