Computer Associates SQLSTQ99000600 Microsoft SQL Guide - Page 122

How the Create Index Statement Impacts File and FileGroup Backups, Partial Backups

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 122 highlights

Backup Concepts How the Create Index Statement Impacts File and FileGroup Backups The Backup statement requires that you back up entire FileGroups affected by a Create Index statement. This requirement exists in the following situations: ■ If you create an index on a FileGroup, you must back up that entire FileGroup in a single backup operation. Microsoft SQL Server does not allow backups of individual files that are part of the affected FileGroup. ■ If you create an index on a FileGroup separate from the FileGroup in which the table resides, then you must backup both FileGroups (the FileGroup containing the table and the FileGroup containing the newly created index) together. ■ If you create more than one index on a FileGroup separate from the FileGroup in which the table resides, you must back up all the FileGroups immediately to accommodate these different FileGroups. The Backup statement detects all of these FileGroup situations and communicates the minimum number of FileGroups that you must back up. Microsoft SQL Server reports this information when the backup job is run in one or more error messages, which will be written to the Activity Log by the Agent for Microsoft SQL Server. Partial Backups Microsoft SQL Server 2005 introduces a special type of Files-and-FileGroups backup called a Partial Backup. These backups automatically select the Primary FileGroup, along with all other FileGroups that are not Read-Only. If the database itself is Read-Only, then only the Primary FileGroup will be included. Unlike Files-and-FileGroups backups, Partial Backups can be performed on databases that use the Simple Recovery Model, because all of the data that can be changed is included. If you have a large database that contains a large amount of static data in Read-Only FileGroups, you can use a Partial Backup to reduce the size and time required to perform regular backups. As long as you retain the latest Full Database backup and the database structure does not change, you can use Partial Full and Partial Differential backups without needing to perform another Full Database backup. Also, you can perform a Partial Restore from a Partial Backup, provided all of the desired FileGroups are contained within the Partial Backup session. You can also perform a Database Restore from a Partial Backup, with the last Database Full backup as a pre-requisite. 122 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
Backup Concepts
122
Agent for Microsoft SQL Server Guide
How the Create Index Statement Impacts File and FileGroup Backups
The Backup statement requires that you back up entire FileGroups affected by a
Create Index statement. This requirement exists in the following situations:
■
If you create an index on a FileGroup, you must back up that entire FileGroup
in a single backup operation. Microsoft SQL Server does not allow backups of
individual files that are part of the affected FileGroup.
■
If you create an index on a FileGroup separate from the FileGroup in which
the table resides, then you must backup both FileGroups (the FileGroup
containing the table and the FileGroup containing the newly created index)
together.
■
If you create more than one index on a FileGroup separate from the
FileGroup in which the table resides, you must back up all the FileGroups
immediately to accommodate these different FileGroups.
The Backup statement detects all of these FileGroup situations and
communicates the minimum number of FileGroups that you must back up.
Microsoft SQL Server reports this information when the backup job is run in one
or more error messages, which will be written to the Activity Log by the Agent for
Microsoft SQL Server.
Partial Backups
Microsoft SQL Server 2005 introduces a special type of Files-and-FileGroups
backup called a Partial Backup. These backups automatically select the Primary
FileGroup, along with all other FileGroups that are not Read-Only. If the
database itself is Read-Only, then only the Primary FileGroup will be included.
Unlike Files-and-FileGroups backups, Partial Backups can be performed on
databases that use the Simple Recovery Model, because all of the data that can
be changed is included. If you have a large database that contains a large
amount of static data in Read-Only FileGroups, you can use a Partial Backup to
reduce the size and time required to perform regular backups. As long as you
retain the latest Full Database backup and the database structure does not
change, you can use Partial Full and Partial Differential backups without needing
to perform another Full Database backup.
Also, you can perform a Partial Restore from a Partial Backup, provided all of the
desired FileGroups are contained within the Partial Backup session. You can also
perform a Database Restore from a Partial Backup, with the last Database Full
backup as a pre-requisite.