May 24, 2013 at 8:24 am
Hi,
I am looking for solution for below approach using TSQL or powershell or using SQL agent job.
1. Perform backup on prod DB server of one database-- this can be done
2. FTP/SFTP the backup file to the staging DB server - i am clearly looking for this line and do not want to use SSIS, this i have to perform using SFTP client/server process.
3. Restore the backup to a database
Can any one please help how to start working on this process and to automate this process at the end.
May 24, 2013 at 8:27 am
Why not implement replication if you very well have network connectivity between both servers, and set the replication end of the day?
May 24, 2013 at 8:38 am
You can FTP from within SQL Server, but I've never gotten SFTP to work properly. That's not saying it can't be done, but I don't want to install an SFTP client on the production server. FTP ships with Windows, so no install is required.
1. Write a script.ftp file in a text editor that will connect to your FTP server and then call it from DOS. Edit until it works as you want it to.
2. Now write the SQL necessary to build that file from scratch.
3. In your procedure, use xp_cmdshell to run the DOS command to invoke the script.ftp file to do the file transfer.
The big caveat here is that you have to allow the account running the job to use xp_cmdshell. It has to be enabled, executed to run your batch file, then it should be disabled. This presents some security risks, but it may be necessary.
All in all, if you can get replication to do what you want, it might be a better solution.
May 24, 2013 at 8:51 am
The two servers are not in network they are on AWS cloud( two diff Virtual servers with no dns names)
No file sharing port is open between the servers.
Replication cannot be achieved as having all these constraints in place and firewall ports blocked.(1433 is also blocked between the two).
May 24, 2013 at 9:02 am
In that case, go with the FTP approach.
May 24, 2013 at 9:41 am
Hi Ed,
I want to know the syntax of SFTP command that will execute by sql server job and what steps will I have to follow to do public key authentication ? I want to implement SFTP to transfer files from window server 2008 to another window server 2008.
May 24, 2013 at 11:06 am
I don't know about using SFTP, but I have implemented FTP. I'd imagine they would be similar, but you will absolutely need SFTP client software that supports command line parameters installed on the server to perform the actual transfer. I know WinSCP supports command line arguments, but I've only automated it from DOS; never from SQL.
Here's an approach using FTP:
First create an FTP file called script.ftp. In it, have your FTP commands you want to execute to put your file to the remote server. Then save it (using C:\temp\script.ftp below as an example) on the SQL Server. I'd prefer to have the procedure build the file and delete it when done, but this is a good place to start. Here's the command to execute the script.ftp script against the server http://ftp.domain.com, which should be replaced by your server name.
SET @strCmd = 'ftp.exe -v -s:c:\Temp\script.ftp http://ftp.domain.com';
EXECUTE master..xp_cmdshell @strCmd
If you get an error because you can't run xp_cmdshell here, it is because it isn't enabled. You'll need to enable it, but check with your senior DBA before doing this. Please don't disregard this important step. There are security implications of allowing command shell from SQL that you'll need to be aware of.
Once you get it working as a proof of concept, you can write into the procedure the commands to generate the script.ftp file dynamically, then execute it, then delete it when you're done. This way, the connection information will only exist in the procedure itself and not in a file in the file system somewhere.
The next step will be to take what you've learned and apply it to scripting SFTP client software instead. I don't imagine it would be too terribly different, but just different enough to being a bit of pain. I've never done this from SQL, so please share what you learn.
May 24, 2013 at 11:23 am
I have put together an sftp job using putty. The nice thing about putty is that while you certainly can install it, you can also just download the zip package and put it somewhere and run it. What I did was get a batch file that could take a filename as a parameter and send it, this saves a bit in time trying to debug your connection through xp_cmdshell.
With that sftp job, we used a password.
A small complication is that putty wants to preserve the destination host signature in the registry under the key associated with your login. Unfortunately the credentials xp_cmdshell runs under is probably not going to be the login you use interactively LOL. What I did was get the sftp job working first through an interactive cmdprompt so I could answer "Y" to saving the destination signature, then get the sftp job running through a batch job for debugging purposes and then duplicating the registry entry for the hostkey under the registry hive for the credentials running the xp_cmdshell.
Thats a birds eye view, and I did not have to set up the destination server and did not use public key encryption. I did a similar job with putty using scp, with the same issues with the destination key signature, and in this case we did put together the destination (bsd), and since I had access to that server, we did use keys instead of passwords the usual way with ssh under *ix which you can google for that. Not sure about an sftp server under windows!
So whats the sftp server look like on windows?
May 24, 2013 at 11:27 am
That's really good - thank you.
The Windows SFTP server depends on whatever software you buy and install. We have one here, but I don't administer it. It's pretty old and (according to the domain administrators) not very friendly, but it gets the job done. I'm just glad I don't have to administer that, too.
Thanks very much for your approach. I'm going play with this when I get the chance.
May 24, 2013 at 12:03 pm
Ed Wagner (5/24/2013)
That's really good - thank you.The Windows SFTP server depends on whatever software you buy and install. We have one here, but I don't administer it. It's pretty old and (according to the domain administrators) not very friendly, but it gets the job done. I'm just glad I don't have to administer that, too.
Thanks very much for your approach. I'm going play with this when I get the chance.
Post on your experience getting the end to end to work if you can!
http://www.freesshd.com looks pretty good, while not exactly open source (one of the modules is closed I guess), I think I'm going to give it a shot for fun but as lazy as I am you'll probably beat me to it! Also one correction, I doublechecked and my registry keys are now going in
[HKEY_USERS\.DEFAULT\Software\SimonTatham\PuTTy\SshHostKeys] and I just pretty much mouse the keys in to look the same as one created by running an interactive session under a regular user account, they'll be in a similar location except under "CURRENT_USER" or that goofy looking sid thing, but you'll probably get the gist of what I'm saying once you poke around!
You can even export the key to a registry text file and edit it to reimport (I did it that way the first few times around), but mousing in regedit works fine!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply