Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

How to take database backup, compress it and upload to SFTP

Apr 12 2012 12:00AM by kavan dhruv (draft)   

BACKUP

declare @BACKUP varchar(200), @SQL varchar(500), @DBName varchar(100),@path varchar(100)
set @DBName = 'TestDB'
set @path = 'E:\TestDB\'
set @BACKUP = @path + @DBName +'_' + convert(varchar,getdate(),112) + '.bak'
set @SQL = 'BACKUP DATABASE ['+@DBName+'] TO DISK = '''+ @BACKUP + ''''
print (@SQL)
exec (@SQL)

ZIP

DECLARE @SERVER VARCHAR(100), @COMMAND VARCHAR(500), @FILE VARCHAR(100)
SET @SERVER = @@SERVERNAME
SET @COMMAND = '"C:\Program Files\WinRAR\RAR" a '+@BACKUP+'.rar'+' '+@BACKUP+''
print (@COMMAND)
EXEC XP_CMDSHELL @COMMAND

COPY

EXEC XP_CMDSHELL 'e:\TestDB\backupmove.bat'

This backupmove.bat file contain,

"C:\Program Files\WinSCP\WinSCP.com" -script=e:\Testdb\backupmove.txt

backupmove.txt contain

option batch on
option confirm off
open sftp://username:password@HOSTName:port
cd foldername
option transfer binary
put E:\Testdb\*.rar
close
exit

You can download WinSCP from http://winscp.net/eng/index.php

Read More..   [32134 clicks]

Published under:  ·  ·  ·  · 

  • Previous: 
  • Next: 

kavan dhruv
350 · 0% · 117
3
 
0
Knew
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

4  Comments  

  • This is an interesting tip. However, a more ideal place for this is in the scripts module. I suggest you post it there. http://beyondrelational.com/modules/30/scripts/485/sql-server-dba-scripts.aspx?tab=default&bs=207

    commented on Apr 12 2012 5:58AM
    Jacob Sebastian
    1 · 100% · 32235
  • @Jacob sir, I have posted in the scripts module also, thanks for suggestion.

    commented on Apr 12 2012 6:10AM
    kavan dhruv
    350 · 0% · 117
  • Why do people insist on using xpcmdshell when every best practices document (including the xpcmdshell topic in Books Online) recommends against its use?

    What is the point of compressing a backup if the backup itself is already compressed?

    What about encryption?

    I would think a more secure solution would be to write a SQLCLR function that handles the compression (if necessary), encryption and transport.

    commented on May 2 2012 10:21AM
    Marc Jellinek
    95 · 2% · 586

Your Comment


Sign Up or Login to post a comment.

"How to take database backup, compress it and upload to SFTP" rated 5 out of 5 by 3 readers
How to take database backup, compress it and upload to SFTP , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]