To differentiate
between archives of the same nameTopics | How To
Media Explorer for Microsoft SQL Server provides the ability to restore, without the use of the CommServe, SQL Server database data secured by data protection operations. This tool is intended for use in situations where it is not possible to use the CommServe to perform the restore. The Media Explorer on Windows software should be installed on the computer to which the data will be recovered. See Install the Media Explorer - Windows in Books Online for step-by-step instructions.
The user of Media Explorer must have a working knowledge of TSQL (native SQL language), as well as the SQL environment and tools necessary to use it. Throughout this text, TSQL script examples are provided to help explain this process.
Restoring a SQL Server database using Media Explorer can be very simple or somewhat complex depending on the type of data protection operations that were run and the number of archives needed for restore. Having a recent copy of the CommCell Configuration and the Media Information reports can help make this process easier.
The basic procedures for restoring any SQL Server database include:
After cataloging the media, you are ready to select the appropriate archive file entries and perform a data restore operation on the files from your media that were secured by data protection operations.
| With regard to Catalog the Media for SQL Server, the SQL Server itself may be installed on the computer running Media Explorer but it need not be installed there. |
Media Explorer on Windows platforms uses a cataloging process to obtain information about the files secured by data protection operations and stored on your media. Media Explorer creates a local catalog database of media archive file entries through which you manually select the archive files containing the data you need to restore. For more information, see the Catalog the Media.
For any procedure, understanding your archive files and backup cycles is critical.
Regularly running the Media Information report and the CommCell Configuration report prior to the need for a disaster recovery will dramatically facilitate your identification and location of media archive files. With this information at hand, you can catalog only the tapes you need and not the entire library. The CommCell Configuration Report shows the association of a subclient to a storage policy. The Storage Policy Report shows media used. Also, during the cataloging process, you could some information in the Media Explorer dialog box to help you identify affected items for the restore, such as chunks, volumes and volume filemarkers.
The following error message will be displayed if the Erase Data license was enabled in the CommCell when the data protection operation to the media was performed:
Password decryption problem! The CommServer this media belongs to may have an Erase Data License.
If you see this error message, you cannot restore the data from this media using Media Explorer.
For more information, see Catalog the Media.
Archive files are containers in which data protection operations store data objects. Examples of data objects are: File System files and folders; Lotus Notes databases and transaction logs; Exchange stores or storage groups. Index data, used for browsing by the product, is also stored in archive files.
A single data protection operation creates one or more archive files to store the iDataAgent data. An archive file will contain data from only one iDataAgent.
| You must recover the entire archive file entry. Media Explorer does not allow recovery of specific objects (i.e., files, folders, databases, type dependent on application). |
The goal of any data recovery is to reconstruct the state of the system as it existed at a given point in time. You must determine how many and which of the archive file types you will require Media Explorer to recover.
A backup cycle is defined as a group of data recovery operations starting with a full and includes all other data recovery operations up to, but not including, the next full.
Each subclient has its own collection of backup cycles. A subclient backup cycle is considered to be all the data required to recover the individual subclient content. Collectively all subclient backup cycles should be considered as the set of data necessary to recover an object (i.e., File System or application Database) to a specific state or point in time. Depending on the type of data being recovered and your immediate needs, it may or may not be necessary to recover all subclient cycles at one time.
Media Explorer provides two basic methods for restoring a SQL Server database: restoring archives either directly to the SQL server (single stream backups) or to disk files (for multiple stream backups).
In most situations, Media Explorer is able to restore data from media directly into a SQL Server database without the need to pre-stage any data, or in other words, without first restoring the backup to disk. This method uses Virtual Device Interface (VDI) and requires no additional disk space above and beyond what is necessary for the SQL Server database itself. This restore option also provides the following capabilities:
When restoring directly to SQL Server the following rules apply:
In some situations it is necessary to restore the archives to disk before loading the database. You may also want to use this method in order to apply an advanced restore command like file renaming or point in time restore.
After restoring archives to disk files, you will use native SQL Server tools, like SQL Server Enterprise Manager or Query Analyzer (ISQLW) to load databases from these files. You are free to use all options available with those tools. This document assumes you have a working knowledge of these tools.
Within the SQL Server Media Explorer section, the following terms apply.
| Database backups | are full and differential data protection operations done for the default subclient of a database. |
| File/File Group backups | are data protection operations done for user-defined subclients. |
| ISQLW | When used, this term implies the user should run the script using the Query Analyzer program provided by the SQL Server software. They must connect to the SQL server instance with a user and password recognized by SQL Server and have permission to restore databases. |
| Load (or "Loading") | implies the process of taking a backup file and writing (or restoring) it into a database. |
| Transaction log backups | are backups of the transaction log done for the default subclient of a database. The Level column of the catalog pane will show these backups as Inc (incremental). |
| TSQL | stands for "Transact Structured Query Language. The native language understood by the SQL server engine. |
| VDI | Both the SQL Server iDataAgent and Media Explorer use the API (application interface) called VDI (Virtual Device Interface) to communicate with the SQL server engine for all backup and restore operations. |
During the cataloging process, if the archive file is a SQL backup the archive header is read and stored into a file called DRSQLcatlog.txt. This file may be used later to determine which archives are necessary to restore in order to bring a database online.
| If you are going to restore the media archives directly to the SQL server, the computer on which you catalog the media must have Microsoft SQL Server installed and functional. |
The DRSQLcatlog.txt file is structured as single lines for which each line represents a SQL Server backup archive. The fields displayed are:
| BackupName | Name given to this particular data protection operation. |
| BackupType |
The type of data protection operation expressed as a number 1 = Database 2 = Transaction Log 4 = File/ File Group 5 = Differential |
| DeviceType | Device used to perform the data protection operation. |
| UserName | User who backed up the data. This is the Windows user account that was used by the SQL Server iDataAgent when performing the data protection operation. |
| ServerName | Name of the SQL server instance. |
| DatabaseName | Name of the database that was backed up. (See Differentiate Identical Database Names in the Catalog File) |
| DatabaseVersion | Version of the database. |
| DatabaseCreation Date | Date the database was created on the SQL server. |
| BackupSize | Size (in bytes) of the data protection operation |
| FirstLsn | First LSN (log sequence number)NULL for File Backups |
| LastLsn | Last LSN (log sequence number)NULL for File Backups |
| CheckpointLsn | Checkpoint LSN (log sequence number) |
| DifferentialBase Lsn | Differential LSN (log sequence number) |
| BackupStartDate | Date and time the data protection operation started |
| BackupFinishDate | Date and time the data protection operation finished |
| SortOrder | Sort order set for the SQL server at the time the data protection operation took place |
| CodePage | Code page the SQL server was using at the time the data protection operation took place |
| UnicodeLocaleId | Unicode locale the SQL server was using at the time the data protection operation took place |
| UnicodeComparisonStyle | Server unicode comparison style |
| CompatibilityLevel | Database compatibility level that existed at the data protection operation time |
| SoftwareVersion Major | SQL server major software version number |
| SoftwareVersion Minor | SQL server minor software version number |
| SoftwareVersion Build | SQL server build software version number (indicates service pack) |
| MachineName | Windows computer name |
| Flags | Indicates if bulk-logged data is captured in this data protection operation |
| Collation | Collation used by the database |
After cataloging your media, the catalog pane displays all archives that existed on the media. If the media contains data protection operations from multiple SQL server instances on the same computer and the database names are identical (such as: master, model, msdb, and pubs) you will need to use the following procedure to select the correct archives.
To differentiate
between archives of the same name