Practical Tips on Database Backup and Restore

1       Types of Database Backup

Eleven types of database backup - theoretically 

Level

Data

Log

Database

Full

Copy-only

Differential

Regular Log Backup

Copy-only Log Backup

File

Full

Copy-only

Differential

Partial* (at the FG level)

Full

Copy-only

Differential

 

* Partial backup is designed for use under the simple recovery model to improve flexibility for backing up very large databases that contain one or more read-only filegroups. Partial backups are useful whenever you want to exclude read-only filegroups.

 

Copy-only provides an ultra-protection to the database, but is rarely used in practice.

 

The Five Typical Backup Types -Practically  

Level

Data

Log

Database

Full

Differential

Regular

Log Backup

File

Full

Differential

 

2       It is NOT recommended to use the “directly copy the mdf/ndf file and then attach to the database” approach

2.1    In order to copy the file, you normally need to stop SQL Server first.

2.2    If you have to use the Detach/Attach method, use the SSMS GUI or sp_detach_db to create the file for copying/attaching.

2.3    Include the log file as well

 

3       What can be lost and how to minimize the work loss?

3.1    Simple recovery model

The simple recovery model provides the simplest form of backup and restore. This recovery model supports both database backups and file backups, but does not support log backups.

 

The following illustration shows the simplest backup-and-restore strategy under the simple recovery model. This strategy uses only full database backups, which include all of the data in the database. Five full database backups exist, but only the most recent backup, taken at the time t5 has to be restored. Restoring this backup returns the database to the t5 point in time. All later updates, represented by the t6 box, are lost.

 

http://2.bp.blogspot.com/-ScQHaWJ3QxQ/VAjC4gCKobI/AAAAAAAAAak/eJmwCPyfhAc/s1600/1.gif

 

3.1.1          Minimizing Work-Loss Exposure: frequent full backup for small debases

 

The following illustration shows work-loss exposure for a backup plan that uses only database backups. The work-loss can occur after t0, depending on which backup we have on hand.

 

This strategy is appropriate only for a small database that you can back up fairly frequently.

 

http://3.bp.blogspot.com/-amwtXY0ZChQ/VAjC-4_5z8I/AAAAAAAAAas/zANK3gasB_Q/s1600/2.gif

 

3.1.2          Minimizing Work-Loss Exposure: full + differential backups for large databases

 

The following illustration shows a backup strategy that reduces work-loss exposure by supplementing database backups with differential database backups. After the first database backup, a series of three differential backups is taken. The third differential backup is large enough that the next backup is a database backup. This establishes a new differential base.

http://1.bp.blogspot.com/-A_Ocsgrjc6Q/VAjDEcThTDI/AAAAAAAAAa0/n-5pXxtEioE/s1600/3.gif

 

3.2    Full recovery model

3.2.1          Full + Log

The following illustration shows the easiest backup strategy under the full recovery model. In the illustration, a full database backup, Db_1, and two routine log backup, Log_1 and Log_2, have been taken. Some time after the Log_2 log backup, data loss occurs in the database. Before these three backups are restored, the database administrator must back up the active log (the tail of the log). The database administrator then restores Db_1, Log_1, and Log_2 without recovering the database. Then the database administrator restores and recovers the tail-log backup (Tail). This recovers the database to the point of failure, recovering all the data. If the disaster makes the tail log unavailable, the data after the Log_2 will be lost.

 

http://2.bp.blogspot.com/-0ntcZO8U7R8/VAjDJbG8LEI/AAAAAAAAAa8/EmrFWCXVi7A/s1600/4.gif

 

3.2.2          Minimizing Work-Loss Exposure: Full + Differential + Log

After the first full database backup is completed and regular log backups start, the potential work-loss exposure is narrowed to the time between when the database is damaged and the most recent regular log backup. Therefore, we recommend that you take log backups frequently enough to keep your work-loss exposure within the confines required by your business requirements.

 

The following illustration shows a backup strategy that supplements full database backups and log backups with differential database backups. The transaction log backups reduce potential work-loss exposure to the time after the most recent log backup, t14. A series of three differential backups is taken to reduce the number of transaction logs that would need to be restored in the event of a failure. The third differential backup is large enough that the next backup is a full database backup. This establishes a new differential base.

 

http://4.bp.blogspot.com/-4Jud1FA4MWM/VAjDOxdTzNI/AAAAAAAAAbE/QlipH1wBmho/s1600/5.gif

 

Before first database backup in this figure, the database is exposed to potential work loss (from time t0 to time t1). Thereafter, routine log backups reduce work-loss exposure to the risk of losing changes that were made after the latest log backup (taken at time t14 in this figure). In the event of a failure after the most recent backup, the database administrator would try to back up the tail of the log (the log that is not yet backed up). If the tail-log backup succeeds, the database administrator could avoid any work loss by restoring the database up to the point of failure.

 

3.2.3          File/FileGroup backup/restore in full recovery model is complex and hard to manage. They are only used in some extra large databases. For more information, see http://technet.microsoft.com/en-us/library/ms189860(v=sql.105).aspx

 

3.3    Restore strategies

3.3.1          Restore level

1)    Database

2)    Data File

3)    Data Page

3.3.2          Restore Scenarios

1)    A complete database restore – database is offline during restore

2)    File restore – restore one or more files without restoring the entire DB – the restoring file is not available, but other files are available

3)    Page restore – restore one or more damaged pages. Significantly reduce the restoring time

4)    Piecemeal restore - Restore and recover the database in stages at the filegroup level, starting with the primary filegroup. Significantly reduce the offline time.

 

Let’s explain and demo each of the four major approaches.

 

3.4    A complete database restore

3.4.1          An Example – restore a database at STOPAT using the Full + Log backups


USE master

GO

 

--Step 1: back up the database in full if one doesnt exist.

BACKUP DATABASE AdventureWorks2012

TO DISK = 'c:\users\Charlie\Documents\AdventureWorks2012.bak'

GO

 

--Step 2: back up the tail log

--This action will put the database in the restoring status

--Also note the tail log backup goes to the same file as the full backup.

--But the file numbers will be different.

 

BACKUP LOG AdventureWorks2012

TO DISK = 'c:\users\Charlie\Documents\AdventureWorks2012.bak'

   WITH NORECOVERY;

GO

 

--Step 3: restore the full backup

RESTORE DATABASE AdventureWorks2012

  FROM DISK = 'c:\users\Charlie\Documents\AdventureWorks2012.bak'

  WITH FILE=1,

    NORECOVERY;

 

--Step 4: restore the log backups in sequence from the earliest to the latest

--Assuming there is one log. In our case, we do not have any log backups. This step will be omitted.

RESTORE LOG AdventureWorks2012

  FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'

  WITH FILE=2,

    NORECOVERY;

 

--Step 5: STOPAT the time you want with the tail log on Recovery

--In our case, it would be File = 2 for the tail log backup

RESTORE LOG AdventureWorks2012

  FROM DISK = 'c:\users\Charlie\Documents\AdventureWorks2012.bak'

  WITH FILE=3, --STOPAT='XXXX xx:xx:xx',

   RECOVERY;

GO

 

3.4.2          Challenges:

 

               The above Tail Log /Full [Differential]/Log/Tail Log approach is

 

·               The most popular practice. Good for many databases

·               But for large databases, the restore of the full backup could take too long, such as a TB database may take up hours for restore. In the case like this, we may use other approaches as explained and demoed below.

 

3.4.3          The online restore option introduced in SQL Server 2005

 (from http://vivekranjan1980.wordpress.com/tag/database/)

Microsoft has added its new online restore option to SQL Server 2005, which allows you to restore a backup while the database is still online. In the past you needed exclusive access to the database in order to restore, but that’s not the case with SQL Server 2005. Before you get too excited about this new feature, there are a couple of things to note: (1) This option only exists in the Enterprise and Developer Editions and (2) You can only restore at the filegroup level.

 

So what does that mean? Well, you will have to invest in the more expensive version of SQL Server to be able to use this feature in a production environment. But, more importantly, to even take advantage of this product, your database must be configured in a certain way.

·       Database setup

A few things need to occur before you can take advantage of this new feature in SQL Server 2005.

o   Your database must be using multiple filegroups. That’s because you are restoring a file or an entire filegroup, and when the restore occurs, this filegroup is offline and not accessible.

o   The primary filegroup, which holds your system tables, needs to remain online.

o   The filegroups can be read-only or read-write filegroups.

o   For read-write filegroups, you must use the full or bulk-logged recovery model so transactions can be restored.

o   For read-only filegroups, you can use the simple recovery model.

o   Since some of the filegroups may be online and others offline, carefully plan how your data is laid out and how your applications access your tables.

 

·       How to restore

From a restore perspective, it is pretty easy to issue an online restore procedure. Basically, you perform the same process as you normally go through when doing a restore, but you also specify a file or filegroup you want to restore.

·       Advantages

The biggest advantage of doing online restores is that you have the ability to keep your database up while you restore a portion of the database that may be corrupt. Doing a partial restore is also much faster than restoring the entire database. For very large database environments, it’s critical to cut down on the overhead of maintaining your databases. Also, if you are using read-only filegroups, the partial restore feature is very helpful because the restore process is quite simple and straightforward, and there is no impact to the rest of the database.

·       Disadvantages

The biggest disadvantage to this process is having to set up your database to take advantage of filegroup restores. Usually if you have a very large database, you’ve already implemented multiple filegroups, so you’ve taken care of this process. In smaller database installations, the database is set up with only the primary filegroup, so planning and reconfiguration would need to take place.

 

In addition to the initial setup, see what portions of your database you can take offline to do an online restore and still have the application function. This part is probably the hardest thing to figure out. With read-only filegroups, which may contain archived data, this may be a much simpler task. However, with read-write filegroups, it may not be very easy to do — or even possible.

 

Now let’s see how the file and filegroup restore work.

 

3.5    File restore – restore one or more files without restoring the entire DB

3.5.1          File Restores in Simple Recovery Model

1)    Overview     

 

This topic is relevant only for simple-model databases that contain at least one read-only secondary filegroup, that is, the read-only files in the secondary filegroup are corrupted and need to be restored.

 

In a file restore, the goal is to restore one or more damaged files without restoring the whole database. Under the simple recovery model, file backups are supported only for read-only files. The primary filegroup and read/write secondary filegroups are always restored together, by restoring a database or partial backup (not by file backup/restore).

 

A file restore scenario consists of a single restore sequence that copies, rolls forward, and recovers the appropriate data as follows:

 

a)    Restore each damaged file from its most recent file backup.

b)    Restore the most recent differential file backup for each restored file and recover the database.

 

The restore sequence contains only two Transact-SQL statements. The first statement restores a secondary file, file A, which is restored using WITH NORECOVERY. The second operation restores two other files, B and C which are restored using WITH RECOVERY from a different backup device:

RESTORE DATABASE xxx FILE = name_of_file_A

        FROM file_backup_of_file_A WITH NORECOVERY

 

RESTORE DATABASE xxx FILE = name_of_file_Bname_of_file_C

        FROM file_backup_of_files_B_and_C WITH RECOVERY;

 

2)    A Simpler Example  - Online Restore of one corrupted Read-Only File (Simple Recovery Model)

 

This topic is relevant for SQL Server databases under the simple recovery model that contain a read-only filegroup. Under the simple recovery model, a read-only file can be restored online if a file backup exists that was taken since the file became read-only for the last time.

 

In this example, a database named adb contains three filegroups. Filegroup A is read/write, and filegroups B and C are read-only. Initially, all of the filegroups are online. A read-only file in filegroup Bb1, has to be restored. The database administrator can restore it by using a backup that was taken after the file became read-only. For the duration of the restore, filegroup B will be offline, but the remainder of the database will remain online.

 

To restore the file, use the following restore sequence:

 

RESTORE DATABASE adb FILE='b1' FROM filegroup_B_backup WITH RECOVERY

 

The file is now online.

 

The syntax for an online restore sequence is the same as for an offline restore sequence.

 

3)    Online restore of read/write data is not allowed in Simple Recovery Model as no log information is available.

 

3.5.2          File Restores in Full Recovery Model

1)    Overview

 

This topic is relevant only for databases that contain multiple files or filegroups under the full or bulk-load recovery model.

 

In a file restore, the goal is to restore one or more damaged files without restoring the whole database. A file restore scenario consists of a single restore sequence that copies, rolls forward, and recovers the appropriate data.

 

If the filegroup that is being restored is read/write, an unbroken chain of log backups must be applied after the last data or differential backup is restored. This brings the filegroup forward to the log records in the current active log records in the log file. The recovery point is typically near the end of log, but not necessarily.

 

If the filegroup that is being restored is read-only, usually applying log backups is unnecessary and is skipped. If the backup was taken after the file became read-only, that is the last backup to restore. Roll forward stops at the target point.

 

Steps to Restore Damaged Files from File Backups:

 

a)    Before restoring one or more damaged files, attempt to create a tail-log backup.

 

If the log has been damaged, a tail-log backup cannot be created, and you must restore the whole database.

 

For an offline file restore, you must always take a tail-log backup before the file restore. For an online file restore, you must always take the log backup after the file restore. This log backup is necessary to allow for the file to be recovered to a state consistent with the rest of the database.

 

b)    Restore each damaged file from the most recent file backup of that file.

c)    Restore the most recent differential file backup, if any, for each restored file.

d)    Restore transaction log backups in sequence, starting with the backup that covers the oldest of the restored files and ending with the tail-log backup created in step 1.

 

You must restore the transaction log backups that were created after the file backups to bring the database to a consistent state. The transaction log backups can be rolled forward quickly, because only the changes that apply to the restored files are applied. Restoring individual files can be better than restoring the whole database, because undamaged files are not copied and then rolled forward. However, the whole chain of log backups still has to be read.

e)    Recover the database.

 

File backups can be used to restore the database to an earlier point in time. To do this, you must restore a complete set of file backups, and then restore transaction log backups in sequence to reach a target point that is after the end of the most recent restored file backup.

 

2)    Example 1 – Online Restore of a Read-Only File (Full Recovery Model)

 

This topic is relevant for SQL Server databases under the full recovery model that contain multiple files or filegroups.

 

In this example, a database named adb, which uses the full recovery model, contains three filegroups. Filegroup A is read/write, and filegroup B and filegroup C are read-only. Initially, all of the filegroups are online.

 

A read-only file, b1, in filegroup B of database adb has to be restored. A backup was taken since the file became read-only; therefore, log backups are not required. Filegroup B is offline for the duration of the restore, but the remainder of the database remains online.

 

To restore the file, use the following restore sequence:

 

RESTORE DATABASE adb FILE=b1 FROM filegroup_B_backup WITH RECOVERY

 

Filegroup B is now online.

 

The syntax for an online restore sequence is the same as for an offline restore sequence.

 

3)    Example 2 - Online Restore of a Read/Write File (Full Recovery Model)

 

This topic is relevant for SQL Server databases under the full recovery model that contain multiple files or filegroups.

 

In this example, a database named adb, which uses the full recovery model, contains three filegroups. Filegroup A is read/write, and filegroup B and filegroup C are read-only. Initially, all of the filegroups are online.

 

File a1 in filegroup appears to be damaged, and the database administrator decides to restore it while the database remains online.

 

Restore Sequences:

 

Step 1 - Online restore of file a1.

 

RESTORE DATABASE adb FILE='a1' FROM backup WITH NORECOVERY;

 

At this point, file a1 is in the RESTORING state, and filegroup A is offline.

 

Step 2 - After restoring the file, the database administrator takes a new log backup to make sure that the point at which the file went offline is captured.

 

BACKUP LOG adb TO log_backup3;

 Step 3 - Online restore of log backups.

 

The administrator restores all the log backups taken since the restored file backup, ending with the latest log backup (log_backup3, taken in step 2). After the last backup is restored, the database is recovered.

RESTORE LOG adb FROM log_backup1 WITH NORECOVERY;

RESTORE LOG adb FROM log_backup2 WITH NORECOVERY;

RESTORE LOG adb FROM log_backup3 WITH NORECOVERY;

RESTORE LOG adb WITH RECOVERY;

 

               File a1 is now online.

 

4)    Example 3 - Restore Sequence for an Offline Restore for Two Read/Write Files (Full Recovery Model)

 

The following sample restore sequence shows an offline restore of two secondary files, A and B, using WITH NORECOVERY. Next, two log backups are applied with NORECOVERY, followed with the tail-log backup, and this is restored using WITH RECOVERY.

 

The following sample restore sequence starts by taking the file offline and then creates a tail-log backup.

 

--Take the file offline.

ALTER DATABASE database_name MODIFY FILE SET OFFLINE;

 

-- Back up the currently active transaction log.

BACKUP LOG database_name

   TO <tail_log_backupWITH NORECOVERY;

GO

 

-- Restore the files.

RESTORE DATABASE database_name FILE=name

   FROM <file_backup_of_file_AWITH NORECOVERY;

 

RESTORE DATABASE database_name FILE=<name> ......

   FROM <file_backup_of_file_BWITH NORECOVERY;

 

-- Restore the log backups.

RESTORE LOG database_name FROM <log_backupWITH NORECOVERY;

RESTORE LOG database_name FROM <log_backupWITH NORECOVERY;

RESTORE LOG database_name FROM <tail_log_backupWITH RECOVERY;

 

5)    Example 4 - Offline Restore of Primary and One Other Filegroup (Full Recovery Model)

 

In this example, a database named adb contains three filegroups. Filegroups A and C are read/write, and filegroup B is read-only. The primary filegroup and filegroup B are damaged, but filegroups A and C are intact. Before the disaster, all the filegroups were online.

 

The database administrator decides to restore and recover the primary filegroup and filegroup B. The database is using the full recovery model; therefore, before the restore starts, a tail-log backup must be taken of the database. When the database comes on line, Filegroups A and C are automatically brought online.

 

Tail-Log Backup

Before restoring the database, the database administrator must back up the tail of the log. Because the database is damaged, creating the tail-log backup requires using the NO_TRUNCATE option (NO_TRUNCATE Specifies that the log not be truncated and causes the Database Engine to attempt the backup regardless of the state of the database. Consequently, a backup taken with NO_TRUNCATE might have incomplete metadata. This option allows backing up the log in situations where the database is damaged.)

BACKUP LOG adb TO tailLogBackup WITH NORECOVERY, NO_TRUNCATE 

 

The tail-log backup is the last backup that is applied in the following restore sequences.

 

Restore Sequence

 

To restore the primary filegroup and filegroup B, the database administrator uses a restore sequence without the PARTIAL option, as follows:

RESTORE DATABASE adb FILEGROUP='Primary' FROM backup1 WITH NORECOVERY
RESTORE DATABASE adb FILEGROUP='B' FROM backup2 WITH NORECOVERY
RESTORE LOG adb FROM backup3 WITH NORECOVERY
RESTORE LOG adb FROM backup4 WITH NORECOVERY
RESTORE LOG adb FROM backup5 WITH NORECOVERY
RESTORE LOG adb FROM tailLogBackup WITH RECOVERY

 

The files that are not restored are automatically brought online. All the filegroups are now online.

3.6    Page restore

3.6.1          What is it?

 

The goal of a page restore is to restore one or more damaged pages without restoring the whole database. Typically, pages that are candidates for restore have been marked as "suspect" because of an error that is encountered when accessing the page. Suspect pages are identified in the suspect_pages table in the msdb database.

 

3.6.2          When to use it?

 

A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation. However, if you have to restore more than a few pages in a file, it is generally more efficient to restore the whole file. For example, if lots of pages on a device indicate a pending device failure, consider restoring the file, possibly to another location, and repairing the device.

 

Furthermore, not all page errors require a restore. A problem can occur in cached data, such as a secondary index, that can be resolved by recalculating the data. For example, if the database administrator drops a secondary index and rebuilds it, the corrupted data, although fixed, is not indicated as such in the suspect_pages table.

 

3.6.3          Limitations

·       Page restore applies to SQL Server databases that are using the full or bulk-logged recovery models. Page restore is supported only for read/write filegroups.

·       Only data pages can be restored. Page restore cannot be used to restore the following:

o   Transaction log

o   Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.

o   Page 0 of all data files (the file boot page)

o   Page 1:9 (the database boot page)

o   Full-text catalog

·       Page restore generally does not work with bulk-logged recovery. A best practice for performing page restore is to set the database to the full recovery model, and try a log backup. If the log backup works, you can continue with the page restore. If the log backup fails, you either have to lose work since the previous log backup or you have to try running DBCC must be run with the REPAIR_ALLOW_DATA_LOSS option.

 

3.6.4          The basic steps to restore damaged page

 

1)    Obtain the File IDs and Page IDs in one of the following ways

·       From the suspect_pages table in the msdbmsdb.suspect_pages

·       SQL Server error Log

·       Events Traces

·       DBCC

·       WMI Provider

2)    Start a page restore with a full database, file, or filegroup backup that contains the page. In the RESTORE DATABASE statement, use the PAGE clause to list the page IDs of all of the pages to be restored.

3)    Apply the most recent differentials.

4)    Apply the subsequent log backups.

5)    Create a new log backup of the database that includes the final LSN of the restored pages, that is, the point at which the last restored page is taken offline.

6)    Restore the new log backup. After this new log backup is applied, the page restore is completed and the pages are now usable.

 

3.6.5          Example:

 

The following example restores four damaged pages of file B with NORECOVERY. Next, two log backups are applied with NORECOVERY, followed with the tail-log backup, which is restored with RECOVERY. This example performs an online restore. In the example, the file ID of file B is 1, and the page IDs of the damaged pages are 57202916, and 1016.


RESTORE DATABASE <databasePAGE='1:57, 1:202, 1:916, 1:1016'

   FROM <file_backup_of_file_B>

WITH NORECOVERY;

 

RESTORE LOG <databaseFROM <log_backup>

WITH NORECOVERY;

 

RESTORE LOG <databaseFROM <log_backup>

WITH NORECOVERY;

 

BACKUP LOG <databaseTO <new_log_backup>;

 

RESTORE LOG <databaseFROM <new_log_backupWITH RECOVERY;

GO

 

3.7    Piecemeal Restore

3.7.1          What is for? - allows databases that contain multiple filegroups to be restored and recovered in stages. 

3.7.2          Which recovery model is applicable to use it? – All three, but more flexible for the Full and Bulk_Logged than for the Simple model.

3.7.3          What are the basic restore procedures?

 

Regardless of the recovery model that is used by the database, the partial-restore sequence starts with a RESTORE DATABASE statement that restores a full backup and specifies the PARTIAL option. The PARTIAL option always starts a new piecemeal restore; therefore, you must specify PARTIAL only one time in the initial statement of the partial-restore sequence. When the partial restore sequence finishes and the database is brought online, the state of the remaining files becomes "recovery pending" because their recovery has been postponed.

 

Subsequently, a piecemeal restore typically includes one or more restore sequences, which are called filegroup-restore sequences. You can wait to perform a specific filegroup-restore sequence for as long as you want. Each filegroup-restore sequence restores and recovers one or more offline filegroups to a point consistent with the database. The timing and number of filegroup-restore sequences depends on your recovery goal, the number of offline filegroups you want to restore, and on how many of them you restore per filegroup-restore sequence.

 

The exact requirements for performing a piecemeal restore depend on the recovery model of the database. 

 

3.7.4          Piecemeal Restore Stages under the Simple Recovery Model

 

The piecemeal restore scenario involves the following stages:

 

1)    Initial stage (restore and recover the primary filegroup and all read/write filegroups)

 

The initial stage performs a partial restore. The partial restore sequence restores the primary filegroup, all read/write secondary filegroups, and (optionally) some of the read-only filegroups. During the initial stage, the whole database must go offline. After the initial stage, the database is online, and restored filegroups are available. However, any read-only filegroups that have not yet been restored, remain offline.

 

The first RESTORE statement in the initial stage must do the following:

 

·       Use a partial or full database backup that contains the primary filegroup and all filegroups that were read/write at the time of the backup. It is common to start a partial restore sequence by restoring a partial backup.

·       Specify the PARTIAL option, which indicates the start of a piecemeal restore.

 

Note: The PARTIAL option performs safety checks that ensure that the resulting database is suited for use as a production database.

 

·       Specify the READ_WRITE_FILEGROUPS option if the backup is a full database backup.

 

2)    While the database is online, you can use one or more online file restores to restore and recover offline read-only files that were read-only at the time of backup. The timing of the online file restores depends on when you want to have the data online.

 

Whether you must restore data to a file depends on the following:

·       Valid read-only files that are consistent with the database can be brought online directly by recovering them without restoring any data.

·       Files that are damaged or inconsistent with the database must be restored before they are recovered.

 

3)    Example 1: Piecemeal Restore of Database in Simple Recovery Model

 

A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, starting with the primary and all read/write, secondary filegroups.

 

In this example, database adb is restored to a new computer after a disaster. The database is using the simple recovery model. Before the disaster, all the filegroups are online.

 

Filegroups A and C are read/write, and filegroup B is read-only. Filegroup B became read-only before the most recent partial backup, which contains the primary filegroup and the read/write secondary filegroupsA and C. After filegroup B became read-only, a separate file backup of filegroup B was taken.

 

Restore Sequences:

 

a)    Partial restore of the primary and filegroups A and C.

 

RESTORE DATABASE adb FILEGROUP='A',FILEGROUP='C'

   FROM partial_backup WITH PARTIALRECOVERY;


At this point, the primary and filegroups 
A and C are online. All files in filegroup B are

recovery pending, and the filegroup is offline.

 

b)    Online restore of filegroup B.

 

RESTORE DATABASE adb FILEGROUP='B' FROM backup WITH RECOVERY;

All filegroups are now online.

 

4)    Example 2: Piecemeal Restore of Only Some Filegroups in Simple Recovery Model

 

In this example, a database named adb, which uses the simple recovery model, contains three filegroups. Filegroup A is read/write, and filegroup B and filegroupC are read-only. Initially, all of the filegroups are online.

 

The primary and filegroup B of database adb appear to be damaged; therefore, the database administrator decides to restore them by using a piecemeal restore sequence. Under the simple recovery model, all read/write filegroups must be restored from the same partial backup. Although filegroup A is intact, it must be restored with the primary filegroup to make sure that they are consistent (the database will be restored to the point in time defined by the end of the last partial backup). Filegroup C is intact, but it must be recovered to bring it online. Filegroup B, although damaged, contains less critical data than Filegroup C; therefore, will be restored last.

 

Restore Sequences:

 

a)    Partial restore of the primary and filegroup A from a partial backup.

 

RESTORE DATABASE adb READ_WRITE_FILEGROUPS FROM partial_backup

WITH PARTIALRECOVERY

 

At this point the primary filegroup and filegroup A are online. Files in filegroups B and C are recovery pending, and the filegroups are offline.

 

b)    Online recovery of filegroup C.

 

Filegroup C is consistent because the partial backup that was restored above was taken after filegroup C became read-only, although the database was taken back in time by the restore. The database administrator recovers the filegroup C, without restoring it, to bring it online.

 

RESTORE DATABASE adb FILEGROUP='C' WITH RECOVERY

 

At this point the primary and filegroups A and C are online. Files in filegroupB remain recovery pending, with the filegroup offline.

 

c)    Online restore of filegroup B.

 

Files in filegroup B must be restored. The database administrator restores the backup of filegroup B taken after filegroup B became read-only and before the partial backup.

 

RESTORE DATABASE adb FILEGROUP='B' FROM backup WITH RECOVERY

 

All filegroups are now online.

 

3.7.5          Piecemeal Restore Under the Full Recovery Model

1)    Restore sequence

Under the full recovery model or bulk-logged recovery model, piecemeal restore is available for any database that contains multiple filegroups and you can restore a database to any point in time. The restore sequences of a piecemeal restore behave as follows:

 

a)    Partial-restore sequence

 

The partial restore sequence restores the primary filegroup and, optionally, some of the secondary filegroups.

 

The first RESTORE DATABASE statement must do the following:

·       Specify the PARTIAL option. This indicates the start of a piecemeal restore.

·       Use any full database backup that contains the primary filegroup. The common practice is to start a partial restore sequence by restoring a partial backup.

·       To restore to a specific point in time, you must specify the time in the partial restore sequence. Every successive step of the restore sequence must specify the same point in time.

b)    Filegroup-restore sequences bring additional filegroups online to a point consistent with the database.

 

In the Enterprise edition, any offline secondary filegroup can be restored and recovered while the database remains online. If a specific read-only file is undamaged and consistent with the database, the file does not have to be restored.

 

c)    Applying Log Backups (Optional)

 

If a read-only filegroup has been read-only since before the file backup was created, applying log backups to the filegroup is unnecessary and is skipped by file restore. If the filegroup is read/write, an unbroken chain of log backups must be applied to the last full or differential restore to bring the filegroup forward to the current log file.

 

2)    Example 1: Piecemeal Restore of Database in Full Recovery Model

 

In this example, database adb is restored to a new computer after a disaster. The database is using the full recovery model; therefore, before the restore starts, a tail-log backup must be taken of the database. Before the disaster, all the filegroups are online. Filegroup B is read-only. All of the secondary filegroups must be restored, but they are restored in order of importance: A (highest), C, and lastly B. In this example, there are four log backups, including the tail-log backup.

 

a)    Tail-Log Backup

 

Before restoring the database, the database administrator must back up the tail of the log. Because the database is damaged, creating the tail-log backup requires using the NO_TRUNCATE option:

 

BACKUP LOG adb TO tailLogBackup WITH NORECOVERYNO_TRUNCATE

 

The tail-log backup is the last backup that is applied in the following restore sequences.

 

b)    Restore Sequences

i.                    Partial restore of the primary and secondary filegroup A.

 

RESTORE DATABASE adb FILEGROUP='Primary' FROM backup1 WITH PARTIALNORECOVERY

RESTORE DATABASE adb FILEGROUP='A' FROM backup2 WITH NORECOVERY

RESTORE LOG adb FROM backup3 WITH NORECOVERY

RESTORE LOG adb FROM backup4 WITH NORECOVERY

RESTORE LOG adb FROM backup5 WITH NORECOVERY

RESTORE LOG adb FROM tailLogBackup WITH RECOVERY

 

ii.                  Online restore of filegroup C.

 

At this point, the primary filegroup and secondary filegroup A are online. All the files in filegroups B and C are recovery pending, and the filegroups are offline.

 

Messages from the last RESTORE LOG statement in step 1 indicate that rollback of transactions that involve filegroup C was deferred, because this filegroup is not available. Regular operations can continue, but locks are held by these transactions and log truncation will not occur until the rollback can complete.

 

In the second restore sequence, the database administrator restores filegroup C:

 

RESTORE DATABASE adb FILEGROUP='C' FROM backup2a WITH NORECOVERY

RESTORE LOG adb FROM backup3 WITH NORECOVERY

RESTORE LOG adb FROM backup4 WITH NORECOVERY

RESTORE LOG adb FROM backup5 WITH NORECOVERY

RESTORE LOG adb FROM tailLogBackup WITH RECOVERY

 

At this point the primary and filegroups A and C are online. Files in filegroup B remain recovery pending, with the filegroup offline. Deferred transactions have been resolved, and log truncation occurs.

 

iii.                Online restore of filegroup B.

 

In the third restore sequence, the database administrator restores filegroup B. The backup of filegroup B was taken after the filegroup became read-only; therefore, it does not have to be rolled forward during recovery.

 

RESTORE DATABASE adb FILEGROUP='B' FROM backup2b WITH RECOVERY

 

All filegroups are now online.

 

3)    Example 2: Piecemeal Restore of Only Some Filegroups in Full Recovery Model

 

In this example, a database named adb, which uses the full recovery model, contains three filegroups. Filegroup A is read/write, and filegroup B and filegroup Care read-only. Initially, all of the filegroups are online.

 

The primary and filegroup B of database adb appear to be damaged. The primary filegroup is fairly small and can be restored quickly. The database administrator decides to restore them by using a piecemeal restore sequence. First, the primary filegroup and the subsequent transaction logs are restored the database is recovered.

 

The intact filegroups A and C contain critical data. Therefore, they will be recovered next to bring them online as quickly as possible. Finally, the damaged secondary filegroupB, is restored and recovered.

 

a)    Tail-Log Backup

 

Create a tail log backup of database adb. This step is essential to make the intact filegroups A and C current with the recovery point of the database:

 

BACKUP LOG adb TO tailLogBackup WITH NORECOVERY

 

b)    Partial Restore of the Primary Filegroup

 

RESTORE DATABASE adb FILEGROUP='Primary' FROM backup WITH PARTIALNORECOVERY

RESTORE LOG adb FROM backup1 WITH NORECOVERY

RESTORE LOG adb FROM backup2 WITH NORECOVERY

RESTORE LOG adb FROM backup3 WITH NORECOVERY

RESTORE LOG adb FROM tailLogBackup WITH RECOVERY

 

At this point the primary is online. Files in filegroups AB, and C are recovery pending, and the filegroups are offline.

 

c)    Online restore of filegroups A and C

 

Because their data is undamaged, these filegroups do not have to be restored from a backup, but they do have to be recovered to bring them online.

 

The database administrator recovers A and C immediately.

 

RESTORE DATABASE adb FILEGROUP='A'FILEGROUP='C' WITH RECOVERY

 

At this point the primary and filegroups A and C are online. Files in filegroup B remain recovery pending, with the filegroup offline.

 

d)    Online reatore of filegroup B

 

Files in filegroup B are restored any time thereafter.

 

Note: The backup of filegroup B was taken after the filegroup became read-only; therefore, these files do not have to be rolled forward.

 

RESTORE DATABASE adb FILEGROUP='B' FROM backup WITH RECOVERY

 

All filegroups are now online.

 

3.7.6          Performing a Piecemeal Restore of a Database Whose Recovery Model Has Been Switched from Simple to Full

 

You can perform a piecemeal restore of a database that has been switched from the simple recovery model to the full recovery model since the full partial or database backup. For example, consider a database for which you take the following steps:

 

1)    Create a partial backup (backup_1) of a simple-model database.

2)    After some time, change the recovery model to full.

3)    Create a differential backup.

4)    Start taking log backups.

 

Thereafter, the following sequence is valid:

 

1)    A partial restore that omits some secondary filegroups.

2)    A differential restore followed by any other needed restores.

3)    Later, a file restore of a read/write secondary filegroup WITH NORECOVERY from the backup_1 partial backup

4)    The differential backup followed by any other backups that were restored in the original piecemeal restore sequence to restore the data up to the original recovery point.

4       What if the backup is bad?

4.1    4.1.   How possible?

4.1.1          The backup and data/log files are on the same physical device which is damaged

4.1.2          The data get corrupted during transmission over a network

4.1.3          The data already corrupted during backup (remember, to save time, SQL Server does not do consistency check during backup, but it will do the check when restoring)

4.2    Solutions

4.2.1          If hardware failure, repair and fix the problems, and try the restore again

4.2.2          Try CONTINUE_AFTER_ERROR, then, use DBCC CheckDB. It is hard to predict how much can be recovered or lost. It depends on what have been corrupted. Some corrupted pages may be marked as suspected in the suspected_pages table. Check the error log after executing

 

Restore Database xxx From backuP_device WITH CONTINUE_AFTER_ERROR, [NORECOVERY]

 

4.2.3          Use log shipping with a StandBy Server or DB mirroring with a secondary server. Practically, log shipping is the cheapest option.