Zip & FTP database and create backup in sql server

Posted By  dotnetfish On 06 Sep 2010 10:09:17
emailbookmarkadd commentsprint
No of Views:6075
Bookmarked:0 times
Votes:0 times

Introduction

My MSSQL database run a daily backup job at 12:00AM to backup folder (E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\) with name: MyDB_backup_200806010000.bak
where 2008 = YYYY, 06 = MM, 01 = DD and 0000 is fixed

my part is zip MyDB_backup_200806010000.bak to MyDB_backup_200806010000.zip and FTP to folder myapp/db in the remote site.

Software: 7zip, core ftp LE (both are free), both can be downloaded free only, google it online and you can get the file downloaded.

Steps:

1. Install Core Ftp LE in the server (my installation folder: c:"\program files\coreftp\coreftp.exe")


2. Open Core Ftp LE and create my FTP site and enter FTP username and password. I name my site mybacksite.


3. Install 7zip in the server. Copy 7z.exe from the installation folder (mine is in C:\Program Files\7-Zip\7z.exe) to database backup folder (mine is in E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\).


4. Write a windows batch script (batch) to perform the zip and ftp process. I name it appDBbackup.bat and put it in (mine is in E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\)

appDBbackup.bat:

@echo Off

for /f "tokens=1,2" %%u in ('date /t') do set d=%%v

for /f "tokens=1" %%u in ('time /t') do set t=%%u

if "%t:~1,1%"==":" set t=0%t%

rem set timestr=%d:~6,4%%d:~3,2%%d:~0,2%%t:~0,2%%t:~3,2%

set datestr=%d:~6,4%%d:~0,2%%d:~3,2%

set timestr=%t:~0,2%%t:~3,2%

7z a -tzip MyDB_backup_%datestr00.zip MyDB_backup_%datestr00.bak

setlocal

c:"\program files\coreftp\coreftp.exe" -s -O -u "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDB_backup_%datestr00.zip" -site mybacksite -p /myapp/db/

5. Create a windows scheduler to run the job. Point the task to appDBbackup.bat. In my case, i point to E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\appDBbackup.bat and i schedule it at 12:30AM, 30 minutes after the backup started. 

Image Loading

to know more on how to create windows scheduler here. Done

Sign Up to vote for this article
Other popular Tips/Tricks
    In this tips, I will explain how to ALTER or ADD two or more columns in to table by t-sql script.
    Published Date : 25/Mar/2011
    A very important feature of SQL Server 2008 is that we can enable CDC(Change Data capture) on database or table.We can track the database had CDC enabled by querying IS_CDC_ENABLED column
    Published Date : 17/Jan/2011
    A very important feature of SQL Server 2008 is that we can enable CDC(Change Data capture) on database or table.We can track the database had CDC enabled by querying IS_CDC_ENABLED column
    Published Date : 17/Jan/2011
    This is the third tips date related function in sql server. In this I going to show you how to use DATEDIFF() function in sql server. The DATEDIFF () is useful to get the specified date part between two dates
    Published Date : 16/Dec/2010
    The DATEADD function is a powerful built-in function sql server to add dates in different way and types. So I would like to explore in this tip how we can use the DATEADD function for different purpose.
    Published Date : 12/Dec/2010
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</