My situation:
i need to backup, zip and FTP my database to another server for safety purposes.
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.
to know more on how to create windows scheduler. read this
Done.
1 comment:
Thanks for brain!
Post a Comment