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

Compaq Backup and Recovery for Microsoft SQL Server 6.x, Microsoft SQL Server System Administrator's

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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 70 16.5 GB/hr with 4 DLT drives, which exceeds the rate of the log volume. Therefore the backup time must be calculated as: [27.3 / 16.5] + [.85 / 12.5] = 1.72 hours, or about 1 hour & 43 minutes. If only 2 DLT's were to be used to back up the database, a system throughput of 9.1 GB/hr would then be the limitation (Chart-6) for both log and data, simplifying the calculation: [27.3 + .85] / 9.1 = 3.1 hours, or about 3 hours & 6 minutes. In the second case however, it is possible that additional time may be required to change the tapes in the drives, since the total space required may exceed the capacity of two 10/20-GB cartridges (depending on the compression achieved). Keep in mind that all such calculations yield only rough estimates of the required time, and should be used conservatively. Customers should perform their own tests to measure actual time, and in a mission critical environment should allow for extra time to incorporate any unforeseen performance degradation. The goal of this section is to supplement information on recovery processes found in sources such as the Microsoft SQL Server System Administrator's Companion. The Administrator's Companion provides detailed information on different recovery scenarios, such as recovering from media failure, recreating lost devices, recreating and reloading lost databases, restoring and reloading the master database, etc., along with examples of each. This section provides additional considerations and a performance analysis of the recovery process. Following are some functional considerations you should be aware of when loading a database after a failure: Make sure you create a database of the same or greater size than your original database. You will not be able to load a database into a smaller frame structure, even if only a portion of the database is used. If you are reloading the database at another server, the code page and sort order must match with the original installation. To move data between servers with different code pages and/or sort orders, use the BCP utility or SQL Transfer Manager. Create the database in the same fashion as the original one, with respect to its physical characteristics, such as device allocation, log space allocation, segment allocation, etc. The best method to ensure identical physical characteristics is to save the SQL scripts used to initially create your database(s), and rerun them prior to loading the database(s). The database will be loaded in the same order that it was created, with respect to device allocation. When recovering a database from a database dump and a series of subsequent transaction log dumps, all dump images must be loaded in sequence and successfully. If, for example, you load from a database dump, and another user updates a record in this database before you have a chance to load the next transaction log dump, the transaction log load will fail. When this happens, you are forced to start over and reload from the last database dump. Evidently, the update transaction occurring between this load will be lost. To prevent this situation from happening, start SQL Server in a single user mode. During a restore operation, the damaged database must be dropped, recreated and the most recent copy of the database dump is then loaded from the backup media. If the database is not damaged, you can reload the backup over the old database. SQL Server locks the database being restored for the duration of the operation. However, all other databases remain open for access.

  • 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
70
16.5 GB/hr with 4 DLT drives, which exceeds the rate of the log volume.
Therefore the backup time
must be calculated as:
[27.3 / 16.5] + [.85 / 12.5] = 1.72 hours, or about 1 hour & 43 minutes.
If only 2 DLT’s were to be used to back up the database, a system throughput of 9.1 GB/hr would then
be the limitation (Chart-6) for both log and data, simplifying the calculation:
[27.3 + .85] / 9.1 = 3.1 hours, or about 3 hours & 6 minutes.
In the second case however, it is possible that additional time may be required to change the tapes in
the drives, since the total space required may exceed the capacity of two 10/20-GB cartridges
(depending on the compression achieved).
Keep in mind that all such calculations yield only rough estimates of the required time, and should be
used conservatively.
Customers should perform their own tests to measure actual time, and in a
mission critical environment should allow for extra time to incorporate any unforeseen performance
degradation.
The goal of this section is to supplement information on recovery processes found in sources such as
the
Microsoft SQL Server System Administrator’s Companion
.
The Administrator’s Companion
provides detailed information on different recovery scenarios, such as recovering from media failure,
recreating lost devices, recreating and reloading lost databases, restoring and reloading the
master
database, etc., along with examples of each.
This section provides additional considerations and a
performance analysis of the recovery process.
Following are some functional considerations you should be aware of when loading a database after a
failure:
Make sure you create a database of the same or greater size than your original database.
You will
not be able to load a database into a smaller frame structure, even if only a portion of the database
is used.
If you are reloading the database at another server, the code page and sort order must match with
the original installation.
To move data between servers with different code pages and/or sort
orders, use the BCP utility or SQL Transfer Manager.
Create the database in the same fashion as the original one, with respect to its physical
characteristics, such as device allocation, log space allocation, segment allocation, etc.
The best
method to ensure identical physical characteristics is to save the SQL scripts used to initially create
your database(s), and rerun them prior to loading the database(s).
The database will be loaded in the same order that it was created, with respect to device allocation.
When recovering a database from a database dump and a series of subsequent transaction log
dumps, all dump images must be loaded in sequence and successfully.
If, for example, you load
from a database dump, and another user updates a record in this database before you have a chance
to load the next transaction log dump, the transaction log load will fail.
When this happens, you
are forced to start over and reload from the last database dump.
Evidently, the update transaction
occurring between this load will be lost.
To prevent this situation from happening, start SQL
Server in a single user mode.
During a restore operation, the damaged database must be dropped, recreated and the most recent
copy of the database dump is then loaded from the backup media.
If the database is not damaged,
you can reload the backup over the old database.
SQL Server locks the database being restored for
the duration of the operation.
However, all other databases remain open for access.