Computer Associates SQLSTQ99000600 Microsoft SQL Guide - Page 113

Recovery Models, Simple, Bulk-logged, Backup Method

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

Microsoft SQL Server Database Basics Recovery Models The SQL Server recovery model is a decision that manages the risk of lost information in case of a disaster by controlling the level of transaction log involvement. You may change recovery models on a per-database basis to help you manage database maintenance tasks. Depending on the version of Microsoft SQL Server and the recovery model inherited by a database, certain CA ARCserve Backup backup options may not be available. In a given SQL Server instance, your databases can have a mix of the following recovery models: ■ Simple -- Allows your database to be recovered only to the time of a backup. Transaction log backups are not permitted so any work done after the most recent backup must be redone. File and FileGroup backups are also not permitted, though in SQL 2005 and later, partial database backups are still permitted. The risk of lost information exists and is limited to all changes made since the last backup. ■ Full -- Allows your database to be recovered to the point of failure, or any point in time. Including transaction log backups is required so you can recover up to a specific point in time. File and FileGroup backups or database differential backups can be optionally included. This model has the lowest risk of data loss and the greatest flexibility during recovery. ■ Bulk-logged -- Allows high-performance batch operations. Transaction log backups are required but you can recover only until the time of backup. You should perform transaction log backups to truncate the transaction log regularly. File and FileGroup backups or database differential backups can be optionally included. Backup Method Simple Database Full Required Database Differential (not available for master db) Optional Transaction Log Not Available File and FileGroup (requires SQL Server 2000 or later) Not Available Partial Database Optional (Requires SQL Server 2005 or later) Backup Transaction Log After Database Full Required Optional Required Optional Optional Optional Bulk-Logged Required Optional Required Optional Optional Optional Appendix C: Backup and Recovery Best Practices 113

  • 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
Microsoft SQL Server Database Basics
Appendix C: Backup and Recovery Best Practices
113
Recovery Models
The SQL Server recovery model is a decision that manages the risk of lost
information in case of a disaster by controlling the level of transaction log
involvement. You may change recovery models on a per-database basis to help
you manage database maintenance tasks. Depending on the version of Microsoft
SQL Server and the recovery model inherited by a database, certain CA
ARCserve Backup backup options may not be available.
In a given SQL Server instance, your databases can have a mix of the following
recovery models:
■
Simple --
Allows
your database to be recovered only to the time of a
backup. Transaction log backups are not permitted so any work done after
the most recent backup must be redone. File and FileGroup backups are also
not permitted, though in SQL 2005 and later, partial database backups are
still permitted. The risk of lost information exists and is limited to all changes
made since the last backup.
■
Full --
Allows
your database to be recovered to the point of failure, or any
point in time. Including transaction log backups is required so you can
recover up to a specific point in time. File and FileGroup backups or database
differential backups can be optionally included. This model has the lowest
risk of data loss and the greatest flexibility during recovery.
■
Bulk-logged
-- Allows high-performance batch operations. Transaction log
backups are required but you can recover only until the time of backup. You
should perform transaction log backups to truncate the transaction log
regularly. File and FileGroup backups or database differential backups can be
optionally included.
Backup Method
Simple
Full
Bulk-Logged
Database Full
Required
Required
Required
Database Differential
(not available for
master db)
Optional
Optional
Optional
Transaction Log
Not Available
Required
Required
File and FileGroup
(requires SQL Server
2000 or later)
Not Available
Optional
Optional
Partial Database
(Requires SQL Server
2005 or later)
Optional
Optional
Optional
Backup Transaction
Log After Database
Optional
Optional