SQL Server 2005 - Schedule Backup

[izzop]

Hi,

Just wondered if anyone could advise me on how to create a scheduled backup for a database?

I know how to carry out a back up using Microsoft SQL Server Management Studio, but wondered how I can schedule one to run using Windows Task Scheduler?

Thanks & Regards

 

[Pho]

This might not be what you want but we use ExpressMaint (as it also works with SQL Server Express) to do a full backup via a scheduled task and then have 7-zip compress the .bak files ready for sending to a backup FTP.

Here's the batch I made, it's probably not very pretty but it works well. It takes the name of the database as the first parameter, i.e. to backup database DB1 make your scheduled task run:
backup.bat DB1

@echo off
echo ===================
echo %1
echo ===================
echo.

echo Backing up database..
expressmaint -S .\SERVER -D %1 -T DB -R "C:\Backup\Tools\Database\Backup\MSSQL\logs" -RU WEEKS -RV 1 -B "C:\Program Files\Microsoft SQL Server\MSSQL10.SERVER\MSSQL\Backup" -BU WEEKS -BV 4

CD C:\Program Files\Microsoft SQL Server\MSSQL10.Backup\MSSQL\Backup\%1

echo Files to compress:
dir /b /O:D *.bak 2>NUL
echo.

echo Compressing Files..
FOR /f "tokens=*" %%G IN ('dir /b /O:D *.bak 2^>NUL') DO 7z a -mx9 %%G.7z %%G

echo Moving Files..
move *.7z compressed 2>NUL
move *.bak uncompressed 2>NUL

cd C:\Backup\Tools\Database\Backup\MSSQL