Let's say it is a late Monday morning, because something always comes up before you are required at your vocation, and by means of unfortunate circumstances, upon arrival you are faced with a clustered server that has provided you with a beautiful stack dump thanks to an e-mail Alert sent from SQL Response (not a pitch, just a real world example of what I rely on).  The Alert indicates to you that virtual memory at the time was, let's say, three times the physical RAM made available to the specific instance in question - ouch (ah, the morning couldn't have started off easier, eh?).  Then you've logged into the cluster and noticed several databases in Suspect Mode, okay, bring it on then, this is going to be a fun one 🙂

Since restoring local backups is preferable and that you've tried to copy them from your network backup location and you're still stuffed (as in the Scotch phrase), then, another solution would be to try and reattach the databases (since the files were there still, but access denied error prevented me from using them, of course) – because for whatever reason, the cluster has flipped your instance onto another node and the databases are not available (!) folder issues, not configured right (arghhh). That didn't work…so now, a direct restore from tape ends up as the only choice.  This is where a your Disaster Recovery Solution should kick in, as setup, in this case, with Hewlett Packard's Data Protector. It's a decent tool that can help out to recover the databases from tape - as long as you are thoroughly familiar with how to use it (as Brent Ozar has explained in this great post, please make sure you run through restore drills...so that the real disaster recovery event itself is not a disaster either).

A prerequisite for work with this tape backup management tool is to ensure that the licensed client tools are installed on the server in question: namely the Disk, Media/Support, User Interface and MS SQL Integration. If you are to use Hewlett Packard's Data Protector tool frequently, which is a pretty straight-forward, create a SQL backup group (chose your in-house nomenclature) in your HP Data Protector Manager client tool (current version installed A.06.10). Within the business day, those who are managing your tape solution should be able to get to you the specific tape in question (if not already loaded) – this is assuming that you do not have the ideal Direct Attached Storage to Tape solution to that specific server, and your HP DP is centralised across your SQL Server Infrastructure (the gateway, or Cell server, as they describe in their techie defs.). Locating the offending server in the backups group, all you have to do is click on the instance, and then click on the database to be restored and there you go (next, finish), problem goes away…unless you have to restore onto a database with a different name - now that's the only real pesky issue (from the point of view of a lazy point-and-click DBA).

What I would consider the worst case scenario with respect to depending on HP Data Protector would be when you must restore a database overtop of another database that holds a different name, and resides on another server entirely. HP Data Protector will give you a replace error from the DP Manager tool if you try this, and there is no easy work-around via this GUI tool (as you may also be used to doing with the SSMS GUI, or by code, here), even when you use the same statement you see in the logs. Thankfully, there is a command line solution – see below for full details, quick example next.  Also, be very careful with respect to using HP data protector on clusters – you must ensure that each node has the software properly installed and configured.

Let's say you are doing a database restore, where you need to restore overtop of another database with the backup from one holding different names, please observe this restore command example:

Omni -mssql -barhost SERVERNAME -base DBNAME my_session_id -asbase NEWDBNAME -file logical_datafilename Drive:\datafilelocation.mdf -file logicalLogfile_name Drive:\logfilelocation.ldf -replace

Here below are more details on how to restore using the Command Line Client Utility of HP data protector, taken directly from page 53 of their own documentation

( C:\Program Files\OmniBack\Docs\IntegrationMS.pdf ):

Restoring using the Data Protector CLI

From the Data_Protector_home\bin directory, run:

omnir -mssql -barhost ClientName [-destination ClientName]

[-instance SourceInstanceName] [-destinstance

DestinationInstanceName] {—base DBName [—session SessionID]

[MSSQL_OPTIONS]... | —base DBName —datafile

GroupName/DataFileName —session SessionID

[DATAFILE_OPTIONS]...} MSSQL_OPTIONS

-asbase NewDBName {-file LogicalFileName1 PhysicalFileName1

[-file LogicalFileName2 PhysicalFileName2]...} -replace -nochain -recovery {rec | norec}

-standby File

DATAFILE_OPTIONS -replace -nochain -recovery {rec | norec}

Provide the Session_ID of the backup session. For object copies, do not use the

copy session ID, but the object backup ID (equals the object backup session ID).

Integration guide for Microsoft applications 53

For description of the CLI options, see the omnir man page or the HP Data Protector

command line interface reference.

Examples

To restore the database RONA running on the SQL Server ALMA to the same

destination, run:

omnir -msssql -barhost ALMA -base RONA

To restore the data file DATAFILE_01 in the file group FILEGROUP_02 of the

database RONA running on the SQL Server ALMA to the same destination, run:

omnir -MSSQL -barhost ALMA -base RONA —datafile

FILEGROUP_02/DATAFILE_01 —session 2008/10/17-3

Restoring to another SQL Server instance or/and another SQL Server

Prerequisites

• Both SQL Servers must have the same local settings (code page and sort order).

This information is displayed in the session monitor for each backup.

• The target SQL Server must be configured and reside in the same Data Protector

cell as the original SQL Server.

Hope this helps in deciding what disaster recovery solution you chose to go with.

I'd like to hear from my readers of which is their favorite if you would be so kind.

P.S. Make sure to have client tools installed on ALL nodes in your cluster, right click on the client and Check Installation to be sure...then run a single small database backup (live drill to tape basically) to verify that it is all okay.