SQL Restore to different instance and file paths (236 Views)
Reply
Frequent Advisor
Peter J West
Posts: 72
Registered: ‎10-24-2002
Message 1 of 4 (236 Views)
Accepted Solution

SQL Restore to different instance and file paths

Hi,

 

We have a SQL system which is backed up on a nightly basis and i'm trying to make a script which will use the omnir command to restore this backup to another SQL host.  The destination host is running a different named instance of SQL and the paths for the files are also different.

 

The current command i've built looks like this:

 

omnir -mssql -barhost sql-source.contoso.com -destination sql-dest.contoso.com -destinstance SQL2008R2 -base Logihold -replace -file LogiholdDotNet "D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Logihold_Data.mdf" -file LogiholdDotNet_log "E:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Logihold_log.ldf" -file ftrow_LogiholdDotNetFT "D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Logihold_ftrow.ndf" -file SerticaDocumentFileStream "D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Logihold_FileStream" -session 2014/03/25-0524

 

Unfortunately running this command results in the following error:

 

[Critical] From: OB2BAR_SQLBAR@sql-dest.contoso.com "SQL2008R2" Time: 2
7-03-2014 10:16:40
There are no objects in the Data Protector Internal Database for object
'Logihold'.

 

I've used the omnidb command to verify the object name and it is correct.  We also test restored to another location with no issues and right now it's looking like the issue is to do with us restoring to a named instance which differs from the original location.  Surely this isn't the case?

 

Can anyone make any suggestions on this one please?

 

Many thanks

 

Pete

Please use plain text.
HP Expert
Bob_Clark
Posts: 1,366
Registered: ‎08-14-2013
Message 2 of 4 (213 Views)

Re: SQL Restore to different instance and file paths

Looking at the SQL Integration Guide for DP 7, at Figure 19, I can see where you are given tyhe option to restore to a different server, and restore to a differnet instance, so, it should be possible to do the same restore from the command line

 

On pg 40, it states these requirements:

 

Restoring to a different SQL Server instance or/and different 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

 

Looking at the CLI documentation in the Integration Guide, one thing that I am seeing that I am not seeing in your command is "-instance SourceInstanceName", although it is in square brackets [   ] which means it is optional

 

The SourceInstanceName is case-sensitive; it has to be the same as the name of the SQL

Server instance that you specified in the backup specification

 

It may be useful here to see the 'detail' output of the backup to be sure that everything si correct

 

    omnidb -session  2014/03/25-0524 -detail > 25-0254_detail.txt

 

I am also starting to wonder if that leading '0' in the session ID '0524' might have something to do with this, because teh 'omnir' command is so syntax-senstive

 

Please use plain text.
Frequent Advisor
Peter J West
Posts: 72
Registered: ‎10-24-2002
Message 3 of 4 (201 Views)

Re: SQL Restore to different instance and file paths

Thanks Bob. You led me down a route which ultimately fixed the issue.

In our case the -SourceInstanceName doesn't appear to be a valid parameter, so i'm guessing maybe we're running an older version. But there is an option of '-Instance' which we'd already tried with no luck.

However, I ran the omnidb command you mention and I noted that the instance name listed there was actually (DEFAULT) and not just DEFAULT as i'd been trying.

Sure enough the same command with '-instance (DEFAULT)' in it causes the restore to work. So it's not optional in this case. I presume it's only optional if you're not also specifying a destination instance.

Case closed.

Thanks for your help :-)
Please use plain text.
HP Expert
Bob_Clark
Posts: 1,366
Registered: ‎08-14-2013
Message 4 of 4 (198 Views)

Re: SQL Restore to different instance and file paths

Great, I am so glad to hear this... I have found the the '-detail' information helps me an awful lot when trying to do things from the command line

Please use plain text.
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation