Zip & FTP database for backup purposes

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.

Manage/Delete IIS log files using scheduler

Microsoft have provide a very good resource kits. Forfiles is one the them that we can use to manage our log files especially IIS log. My purpose for this article is to show how i use forfiles to help me to manage/delete my IIS log file more than 14 days (you can delete any time frame you want).

Steps:
1. download and install Windows Server 2003 Resource Kit Tools
2. write a bat script
3. create a scheduler


Let look at the details

1. download and install Windows Server 2003 Resource Kit Tools

In order to us it, you need to install it in your server first. Go to microsoft website "Windows Server 2003 Resource Kit Tools" and download it. After finish downloading, you can start to install it.
After finish installation, you can find Forfiles in C:\WINDOWS\system32\forfiles.exe


2. write a bat script

Syntax for forfiles

forfiles [/p Path] [/m SearchMask] [/s] [/c Command] [/d[{+ -}] [{MM/DD/YYYY DD}]]

the details syntax you can get it from technet.

For me, i want to delete the log files date 14 days before. Below are some of the details

1. log file location = C:\WINDOWS\system32\LogFiles\W3SVC1
2. file type = *.log
3. Date to delete = -d -14 (14 days before)

Below is the command that i'm able to come out and save it as .bat format. Say, "managelog.bat"

Forfiles -p C:\WINDOWS\system32\LogFiles\W3SVC1 -s -m *.log -d -14 -c "Cmd /C DEL @File"

copy forfiles.exe from C:\WINDOWS\system32\forfiles.exe and managelog.bat to the same location which you prefer.

3. create a scheduler

You need to create a scheduler to run the job automatically. For me, i run my job weekly at mid night 12:30AM.

To know how to create a window scheduler job, please find reference here.

Please test it before make it run automatically. I suggest that you use below command line to test. I change the DEL to ECHO so that i work safely.

Forfiles -p C:\WINDOWS\system32\LogFiles\W3SVC1 -s -m *.log -d -14 -c "Cmd /C ECHO @File"

Done. Good luck.