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.
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.
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.
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.
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.
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 doesn’t 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_B, name_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 B, b1, 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 A 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_backup> WITH NORECOVERY;
GO
-- Restore the files.
RESTORE DATABASE database_name FILE=name
FROM <file_backup_of_file_A> WITH NORECOVERY;
RESTORE DATABASE database_name FILE=<name> ......
FROM <file_backup_of_file_B> WITH NORECOVERY;
-- Restore the log backups.
RESTORE LOG database_name FROM <log_backup> WITH NORECOVERY;
RESTORE LOG database_name FROM <log_backup> WITH NORECOVERY;
RESTORE LOG database_name FROM <tail_log_backup> WITH 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.
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.
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 msdb: msdb.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 57, 202, 916,
and 1016.
RESTORE DATABASE <database> PAGE='1:57, 1:202,
1:916, 1:1016'
FROM <file_backup_of_file_B>
WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
WITH NORECOVERY;
BACKUP LOG <database> TO <new_log_backup>;
RESTORE LOG <database> FROM <new_log_backup> WITH 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 filegroups, A and C. After filegroup B became read-only, a separate file
backup of filegroup B was taken.
a)
Partial restore of the primary and filegroups A and C.
RESTORE DATABASE adb FILEGROUP='A',FILEGROUP='C'
FROM partial_backup WITH PARTIAL, RECOVERY;
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, B will be restored last.
a)
Partial restore of the primary and filegroup A from
a partial backup.
RESTORE DATABASE adb READ_WRITE_FILEGROUPS FROM partial_backup
WITH PARTIAL, RECOVERY
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.
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 NORECOVERY, NO_TRUNCATE
The
tail-log backup is the last backup that is applied in the following restore
sequences.
i.
Partial restore of the primary and
secondary filegroup A.
RESTORE DATABASE adb FILEGROUP='Primary' FROM backup1 WITH PARTIAL, NORECOVERY
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 filegroup, B, is restored and recovered.
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 PARTIAL, NORECOVERY
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 A, B,
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]
