Computer Associates SQLSTQ99000600 Microsoft SQL Guide - Page 70

Perform an Online Torn Restore Using Microsoft SQL Server 2005 or 2008 Enterprise, Data Center

Get Computer Associates SQLSTQ99000600 - Unicenter SQL-Station 6.0.0 PDF manuals and user guides
UPC - 757943257441
View all Computer Associates SQLSTQ99000600 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 70 highlights

Perform an Online Torn Page Restore Using Microsoft SQL Server 2005 or 2008 Enterprise, Data Center, or Developer Editions Perform an Online Torn Page Restore Using Microsoft SQL Server 2005 or 2008 Enterprise, Data Center, or Developer Editions Microsoft SQL Server 2005 and 2008 have the ability to detect when the data in the database has been damaged, and isolate the damage at the data page level. At any point in time, the current list of known damaged pages can be found in the [suspect_pages] table of system database [msdb]. In addition to torn page detection and isolation, SQL 2005 also introduces the ability to perform a Restore in which only those data pages which are damaged are overwritten. This will allow you to bring a database which is slightly damaged back into service quickly. Note: Do NOT take the database offline before you start this procedure. To perform an online torn page restore using Microsoft SQL Server 2005 or 2008 Enterprise, Data Center, or Developer Editions 1. Change to Full Recovery Model, if the database is using the Simple Recovery Model. 2. (Optional) Perform a Database Consistency Check (DBCC CheckDB) on the database to locate any additional damaged pages beyond the one already reported. This can be done as part of step 4. 3. Perform an Online Torn Page Repair Restore of the database as follows: a. Open the Restore Manager. b. On the Source tab, use the Restore By Tree view to find and select the database. c. Open Agent Option. d. Confirm that Automatic Selection is selected. e. Under Subset, select Torn Page Repair - Online. f. Under Recovery Completion State, select Leave database operational. g. (Optional) You may select a Database Consistency Check before the restore, to identify any other damaged or corrupted pages. (Note that this is the only restore type for which a DBCC is permitted before a Restore, because DBCC requires the database to be online.) h. (Optional) You may select a Database Consistency Check after the restore. i. Click OK. j. On the Destination tab, select Restore to Original Location, if it is not selected. k. Start the Restore. 70 Agent for Microsoft SQL Server Guide

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
Perform an Online Torn Page Restore Using Microsoft SQL Server 2005 or 2008 Enterprise, Data Center, or
Developer Editions
70
Agent for Microsoft SQL Server Guide
Perform an Online Torn Page Restore Using Microsoft SQL
Server 2005 or 2008 Enterprise, Data Center, or Developer
Editions
Microsoft SQL Server 2005 and 2008 have the ability to detect when the data in
the database has been damaged, and isolate the damage at the data page level.
At any point in time, the current list of known damaged pages can be found in the
[suspect_pages] table of system database [msdb]. In addition to torn page
detection and isolation, SQL 2005 also introduces the ability to perform a
Restore in which only those data pages which are damaged are overwritten. This
will allow you to bring a database which is slightly damaged back into service
quickly.
Note:
Do
NOT
take the database offline before you start this procedure.
To perform an online torn page restore using Microsoft SQL Server
2005 or 2008 Enterprise, Data Center, or Developer Editions
1.
Change to Full Recovery Model, if the database is using the Simple Recovery
Model.
2.
(Optional) Perform a Database Consistency Check (DBCC CheckDB) on the
database to locate any additional damaged pages beyond the one already
reported. This can be done as part of step 4.
3.
Perform an Online Torn Page Repair Restore of the database as follows:
a.
Open the Restore Manager.
b.
On the Source tab, use the Restore By Tree view to find and select the
database.
c.
Open Agent Option.
d.
Confirm that Automatic Selection is selected.
e.
Under Subset, select Torn Page Repair
–
Online.
f.
Under Recovery Completion State, select Leave database operational.
g.
(Optional) You may select a Database Consistency Check before the
restore, to identify any other damaged or corrupted pages. (Note that
this is the only restore type for which a DBCC is permitted before a
Restore, because DBCC requires the database to be online.)
h.
(Optional) You may select a Database Consistency Check after the
restore.
i.
Click OK.
j.
On the Destination tab, select Restore to Original Location, if it is not
selected.
k.
Start the Restore.