Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 10

With Cheyenne ARCserve for Windows NT and Compaq TurboDAT AutoLoader or Compaq DLT

Get Compaq ProLiant 1000 PDF manuals and user guides View all Compaq ProLiant 1000 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 10 highlights

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 10 Any file backup utility will backup the files in their entirety. To illustrate our point, let's use an example where we allocate a 100MB database device, create a 100MB database on this device, and store 50MB of data in this database. From the operating system's perspective, we have 100MB worth of data, which we need to back up. However, we end up backing up about 50MB of data and 50MB of empty database structures, increasing both the duration of the operation and amount of the backup storage used or needed. You have to back up all database and transaction log files in order to recover. SQL Server keeps track of all user databases in the master database, and time stamps all files. If you restore only selected files, SQL Server will detect inconsistency between the files and the information in the master database. Likewise, in order to restore a database, you have to restore all database and transaction log files to keep them all consistent. Incremental backups (backups of changes since the last full backup) or partial backups (backups of selected subsets of data, such as a particular database) are not possible. Disk I/O associated with file-based backup utilities typically occurs in 64KB blocks, thus improving performance. Some file backup utilities support software data compression. Software data compression can yield the fastest backup throughputs, but is demanding of the system CPU. Off-line backups can take advantage of verifying the data (backup with verify) on the tape after backup. This guarantees data integrity on the tape. With Cheyenne ARCserve for Windows NT and Compaq TurboDAT AutoLoader or Compaq DLT Library, you can group tapes together to increase the capacity. Groups of tapes appear to the host as one continuous tape with an increased capacity, and the next tape in the group is automatically and transparently loaded once one tape is filled up. File-based backup utilities cannot be used to archive a database that has been created on raw devices. Creation of database device files on 'raw' (unformatted) partitions is supported by both Windows NT and SQL Server to enhance I/O performance by bypassing the file system. The only way to archive such a database is by using online backup. SQL Server-based database and transaction log backups have the following characteristics: SQL Server will dump a database or a transaction log while permitting access to any and all user databases. All processing in databases not being dumped continues normally. Any changes7 to databases not being dumped remain unaffected. In the database being dumped, any change to a data page already dumped occurs immediately. This change, however, will not be included in the backup image. If a transaction intends to update a data page or an index page not yet backed up, SQL Server places this transaction on an internal queue, dumps that page before allowing any change to this page, and then proceeds with the transaction. In other words, the database image is the data captured at the instant the DUMP command is issued. We will discuss performance implications of this type of backup later in the document. SQL Server will backup only pages containing data - both data and index pages. As a result, no unnecessary data will be backed up. However, during the load operation, SQL Server will initialize all unused pages in addition to reloading all used data and index pages, thus consuming more time as compared to the dump operation. 7 We are only concerned with insert, delete or update requests. Look-up (read-only) queries don't concern us here since they have no affect on the user data.

  • 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
Compaq Backup and Recovery for Microsoft SQL Server 6.x
Page
10
Any file backup utility will backup the files in their entirety.
To illustrate our point, let’s use an
example where we allocate a 100MB database device, create a 100MB database on this device,
and store 50MB of data in this database.
From the operating system’s perspective, we have
100MB worth of data, which we need to back up.
However, we end up backing up about 50MB of
data and 50MB of empty database structures, increasing both the duration of the operation and
amount of the backup storage used or needed.
You have to back up all database and transaction log files in order to recover.
SQL Server keeps
track of all user databases in the master database, and time stamps all files.
If you restore only
selected files, SQL Server will detect inconsistency between the files and the information in the
master database.
Likewise, in order to restore a database, you have to restore all database and
transaction log files to keep them all consistent.
Incremental backups (backups of changes since the last full backup) or partial backups (backups of
selected subsets of data, such as a particular database) are not possible.
Disk I/O associated with file-based backup utilities typically occurs in 64KB blocks, thus
improving performance.
Some file backup utilities support software data compression.
Software data compression can
yield the fastest backup throughputs, but is demanding of the system CPU.
Off-line backups can take advantage of verifying the data (backup with verify) on the tape after
backup.
This guarantees data integrity on the tape.
With Cheyenne ARCserve for Windows NT and Compaq TurboDAT AutoLoader or Compaq DLT
Library, you can group tapes together to increase the capacity.
Groups of tapes appear to the host
as one continuous tape with an increased capacity, and the next tape in the group is automatically
and transparently loaded once one tape is filled up.
File-based backup utilities cannot be used to archive a database that has been created on raw
devices.
Creation of database device files on ‘raw’ (unformatted) partitions is supported by both
Windows NT and SQL Server to enhance I/O performance by bypassing the file system.
The only
way to archive such a database is by using online backup.
SQL Server-based database and transaction log backups have the following characteristics:
SQL Server will dump a database or a transaction log while permitting access to any and all user
databases.
All processing in databases not being dumped continues normally.
Any changes
7
to
databases not being dumped remain unaffected.
In the database being dumped, any change to a
data page already dumped occurs immediately.
This change, however, will not be included in the
backup image.
If a transaction intends to update a data page or an index page not yet backed up,
SQL Server places this transaction on an internal queue, dumps that page before allowing any
change to this page, and then proceeds with the transaction.
In other words, the database image is
the data captured at the instant the DUMP command is issued.
We will discuss performance
implications of this type of backup later in the document.
SQL Server will backup only pages containing data - both data and index pages.
As a result, no
unnecessary data will be backed up.
However, during the load operation, SQL Server will
initialize all unused pages in addition to reloading all used data and index pages, thus consuming
more time as compared to the dump operation.
7
We are only concerned with insert, delete or update requests.
Look-up (read-only) queries don’t concern us
here since they have no affect on the user data.