question

sagari avatar image
sagari asked

Automated uploads of database backup files onto remote server

Hello, I have the database and the database backup files on the cloud server. I need the backups to be moved to an other server(lets call this Server B) every night. I cannot map the drives due to security reasons. The idea I have is to upload the files onto the fileserver and download them onto Server B. Can this process be automated? Does sqlserver have any automatic ftp jobs that it can handle? If there any other way to approach this problem? Thanks
backup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
SQL Server does not have its own FTP capability, but it can invoke any other program with a command line interface through `xp_cmdshell`. However, `xp_cmdshell` is disabled by default and enabling it incereases the exploitation surface area for SQL Server (in other words, it opens up security holes). I would recommend automating this outside SQL Server by writing something like a PowerShell script, Python script, or even old-style bat file and putting that on a schedule with the windows scheduler. Those other options are much more amenable to file manipulations than T-SQL is in the first place and would not require you to enable `xp_cmdshell`.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have never used that one so I won't comment other than saying that the best 3rd party product I have actually tried for making and compressing SQL backups is Red Gate's SQL Backup.
1 Like 1 ·
thanks for ur reply. I came across this product http://sqlbackupandftp.com/. Any views on this?
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
If you cannot use mapped drives I take it the cloud server has an FTP connection option of some sort? If this is true then you should be able to utilize SSIS. It has an FTP task that can be configured with username/password for the connection and then you can point it to where you want it to download/upload to. This is as long as Server B has the same function/ability (FTP connection).
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I like the answer.I discussed this with teammate and he expressed questions of performance degrading on the server on installing SSIS and using it for ftp. I dont have knowledge with respect to SSIS to judge the performance. Does it really effect?
0 Likes 0 ·
You can do a search for "SQL Server SSIS performance" on Bing and a Technet and SQL CAT team articles can explain some of this better. If you are just going to be doing file manipulation the performance concerns should be minimal. I believe most performance issues occur when you get into feathering data.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

If you are transferring over the internet and security is an issue then personally I would opt for a 3rd party product (I use RedGate SQL Backup https://www.red-gate.com/products/dba/sql-backup/) as they will compress, encrypt and transfer files for you all in one process, you simply decide on compression rate, encryption strength, alternative location and schedule. Job Done.

[Dislosure: I am a Friend of RedGate so have a certain bias towards their products. At work however we pay for our licenses and I think they are worth the cost in the assurance they give]

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack Dude, you need to do some proof reading!
0 Likes 0 ·
its the damn paste problems in the UI. Gargh. "Pass me the gun Ellie"
0 Likes 0 ·
Sadhu avatar image
Sadhu answered
In this case it is more handy to use some third-party backup software. [EMS SQL Backup][1] allows you to setup uploading backups to remote servers using FTP, SFTP protocols. The compression feature is also essential in this case. [1]: http://sqlmanager.net/products/mssql/sqlbackup
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Sadhu are you connected with this product? The reason I ask is that this is the 4th answer you have given today so far, with a link to the product, on questions that are very old. This looks like you are simply spamming us with advertising. There is nothing wrong with sharing information about 3rd party products where the context is right, and any connection is disclaimed, but so far you seem to be just posting links.
1 Like 1 ·
The other side of participating in a huge community - oh well. Still better than some of the GO HERE AND BUY CRAP spam we see :)
1 Like 1 ·
@Fatherjack Just wanted to spread the knowledge
0 Likes 0 ·
Which doesn't actually answer @Fatherjack's question...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.