Media Explorer for Microsoft SQL Server - How To
Topics |
How To
Determine Required Archive Files for Data Recovery
Catalog the Media
Restore Procedures
Understand
the Number of Media Required for a Restore
Restore Options
Recover a Database
Determine Required Archive Files for Data Recovery
Required Capability: none
To determine
which archive files are needed to complete a data recovery:
- Determine the time for which you want to recover the data. This is based
on your decision to recover either the latest set of data as in the case of
a hardware failure or to a earlier time as in the case of a database or software
corruption that was captured by more recent data protection operations.
- Once you have determined the recovery time, determine the number of subclients
that will be required in the recovery.
If the data being recovered is of File System type and only part of the data
is required or the entire Database application was secured by a data protection
operation of the default subclient, then the number of subclients required can
be as few as one.
If the entire File System is required or the data being recovered is a database
application, then most likely all subclients that contained any part
of this application will be required.
|
This identification process is greatly facilitated
if you had run Media Information and CommCell Configuration reports
while the CommServe was still available, and have those reports available
as reference materials. |
- Catalog all the required media using Media Explorer, and view the archive
files in the Archive File Catalog
Database pane. To determine the number of subclients that exist, sort by
clients and examine the number of unique application IDs per client.
- Once you have determined the time of the recovery and the number of subclients
that are required, using the Backup Time column, select the first Full data
protection operation prior to the desired recovery time.
- Continue selecting each non-full data protection operation for that subclient
until:
- you reach the desired recovery time
- you reach the next Full
- there are no more archive files
(At which option your stop is dependent upon your particular recovery scenario.)
- Step 5 needs to be executed for each subclient that was determined to be
part of this client's recovery.
- Repeat for each client.
Catalog the Media
Before You Begin
- Install Media Explorer from the installer.
- Make sure no other applications or services are attempting to access the
library.
- Have your encryption pass-phrases available, if applicable.
- Have your Media Information and CommCell Configuration reports at hand to
facilitate your identification and location of media archive files.
|
Regularly running the above reports prior to the
need for a disaster recovery will facilitate your identification and
location of media archive files. |
To Catalog the Media
using Media Explorer:
- From whichever folder you installed Media Explorer, run the
MediaExplorer.exe. The
Current database file path is created and defaults
to the DrCatalogDb.dat as indicated in
the Media Explorer window.
|
It is possible to perform a recovery using only
the default archive database. However, if recovering large numbers of
or many types of archive files makes locating files cumbersome, you
can create additional database file paths using the New button.
The New and Open buttons can be used to start cataloging
to a new catalog or to open an existing catalog database. This is useful
in situations where you would like to use Media Explorer over multiple
sessions and not overwrite previous catalog operations.
|
- Detecting the Media - Selecting the tape or magnetic/optical media option.
Use
the Media Information and CommCell Configuration reports to assist you in determining
from which media it will be necessary to restore. If you do not have any reports
saved, you will have to manually determine the required media. This involves
finding the correct (latest) archive file(s) for backups you want to restore.
For a locally-attached tape drive/library:
- manually load the media into the drive
- select the Tape option
- click Drive Detect to populate the tape mount paths in this field
(i.e., tape0, tape1, tape2, etc., depending on your SCSI settings). The
drive shown matches the SCSI ID setting for the device.
- select the drive on which it is loaded
- identify the Media Type by selecting from the list
|
A full DLT 7000 tape will take approximately two
hours to catalog. Be certain you have enough disc space on your local
computer to accommodate the files. |
For a magnetic volume:
- Select the Magnetic/optical option
- Browse to or type the drive path for the magnetic volume path. (i.e.,
F:\, where F
is the drive which holds the magnetic volume)
- Directly beneath CV_magnetic folder,
select the volume folder in which the archive files (AF_nnn)
reside, and click OK to continue.
|
The CV_MAGNETIC
folder structure represents the top level of the magnetic mount path.
The V_nn folder represents a volume
which is equivalent to a storage policy copy.
The AF_nnn folders represent the
individual archive files within a volume. Each backup creates one or
more archive files, depending on the
iDataAgent.
|
- If the magnetic volume to be cataloged belongs to a de-duplicated
library, check the De-Duplicated Data checkbox.
For an optical volume:
- Select the Magnetic/optical option.
- Provide the root drive of the optical media in the volume path.
- Click Catalog Jobs. The catalog operation reads the media for archive
file information and displays it in the
Archive File Catalog Database
pane.
- You may be asked to provide a media password. Type the password and click
OK. If you do not know the media password, contact your software provider
for assistance.
- If data was encrypted using a pass-phrase, you are prompted to enter the
pass-phrase used at the time of the data protection operation.
- The status bar reflects the cataloging process as it is occurring. A message
appears when cataloging is complete. Click OK. You can check for details
in the DrRecovery.log located in the local Media
Explorer folder.
|
An archive file will sometimes span across several
tapes during the course of normal backups. In this case when a catalog
operation is run on the first media, the information on the second media
may not appear to have been cataloged. However, when you perform a recovery
from the first tape, Media Explorer asks you to load the second tape
manually to continue the recovery. |
- For multiple tapes, return to Step 2 and continue cataloging all media to
be used in the restore.
Post Catalog Check
Recovering Data that Span Media
In some cases, a data protection operation will start on one media and complete
on another. This we refer to as a spanned job . At most there can be one spanned
job per any given media.
If after cataloging a media you see an archive file for which the
Catalog column does not display
Found All and the Catalog
State column displays Partial, this indicates
the archive file is spanned to another media. In this case, you need to locate that
other media and catalog it as well. Once the last media that contains the spanned
archive has been reached, you will be prompted to reinsert all the media (starting
with the first) necessary to finish the cataloging process for the spanned job.
Once all media has been successfully cataloged, the
Catalog column should display Found All and
the Catalog State column should display
Complete, and the recovery may take place like any
other recovery. During the recovery of this archive file, you will be prompted to
insert the media as required to complete the recovery.
|
Media Explorer stores cataloged media information in
its database on your local hard drive for future reference. Once your files
have been cataloged, you can go back and obtain this information without
running the catalog operation again. To have the information available on
another computer, however, you would need to run a catalog on that computer. |
Using the Chunk Details Window
You can click View Chunks from the File menu to display the Chunk Details window. This window
provides Media Explorer database details that may be helpful to you.
Related Topics:
Back to Top
The procedures for rebuilding an entire SQL server or instance are:
- Reinstall Microsoft SQL Server to the same location as before using the
same instance names, and applying the same Microsoft Service Pack as the previous
SQL install.
- Restore the master Database
- Restore model and msdb Databases
- Restore Non-System
(User-Defined) Databases
Before You Begin
- Catalog the media.
- All restores from tape must be done using "tape0".
- When restoring a complete instance, it is recommended that you restore your
databases in the following order:
- master database
- model and msdb databases
- non-system (user-defined) databases
To restore the
master database
|
In order to restore the master database, the SQL instance
must be started in Single User mode. |
- Start the SQL server in single user mode.
a) Stop the SQL instance.
b) Open a command prompt and change into the folder where the
sqlservr.exe file exists.
|
For SQL Server 2000
|
This is normally the
[installdir]\<instance>\binn folder.
SQL 2000 syntax: sqlservr.exe -m -c -s [instancename]
|
|
For SQL Server 2005 (and later)
|
This is normally the
[installdir]\<instance>\MSSQL\binn folder.
SQL 2005 syntax: sqlservr.exe -m -c -s [instancename]
|
- From the Media Explorer Display data
corresponding to list, select SQL Server.
- From the Media Explorer Catalog
Pane, select the latest Full database backup archive of the master database.
- From the File menu, click Recover Data.
- From the SQL Server Database Restore dialog box, select
to restore to the SQL server. (Refer to Restore Options.)
|
The master Database cannot be restored to the SQL
server if it is first restored to the disk. |
- Click Start Restore. If data was encrypted using a pass-phrase, you
are prompted to enter the pass-phrase used at the time of the data protection
operation.
- After restoring the database, in the command window type
Ctrl+C to stop the SQL server instance.
- Restart SQL instance in standard mode.
Before You Begin
- Catalog the media.
- All restores from tape must be done using "tape0".
- When restoring a complete instance, it is recommended that you restore your
databases in the following order:
- master database
- model and msdb databases
- non-system (user-defined) databases
To restore the
model or msdb database
Restoring the model and msdb databases is the same as
restoring a non-system
database from Full database backups. Since the SQL
iDataAgent does not allow transaction log
or differential backups for system databases, skip any step describing the restore
of a transaction log or differential backup.
|
Whenever you are restoring system databases, do not
select the Norecovery option in the
Restore Options dialog box.
|
You may restore directly to the SQL Server or restore the backup archives to
disk files. (Refer to Restore Options.) You can also
recover the database in either an offline (Norecovery option) or online state,
and you can recover the database to the either same instance or to a different instance
on the SQL Server.
Before You Begin
To restore a non-system
database
- From the Media Explorer Catalog
Pane, select the archive files that will be required for the restore. (Refer
to Restore from different Backup
Types.)
- From the File menu, click Recover Data.
- From the SQL Server Database Restore dialog box, select
whether to restore to the SQL Server or to disk. For either option, click or
select the appropriate sub-options. (Refer to Restore
Options.) Also, select whether to leave the database being recovered offline
or online and whether to restore the database in-place or out-of-place.
- Click Start Restore. If data was encrypted using a pass-phrase, you
are prompted to enter the pass-phrase used at the time of the data protection
operation.
- Once the restore completes, follow the procedure for
Recover a Database.
These procedures apply to all restores for system or user-defined databases.
To restore from
database backups
- From the Media Explorer Display data
corresponding to list, select SQL Server.
- From the Media Explorer Catalog
Pane, select the full Database backup from the desired time you want to
restore.
- Select latest differential if it exists.
- Select all transaction log backups from:
- after the differential, if one was selected, or
- the full database backup to either the latest time or the next full
database backup
- From the File menu, click Recover Data.
- From the SQL Server Database Restore dialog box, select
whether to restore to the SQL Server or to disk. (Refer to
Restore Options.)
- Recover the database. (Refer to Recover
a Database.)
To restore from
file/file group backups
Databases backed up using file/file group single stream backups can be restored
directly to the SQL Server.
- From the Media Explorer Display data
corresponding to list, select SQL Server.
- From the Media Explorer Catalog
Pane, select the full and latest differential (if one exists) backup archive
for each file/file group for the desired time you want to restore.
- Select all transaction log backups from:
- after the earliest differential, if one was selected, or
- the earliest full file/file group backup to the desired time.
- From the File menu, click Recover Data.
- From the SQL Server Database Restore dialog box, select
whether to restore to the SQL Server or to disk. (Refer to
Restore Options.)
- Recover the database. (Refer to Recover
a Database.)
To restore SQL
Server databases that were backed up using VSS
|
Keep in mind that differential and transaction log
backups cannot be restored after restoring from a VSS full backup. |
- Shut down the SQL server.
- Clear the database folders from all database files that exist (databases
that may have been re-created as part of the server rebuild).
- From the Media Explorer Display data
corresponding to list, select All Data.
- Select an archive file for the database that exists on a single disk in
the catalog pane.
- From the File menu, click Recover Data.
- On the Restore Options dialog box, choose either Restore in place
or Restore out of place. Consider the following when making your selection:
- If more than one database belongs to the same archive file, we recommend
that you use the Restore out of place option. Then you can either
attach the database (using SQL Server Enterprise Manager) from the new location,
or perform a cut-and-paste operation to move the restored files to the original
location first and then attach the database.
- If you selected to restore out of place, as the destination path, enter
just the drive letter (e.g., C:) where the database files should be restored.
Note that for VSS restores, the restored file will retains its full path
so it is not necessary to provide it.
|
If you want to restore the files to a different
location, be aware that the files will be restored with all the
parent directories as well. |
- Click OK. If data was encrypted using a pass-phrase, you are prompted
to enter the pass-phrase used at the time of the data protection operation.
The database files should be brought back to their original locations.
- If there are more data files for this database, select the next file which
resides on the next disk drive and repeat from Step 5.
- Once you have restored all data files for all databases necessary, you may
restart the SQL server. If the database did not exist before the restore, use
Attach Database command in Enterprise Manager.
When restoring from single stream backups you are free to restore directly to
the SQL Server, or if you want to use more advanced TSQL commands (like point in
time or transaction mark restore), you may restore the backup archives to disk and
then load them manually.
When restoring from multiple stream backups, it is necessary to first restore
the backup archives to disk and then manually using TSQL commands, load them into
the database. Depending on the number of backup archives needed and the types of
backups taken, this can be fairly simple or somewhat complex. It may also require
as much disk staging space as the entire database itself.
To restore from
multiple stream backups
- Catalog the media.
- Determine the backup archives required for the restore.
- Restore the backup archives to disk files.
- Repeat the above steps with each media used for all the backup archives.
- Use either SQL Server Enterprise Manager or Query Analyzer to load the database.
(Refer to Restore to disk.)
- Recover the database. (Refer to Recover
a database.)
This option allows the selected archives to be restored directly into the database.
If the database does not already exist, it will be created.
Understanding and changing the VDI timeout value
Both the SQL Server iDataAgent and
Media Explorer use the API (application interface) called VDI (Virtual Device Interface)
to communicate with the SQL server for all backup and restore operations. When issuing
a VDI command to the SQL server, the Media Explorer software must wait for the command
to complete before issuing another command. By default, Media Explorer will wait
120 minutes for the SQL server to respond to a VDI command before giving up. If
the SQL server takes longer then this timeout value Media Explorer will abort the
operation and assume the command failed.
When restoring a database, the VDI timeout represents the time Media Explorer
must wait for the SQL server to become ready to accept data into the database. If
the database files do not exist, a restore command to the SQL server will automatically
create them. The time required to create the file(s) will be the same as when you
first created the database or altered the database and added the file(s).
When restoring a large database when the data files do not exist, you may need
to increase the VDI timeout value on the Restore Options screen to tell Media Explorer
to wait for the SQL server to complete the file re-creation process before restoring
data.
This option appends the WITH REPLACE clause to
the SQL command sent when restoring directly into a database. This option is useful
in situations where database files exist for a particular database and you want
to forcefully overwrite those files. Normally SQL server will prevent overwriting
of an existing data file.
To restore backup
archives to disk files
This option restores the backup archives to files on either the local hard drive
or a mapped network drive. These backup files can then be manually loaded into a
database from SQL Server Enterprise Manager or TSQL commands. For databases backed
up using multiple streams this is the required method.
- Select the archives the same way as any other restore.
- On the restore option screen choose Restore to
disk. Select the destination path. The backup archives will be restored
to the specified folder with the following naming convention:
DB name + "_" + the number of the
ID column
Example: DB_10
This file will be written in standard Microsoft tape device format.
To load a database
using SQL Server Enterprise Manager
- In SQL Server Enterprise Manager, open the databases node.
- For each backup type (i.e., full, differential, log or file/file
group), perform the following:
- Right click and select All tasks > Restore database.
- Enter the name for the database that is to be restored.
- Select the restore option From Device.
- In the Parameters group, click Select Devices.
- In the Restore from options, select
Disk.
- Choose Add, and choose the disk file restored by Media Explorer.
- Click OK.
- Click OK again.
- Choose any other options as needed.
- In the Parameters group, select Restore backup set, and then the
backup type to be restored.
- Click OK.
- Repeat Steps 2 through 13 for each disk file restored by Media Explorer.
To
load a database using TSQL
The following are basic examples of how to load a database using TSQL commands.
If you need to move a database, change filenames or operations, such as point in
time restores, consult the Vendor documentation on the command syntax.
- Open Query Analyzer and connect to the SQL instance as a user who has permission
to load databases.
- Enter the following command:
restore database
[database_name] from disk = 'path to restored file'
Example restoring a database from a full database backup single stream:
restore database sampledb from disk = 'c:\sql\backup\DB_10'
with recovery
Example restoring a database from a full backup and a single log backup using
two streams:
--restore database
restore database sampledb from
disk = 'c:\sql\backup\SQL Backup_100_DB_1',
disk = 'c:\sql\backup\SQL Backup_101_DB_1'
with norecovery
--restore logs
restore log sampledb from
disk = 'c:\sql\backup\SQL Backup_102_DB_1',
disk = 'c:\sql\backup\SQL Backup_103_DB_1'
with recovery
Recovery is the process that tells the SQL Server engine to bring the database to
a consistent online state available for use. After restoring archives with Media
Explorer directly into a database, the database is left in
NORECOVER state. In this state the database cannot
be used.
To bring the database
online
- Start Query Analyzer.
- Enter the following command:
restore database
[database_name] with recovery
- Replace [database_name] with the
actual name of the database (no brackets required). Refer to TSQL online help.
Example:
restore database sampledb with recovery
- Run the command. The SQL server should change the database state from
LOADING to a usable online state.
Back to Top