Transcript Unix System Admin

5. SQL Restore tasks
• Objectives
– Learn howto recover databases from disaster
• Contents
–
–
–
–
View database and transaction log backup files
Perform a complete data restoration of a user database
Recover a database to a specified point in time
Restore a database to standby mode using
• Practicals
– Recover your databases
• Summary
Determining the Data Restoration Sequence
• msdb database, SQL Server 2000 records the details of the backup and
the restore history in the msdb database
– which devices or files the backup is stored on,
– Who performed the backup,
– and at what time.
• If the msdb database is damaged, you should restore the msdb database
from backup before you restore any user databases.
• Check what backups you have in the Management container, right click
and select properties or double click and view contents
• Use the transact SQL
RESTORE LABELONLY FROM gurka
RESTORE HEADERONLY FROM gurka
RESTORE FILELISTONLY FROM gurka
• If you do not have a recent backup of the msdb database or are restoring
to another SQL Server 2000 instance.
– read each backup file and add the information to the msdb database
– Restore msdb history
Retrieving Backup Media Information
To retrieve backup media information
1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain
controller as Administrator.
2. In the SQL Server Enterprise Manager console tree, expand the default
instance, expand the Management container, and then click Backup. The
backup devices for this SQL Server 2000 instance are displayed in the
details pane.
3. In the details pane, right-click SSEMDBTLogBackup and then click
Properties. The Backup Device Properties – SSEMTLogBackup dialog
box appears displaying the filename associated with this backup device.
4. Click the View Contents button. The View Backup Media Contents dialog
box appears, displaying the contents of the SSEMTLogBackup device.
Details regarding three transaction log backup sets appear.
5. Switch to SQL Query Analyzer.
6. On the toolbar, click the Load SQL Script button. The Open Query File
dialog box appears.
7.
RESTORE LABELONLY FROM gurka
RESTORE HEADERONLY FROM gurka
RESTORE FILELISTONLY FROM gurka
Back up the transaction log without truncation
• Before you attempt to restore a backup to a database that is
still functioning, be sure to restrict user access to it.
• Disk is intact, software error/smartboy caused damage
– disk containing a data file failed,
– SQL Server 2000 installation failed
– recover data to an earlier point in time.
• If a disk containing a data file fails, SQL Server Enterprise
Manager will display the database containing the damaged
data file as suspect.
• If you discover you have a suspect database, you should
immediately back up the transaction log without truncation
before you attempt any restoration of your dat
Back up the transaction log without truncation, cont.
•
If you discover you have a suspect
database
•
Backing up the current transaction log
(using the Without Truncation option)
allows you to recover up to the point
of failure.
1.
2.
3.
4.
•
Goto the Databases container,
right-click the suspect database in the details pane,
point to All Tasks,
and then click Restore Database.
Next, you should replace the failed disk.
If your data is using RAID for fault
tolerance, all that you need to do is to
simply add the new drive to the
RAID system.
Begin restoration suspect Database
1. Goto the Databases container,
2. right-click the suspect database in
the details pane,
3. point to All Tasks,
4. and then click Restore Database.
•
the First Backup To Restore dropdown list allows you to select
which backup to try first
•
You can make a combination of
backup sets that you can use to
complete the restoration.
Howto restore suspect Database
1. Goto the Databases container,
2. right-click the suspect database in the
details pane,
3. point to All Tasks,
4. and then click Restore Database.
5. Select options tab
•
You can choose to leave the database
operational, not possible to restore
additional transaction logs.
•
You can choose to leave the database
read-only and able to restore
additional transaction log files.
•
You can leave database read only adn
able to restore more transaction logs