Restoring a backup file on a remote server

Question

Restoring a backup file on a remote server

himanshu.sinha

Right there with Babe

Points: 739
More actions
February 13, 2016 at 6:43 pm

#316028

Working on a script to restore a backup file which is on server A & restore it on server B .
These are the steps which needs to followed .
1 .FInd the latest backup from the folder on server A .
2.Copy the file to the remote server B ( cmd shell is disabled and cannot be enabled )
3.Restore the on the remote server .
Well since as per the #2 we cannot use the cmd shell I think using powershell will be best option , which can be invoked using a SQL job or a scheduled task.
Any there suggestions / work around possible ?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

himanshu.sinha (2/13/2016)
Working on a script to restore a backup file which is on server A & restore it on server B .
These are the steps which needs to followed .
1 .FInd the latest backup from the folder on server A .
2.Copy the file to the remote server B ( cmd shell is disabled and cannot be enabled )
3.Restore the on the remote server .
Well since as per the #2 we cannot use the cmd shell I think using powershell will be best option , which can be invoked using a SQL job or a scheduled task.
Any there suggestions / work around possible ?

Re: 2, why do you need to copy it to server 2? Can you just run the restore command on server2 and have it refer to the backup file on server1 via a UNC path, e.g. \\server1\d$\sql\backups\whatever_20160213_121212.bak ?

You would still need a way to invoke the restore command on server2. A Linked Server would do it if that is an option.

I am throwing these ideas out there on how to accomplish this in T-SQL for completeness but honestly this is exactly where PowerShell shines. If you get good with SMO in PowerShell you'll ask yourself why you ever toiled away messing with clunky tools like xp_cmdshell and Linked Servers. Be wary of the PowerShell step type in SQL Agent though. I prefer to have a my PowerShell scripts saved to .ps1 files (ideally on a shared NAS location where all my servers can use them) and then execute using powershell.exe in an Agent step of type Operating System (CmdExec).

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

himanshu.sinha Right there with Babe Points: 739 More actions · Answer 2

Thanks for the reply .

Well since the backup size is 200GB , I am not very comfortable using the UNC path but i will give it a try though .Linked server is also not an option to be used in production here .

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

himanshu.sinha (2/13/2016)
Thanks for the reply .
Well since the backup size is 200GB , I am not very comfortable using the UNC path but i will give it a try though .Linked server is also not an option to be used in production here .

Sounds like the people setting the standards in your environment and I are on the same page.

If it's a fast network do not worry about the size of the backup you'll be restoring from a remote location. Think of it this way, whether you copy the file first then restore it from a local location, or restore it over the network, the data has to move from one place to the other. SQL Server is more than capable of managing the data movement over the wire just as well as Windows can.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

himanshu.sinha (2/13/2016)
Thanks for the reply .
Well since the backup size is 200GB , I am not very comfortable using the UNC path but i will give it a try though .Linked server is also not an option to be used in production here .

If you need to do this more than once, is there any chance of SAN replication there? It would become almost instantaneous if there was.

--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)

himanshu.sinha Right there with Babe Points: 739 More actions · Answer 5

Thanks for the reply , I need to do this everyday once off peak hours and we are not on SAN .

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Orlando Colamatteo (2/13/2016)
Be wary of the PowerShell step type in SQL Agent though. I prefer to have a my PowerShell scripts saved to .ps1 files (ideally on a shared NAS location where all my servers can use them) and then execute using powershell.exe in an Agent step of type Operating System (CmdExec).

+1 here, the SQL agent job step provider is very quirky

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Perry Whittle (2/15/2016)
Orlando Colamatteo (2/13/2016)
Be wary of the PowerShell step type in SQL Agent though. I prefer to have a my PowerShell scripts saved to .ps1 files (ideally on a shared NAS location where all my servers can use them) and then execute using powershell.exe in an Agent step of type Operating System (CmdExec).
+1 here, the SQL agent job step provider is very quirky

I remember you being a big VB Script guy. Sounds like you might be using more PowerShell. Are you dabbling or did you do a full trade?

EDIT: I guess that answers that...

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

himanshu.sinha (2/13/2016)
1 .FInd the latest backup from the folder on server A .

easy via Powershell, something similar to this

$checkpath = "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\*.*"

$result = get-childitem $checkpath -Include *.bak | sort-object -property @{expression={$_.LastWriteTime};Ascending=$false} | select -first 1 | select FullName

Write-Output "latest backup filename is: $($result.FullName)"

himanshu.sinha (2/13/2016)
2.Copy the file to the remote server B ( cmd shell is disabled and cannot be enabled )

Again, easy via Powershell

copy-item -Path $result.FullName -destination destpathandfilename -Force

himanshu.sinha (2/13/2016)
3.Restore the on the remote server .

Sure you can work this bit out yourself

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

himanshu.sinha Right there with Babe Points: 739 More actions · Answer 9

Thanks for the reply , well i have implemented the logic and its working now , will be posting the same in Github but the way you used pipe to get the latest file in the folder , i liked that 🙂

for some reason -Include was not working but -Filter did worked fine . Thanks once again .

Get-childitem $_path [highlight="#ffff11"]-Include [/highlight]*.bak| sort-object -property

Get-childitem $_path [highlight="#ffff11"]-Filter[/highlight] *.bak| sort-object -property