Various Scenarios in SQL Server Restore and Recovery

This post aims at explaining what all types of files/data users can recover via SQL Restore and Recovery. The post also explains few scenarios under simple recovery model and full recovery model.

In order to recover SQL server database after a failure, database admin needs to restore all the SQL Server backups in restore sequences. The sequences must be both meaningful as well as logically correct. The entire restore and recovery process is helpful in recovering the data from backups. The backups can contain a data file, data page, or even an entire database. Below you will find information about what all type of data it can restore.

A complete database

Complete Database RestoreThe database and everything it contains is recoverable and restorable. The database goes offline for the time when recovery and restoration takes place.

File restore

A file containing data or a set of files is recovered and restored. While the restoration process is going on, filegroups containing the files automatically go offline for that time. Attempting to access that filegroup will cause an error.

Page restore

Page Restore In SQLUnder Bulk logged or full recovery model, it is possible to restore individual pages. Users can perform this restoration process on all types of databases irrespective of number of filegroups.

SQL Server backup and restore works on all the operating systems that it supports whatever it is 32-bit or 64-bit system.

Below you will find a detailed list of a few restore scenarios that work on various recovery models. Take a look at them:

Restore scenario

Under simple recovery model

Under full/bulk-logged recovery models

Complete database restore

This basic strategy involving complete database can restore and recover a full database. Alternatively, it can also involve restoring database backup which is followed by recovery of a differential backup. Recover and restore strategy for complete database involves full database backup restoration. Additionally, it can also involve a differential backup. After that it sequentially restores all subsequent log backups. The process finishes when the last log backup is recovered and restored.

 

 

File restore

It restores one or more read-only files that are damaged. It doesn’t restore entire databases. Restore is available only if there is at least one read-only file group in the database. It restores files but not the entire database. Users can perform restoration while database is offline. For few editions of SQL Server 2005 and the versions that followed, database remains online during restoration. File groups containing files that need restoration always remain offline during restoration.

Page restore

Not applicable It restores one or more lost pages. Restore can happen while the database is offline. Few versions of SQL Server 2005 and the following versions can be restored online as well. The pages being restored remain offline during the process of restore.

The log back-ups chain must be unbroken and available including the latest log file.

Piecemeal restore

Restore and recover of database takes place in stages starting with the primary and all read/write and then secondary filegroups. Database recovery takes place in stages at filegroup level starting with the primary filegroup.

Author Introduction:

Peter Song is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including outlook recovery and PDF recovery software products. For more information, visit www.datanumen.com.

Comments are closed.