Copy BAckup Files to Non Prod servers

Question

Copy BAckup Files to Non Prod servers

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 11, 2016 at 2:46 pm

#338157

Hello Experts,
I need to copy backup files from Prod server to Non Prod server but the problem is both are in two different domains and SQL Service Agent Service accounts do not have permissions to copy the files. Prod bkp files are stored in the storage server which is in Prod domain.
Any suggestion how to copy the Prod bkp files to Non Prod server so that I can automate the Restore DB process?
Thanks.
December 11, 2016 at 9:32 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63434 More actions · Answer 1

If you were going to automate this, wouldn't it require at least a one-way trust between the two domains? SneakerNet isn't really automation...

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 2

pietlinden (12/11/2016)
If you were going to automate this, wouldn't it require at least a one-way trust between the two domains? SneakerNet isn't really automation...

Especially offline SneakerNet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I need to copy backup files from

JasonClark (12/11/2016)
Make database off line.
Detach the production database

????

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

If you don't have a trust relationship between the domains, then you'll need to somehow embed a password for the non prod domain into whatever process does the work. Maybe you could use FTP? Failing that, you're back to SneakerNet. I had never heard that term before!

John

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

GilaMonster (12/12/2016)
I need to copy backup files from
JasonClark (12/11/2016)
Make database off line.
Detach the production database
????

I was just coming to post that. It made my blood run cold.

tazzman SSC Eights! Points: 830 More actions · Answer 6

If you want to automate this process the best way is to configure trust between domains. If you nedd to do this operation once, you can use local account on non prod server to copy files. Just type \\<remoteservername.domain>\<sharename>\ in explorer and then use local credentials (in domainprefix\username format) to authenticate.

Ken McKelvey SSCoach Points: 18769 More actions · Answer 7

I have a similar problem in getting backups off a production server in a timely manner.

(I also have to encrpyt them with 7-Zip as it only runs standard edition.)

Running a powershell job step to use RoboCopy as the last step of the backup agent jobs seems to work.

Something like:

$Share = 'remoteserver\sharename'

$ShareUsr = 'remoteserver\userWithWriteRightsToShare'

$SharePwd = 'UserPwd'

$Source = 'SourcePath'

$net = new-object -ComObject WScript.Network

If (!(Test-Path "B:"))

{

$net.MapNetworkDrive("B:", "$Share", $false, "$ShareUsr", "$SharePwd")

}

robocopy $Source "B:\" *.7z /S /R:0 /W:0 /NFL /NDL /NJH /NJS

$net.RemoveNetworkDrive("B:", $true)

A bit of a hack but there are extenuating circumstances.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Hi Ken

Thanks for sharing the script.

The prod backup goes to a NAS shared location which is in Prod domain. The Non Prod servers are in TEST domain. Both the servers as being in different domains, do not talk to each other.

To use the script, should I create a Proxy and give full permission to Powershell and run the script from NON PROd under a SQL agent job? Or what do you suggest.

Thanks.

Ken McKelvey SSCoach Points: 18769 More actions · Answer 9

SQL-DBA-01 (12/12/2016)
Both the servers as being in different domains, do not talk to each other.

In my case I can login to the remote server using a local user on that server. ie I do not need to bother with a poxy. You will have to play around to see what works for you,

I have just noticed you want to automate a restore process on a DEV box. In this case it might be better to attach to the production box from DEV so the whole process can be run from a job on DEV at a time you know the backups are available.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 10

Only the bar here for me is that, I need to copy the backup from the remote NAS share server which is in PROD domain to the NON PRod server location and the remaining restore part I can handle easily with post restore steps.

Thanks.

kevaburg SSCoach Points: 18131 More actions · Answer 11

I think there are far too many complex solutions to a relatively simple Problem here.

Why not simply create a COPY_ONLY backup on the prod System and ROBOCOPY it to the non-prod location?

dave hants Default port Points: 1485 More actions · Answer 12

We looked at Robocopy and Powershell here and tried various implementations but found them not totally 100% reliable and secure.

In the end we went with a third party product called SyncBackPro and used the Secure FTP feature to do the transfer across domains.

We found that method very reliable and very automatic.

YMMV.

Regards, Dave.

kevaburg SSCoach Points: 18131 More actions · Answer 13

dave hants (12/14/2016)
We looked at Robocopy and Powershell here and tried various implementations but found them not totally 100% reliable and secure.
In the end we went with a third party product called SyncBackPro and used the Secure FTP feature to do the transfer across domains.
We found that method very reliable and very automatic.
YMMV.
Regards, Dave.

What works for one doesn't necessarily work for another I guess.....