Data Protector Support and News Forum
Showing results for 
Search instead for 
Do you mean 

SQL Restore to different instance and file paths

SOLVED
Go to Solution
Highlighted
Frequent Advisor

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

1 ACCEPTED SOLUTIONS
HPE Expert

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

 

4 REPLIES
HPE Expert

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

 

Frequent Advisor

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 :-)
HPE Expert

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

Occasional Visitor

Re: SQL Restore to different instance and file paths

Great!! This is exactly what I was trying to figure out doing from CLI.

 

I have a question though, I'm not sure if this is where I should post it, but here it is...

 

How can the (DEFAULT) instance be replaced with the actual database instance name? I'm sure this is to done in MSSQL Server Management Studio but I do not know how.

 

-louie