Restoration of msql DB instance from serverA to serverB

[veera-lav]

Hi Folks,

i have a problem in msql db that is running in serverA.there was some tables deleted. now we have a backup which was taken from Symantec Netback through backup script *.bch file. My query is i need to restore the dabatase to serverB and which was taken in tape, so could you please any one help how to restore the database and actual procedure to follow and what are the pre-requisited required in serverB. Thanks in advance.

Regards,

Veer

[Marianne]

Alternate Client/Redirected restore is covered in the NetBackup for Microsoft SQL Server Administrator's Guide 

The restore must be initiated from the SQL server where you want to restore the databases to.

Before you do that, you need to authorize the alternate restore on the master server with No.Restrictions file or altnames entries for source and destination clients.

Step-by-step procedure under this topic in above manual:
Redirecting a SQL restore to a different host
 

Don't forget to create bpcd and dbclient log folders on destination client to troubleshoot in case the restore fails.
Also remember to increase Client Read Timeout as per recommendation in the same guide.

[veera-lav]

Ok. i will try and let you know the status ASAP. Thanks for your reply

[veera-lav]

Hi Marianne

i have save a script before restoration.

i renamed the database from "sm711_prd" to "sm711_prd_test", could you please check this and so that i can start the restoration... Thank you

Netbackup server --> INCHEAM-BKUP01

Original Server --> HSC0000133

Destination server --> hpsm-app

could you please brief on " RESTOREOPTION REPLACE"

===

OPERATION RESTORE

OBJECTTYPE DATABASE

DATABASE "sm711_prd_test"

# The following image is type: Full

NBIMAGE "HSC0000133.MSSQL7.HSC0000133.db.sm711_prd.~.7.001of001.20140214060603..C"

SQLHOST "hpsm-app"

NBSERVER "INCHEAM-BKUP01"

BROWSECLIENT "HSC0000133"

MAXTRANSFERSIZE 6

BLOCKSIZE 7

RESTOREOPTION REPLACE

RECOVEREDSTATE NOTRECOVERED

NUMBUFS 2

ENDOPER TRUE

====

[veera-lav]

I just tried with above commads and got an error, i have attached error file.

here i have to restore in another server with d:\ drive location and database name also need to change. so whether i need to modify the script or need to create another method to restore. Thanks..

[Will_Restore]

Please see Redirecting a database to a different location on a different host section in the Symantec NetBackup™ for Microsoft SQL Server Administrator's Guide to learn how to create a move template.

2/20/2014 1:17:38 AM - Info dbclient(pid=1828) DBMS MSG - ODBC return code <-1>, SQL State <37000>, SQL Message <5133><[Microsoft][SQL Native Client][SQL Server]Directory lookup for the file "E:\SQLServer\MSSQL.1\MSSQL\DATA\sm711_prd.mdf" failed with the operating system error 21(The device is not ready.).>.
2/20/2014 1:17:39 AM - Info dbclient(pid=1828) DBMS MSG - SQL Message <3156><[Microsoft][SQL Native Client][SQL Server]File 'sm711_prd' cannot be restored to 'E:\SQLServer\MSSQL.1\MSSQL\DATA\sm711_prd.mdf'. Use WITH MOVE to identify a valid location for the file.>

[Marianne · Accepted solution]

Apologies for only responding now - we seem to live in different parts of the world...

wr is correct - you need to use Move Template, and then edit the restore script to specify new name and new location.

The manual explains this in the sction that I pointed out yesterday under this topic:

Redirecting a SQL database to a different location on a different host
 

The database move redirects the restore of a SQL Server database to a different

location. The new location can be a different instance on the same host, a different

host, or a different file path. The move operation also lets you restore the database

under a different name than the original one.

You will find examples of how to modify the script.
The move template that is created when you select this option in the GUI also contains comments that will guide you.

Good luck!

[veera-lav]

This is resolved by below script executed.

Script to move database to another server in another location

database name changed from sm711_prd to sm711_prd_restore

=====

OPERATION RESTORE

OBJECTTYPE DATABASE

DATABASE "sm711_prd_restore"  --> changed the database name

MOVE  "sm711_prd"                    

TO  "D:\sm711_prd_restore\sm711_prd_restore.mdf"      --> changed the .mdf name

MOVE  "sm711_prd_log"

TO  "D:\sm711_prd_restore\sm711_prd_log_restore.ldf"  --> changed the .ldf name

NBIMAGE "HSC0000133.MSSQL7.HSC0000133.db.sm711_prd.~.7.001of001.20140214060603..C"

SQLHOST "hpsm-app"                                                --> destination host

NBSERVER "INCHEAM-BKUP01.AD.CSSCORP.COM" --> Netbackup server

ALTCLIENT "hsc0000133"                                            --> source host

MAXTRANSFERSIZE 6

BLOCKSIZE 7

RESTOREOPTION REPLACE

RECOVEREDSTATE RECOVERED                             --> database auto recover after restore

NUMBUFS 2

ENDOPER TRUE

=====

Thanks Folks for your support... :)

[Marianne]

Great stuff!

If you need to do this regularly, you may want to bookmark this TN: 
http://www.symantec.com/docs/TECH74475