Transcript Database - bYTEBoss

Liverpool John Moores University
CMPCP0031
Database Development in SQL Server 2005
Lecture 10
Backup & Recovery
Software & Hardware Failure
• A backup determines how a copy of the databases or transaction logs
is constructed and which backup device is used for these operations
– These precautions have to be taken to prevent data loss
– You can lose data as a result of different hardware or software problems
• The reasons for data loss can be grouped as follows
– Program Exceptions
– Administrator (human) Mistakes
– Computer Failures (system crash)
– Disk Failures
– Cataclysms (fire, earthquake, nuclear winter :) or Thefts
…cont
• Program Exceptions
– During program execution, conditions may arise that terminate
the program, such program exceptions concern only the accessed
database and usually have no impact on the entire database system
– As these program exceptions are based on faulty program logic, the
recovery should be performed by the developer, who has to handle
exceptions using the COMMIT and ROLLBACK statements
• Administrator Mistakes
– Database users with sufficient security permissions or the
database administrator may accidentally lose or corrupt data
such
…cont
• Computer Failures
– These specify different hardware or software problems
– A system crash is an example of a computer failure as
the contents of the computer’s memory may be lost
• Disk Failures
– A disk failure occurs either when a head in the disk crashes (literally)
or when the input output system discovers corrupt data blocks
• Cataclysms or Thefts
– The system must keep enough data to recover from the problem, this
is normally achieved by means of a backup device that is not on site
Transaction Logs
• SQL Server 2005 stores the old records of each row
that has been modified during a particular transaction
– This is crucial in case a problem occurs during the execution
of the transaction and all statements have to be rolled back
– As soon as SQL Server 2005 detects such a situation, it uses the
stored records to bring the database back to a consistent state
• SQL Server 2005 stores these old records in the transaction log
– The log contains the before and after values of each column and can
be used to perform either an automatic recovery or a restore process
– The transaction log is generally used to prevent the loss
of all changes executed since the last database backup
Backup Types
• SQL Server 2005 provides static as well as dynamic backup
– Dynamic backup means that a database backup
can be performed while users are working on data
– In contrast to some other DBMSs, which backup all databases as one,
SQL Server 2005 does the backup of each database separately, this
method improves security when it comes time to a restore
• SQL Server 2005 supports four different backup methods
– Full Database Backup
– Differential Database Backup
– Transaction Log Backup
– Database File (or FileGroup) Backup
…cont
• A Full Database Backup captures the state of
the database at the time the backup is started
– During the Full Database Backup, the system copies the
data as well as the schema of every object in the database
– When a Full Database Backup is executed dynamically, SQL Server
2005 records any activity that took place during the backup so even the
uncommitted transactions in the transaction log are included in the backup
• Using Differential Database Backup, only database objects that
have changed since the last Full Database Backup are recorded
– As in the Full Database Backup, any activity that took place
during the Differential Database Backup is backed up too
– The advantage of a Differential Database Backup is speed,
because it minimizes the time required to backup a database
…cont
• Transaction Log Backup considers only changes recorded in the log
– This form of backup is thus not based on physical objects of the
database but on logical operations, that is, changes executed
using the DML statements INSERT, UPDATE and DELETE
– Again, because the amount of data is smaller, this operation can be
performed significantly quicker than the Full Database Backup and
slightly quicker than a Differential Database Backup
• A Database File Backup backs up specific database files
– In this case, SQL Server 2005 backs up only files you specify
Performing Backup
• You can perform backup operations using either
– SQL Server Management Studio
– T-SQL Statements
• Before a database or transaction log backup can be done, we need
to specify a backup device, SQL Server Management Studio allows
you to specify disk devices and tape devices in a similar manner
Backup in SSMS
• In Object Explorer, locate Backup Devices under Server Objects
– Right click Backup Devices and choose New Backup Device
– If Tape can not be selected, then no tape devices exist on the computer
– After you specify a backup device, a database backup can be done
…cont
• In Object Explorer, locate the database (say cmpghugh)
– Right click the database then choose Tasks and then Backup
– Alternatively, right click Backup Devices then choose Backup Database
…cont
• On the Options tab, you can append or overwrite an existing backup
– For verification of the database backup, select Verify Backup
Backup in T-SQL
• All backup operations can be executed using two T-SQL statements
– BACKUP DATABASE
– BACKUP LOG
• SQL Server 2005 allows you to backup to these backup devices
– Disc Devices
– Tape Devices
– Disk backups are the most common device for storing backups, they can
be located on a local disk or, more usually, on a remote disk on a shared
network resource
– Tape devices are generally used in the same way
as disk devices, however, when you backup to
the tape device must be local
tape,
…cont
• BACKUP DATABASE is used to perform a FULL database backup
– Can also be used to perform a DIFFERENTIAL database backup
BACKUP DATABASE {database_name | @variable}
TO device_list
[MIRROR TO device_list]
[WITH option_list]
• BACKUP LOG is used to perform a backup of the transaction log
BACKUP LOG {database_name | @variable}
TO device_list
[MIRROR TO device_list]
[WITH option_list]
Which Databases ?
• These databases should be backed up on frequent schedule
– The master database
– The production databases
– The msdb database
• Backup the master database
– The master database is the most important in SQL Server 2005
because it contains data about all the databases in the system
– You can perform a FULL backup of the master database only
– Without a backup of the master database, you must completely
reconstruct each database because when the master database is
corrupt, all references to existing user defined databases are lost
…cont
• Backup the production databases
– Always make a FULL backup once they have been constructed as
DIFFERENTIAL backups or backups of the transaction log can
not be applied without a FULL database backup
– Always backup the database once an index has been constructed, this
saves time during the restore process because the index structures are
backed up together with the data and do not have to be reconstructed
• Backup the msdb database
– The msdb database is used to store alerts, jobs and operations
which are subsequently used by the SQL Server Agent service
– The msdb database can be reconstructed with instmsdb.sql script
Minimize System Down Time
• There are some strategies to minimize down time due to disk failures
– Two general strategies that SQL Server 2005 supports are
• STANDBY SERVER
– A standby server is just another server that is standing
by in case something happens to the production server
• RAID (redundant array of inexpensive disks)
– RAID is when multiple physical disks construct a single logical block
– RAID technology affords reliability at the cost of performance
– Generally, there are six RAID levels (zero to five) but only three
are significant to database systems, RAID zero, one and five
High Availability
• SQL Server 2005 supports the following technologies
– Log Shipping & Database Mirroring
– Failover Clustering
• Log Shipping allows the transaction logs from one database
system
to be constantly sent to and then used by another
– This allows you to have a warm standby server which holds an
exact copy (a mirror) of the database on the production server
• Any Database Mirroring system usually requires three servers
– Principal Server
– Mirror Server
– Witness Server
…cont
• Failover Clustering in SQL Server 2005 achieves high availability
– Here, the operating system and SQL Server 2005 work as
one to provide availability in the event of system failures
– When a system (a node) fails, SQL Server 2005 shuts down
and the Microsoft Cluster Service transfers its resources and operations
to another equally configured node in the cluster
Recovery Types
• When a transaction is executed, SQL Server 2005 is responsible
either for executing the transaction completely and recording its
changes permanently in the database or for guaranteeing that the
transaction has no effect on the database
– This approach ensures that the database is consistent in case of failure
– Failures do not corrupt the database as such, rather they affect
transactions that are in progress at the same time as the failure
• SQL Server 2005 supports both automatic and manual recovery
Automatic Recovery
• Automatic recovery is a fault tolerant feature that SQL Server 2005
executes every time it is restarted following a failure or shut down
– The automatic recovery process checks to see if the restoration
of databases is necessary, when it is, each database is returned
to it’s last consistent state using the transaction log
– SQL Server 2005 examines the transaction log from the last check
point right up to the point at which the system failed or was shut down
– A check point is the most recent point at which all data changes are
written permanently to the database, thus a check point ensures the
physical consistency of the data
…cont
• The transaction log contains committed transactions . . .
– Transactions that are successfully executed, but their
changes have not yet been written to the database
• . . . and uncommitted transactions
– Transactions that are not successfully executed
before
a shut down or failure occurred
– SQL Server 2005 rolls forward all committed transactions, thus making
permanent changes to the database and then undoes the part of the
uncommitted transactions that occurred before the check point
– SQL Server 2005 first performs the automatic
recovery of the master database, followed by
the recovery of other system databases then
the user defined databases are recovered
Manual Recovery
• A manual recovery of a database specifies the application of the
backup of your database and all transaction logs in sequence
– The database is then in the same consistent state as it was at the
point when the transaction log was backed up for the very last time
– When you recover a database using a Full Database Backup, SQL
Server 2005 first reconstructs all database files in the correct
locations and then the system reconstructs all database objects
– In contrast to previous versions, SQL Server 2005 processes
certain forms of recovery process dynamically which allows you
to restore either an entire database file or a single table of data
– Microsoft calls dynamic recovery online restore
Restore in SMSS
• In Object Explorer, locate the database (say cmpghugh)
– Right click the database then choose Tasks, Restore and then Database
– Select the databases/devices to which and from you want to restore
– Check the type of the backup that you want to perform (in this case FULL)
…cont
• On the Options tab, you can select the appropriate restore options
Restore in T-SQL
• All restore operations can be executed using two T-SQL statements
– RESTORE DATABASE
– RESTORE LOG
• RESTORE DATABASE is used to perform a database restore
RESTORE DATABASE {database_name | @variable}
[FROM device_list]
[WITH option_list]
Restore System Databases
• The corruption of the master database can be a catastrophe
– The restore process of the master database is quite different
from the same process concerning user defined databases
– A corrupt master database will show up through various failures
• There are two ways to go to recover the master database
– The easier way is when you can start the database system, you just
restore the master database from the last Full Database Backup
– To restore the master database, start the database server in
single user mode then restore the master database together
with all other databases using the last Full Database Backup
…cont
• The restore process for the other system databases is similar
– Taking the msdb database as an example
– The msdb database needs to be restored from a backup
when either the master database has been reconstructed
or the msdb database itself has been corrupted
– When the msdb database is corrupt, restore it using the existing backups
Recovery Models
• SQL Server 2005 supports the standard database recovery models
– These models allow you to control to what extent you are ready
to risk losing committed transactions if a database is corrupted
– The choice of a recovery model has an impact on the size of the
transaction log, thus on the time period needed to back up the log
• SQL Server 2005 supports this recovery models
– Full Recovery
– Simple Recovery
Full Recovery
• In a Full Recovery, all operations are written to the transaction log
– This model provides complete protection against disk failure which means
that you can restore your database up to the last committed transaction
that is stored in the log file
– Data can be recovered to any point in time (prior to the point of failure)
– Besides point in time recovery, the Full Recovery model allows you
recover to a log mark which corresponds to a specific transaction
– The disadvantage of this recovery model is that the
transaction log may be very voluminous and the files on
the disk containing the log will populated very quickly
to
Simple Recovery
• In the Simple Recovery model, the transaction log is not used
to protect your database against any disk failure whatsoever
– You can recover a corrupted database only by using
a Full Database or a Differential Database Backup
• The backup strategy for this model is very simple
– Restore the database using existing backups and if
differential backups exist, apply the most recent one
– The advantage of Simple Recovery is that performance
is very high and log space requirements is very low
– On the other hand, this model requires the most work
because all changes since the most recent database
(or differential) backup must be redone manually
Recovery Mark
• SQL Server 2005 allows you to use the
transaction log to recover to a specific mark
– Log marks correspond to a specific transaction and
are inserted only when the transaction commits
– This allows the marks to be tied to specific work and
provides the ability to recover to a point that includes
or excludes this work