Automating Database Backups

If you use the full version of SQL Server you can setup automatic database backups via the Enterprise Manager tool.

If you’re using the free version of SQL Server you can use the command line tool
(osql) in a batch file and set it up to run automatically using the Scheduled Tasks application in Windows.

The following batch file as an example:

REM =============================== START
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%
set timestr=%d:~6,4%%d:~3,2%%d:~0,2%%t:~0,2%%t:~3,2%
“C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe” -U sa
-P serverpass -d seedvue -Q “BACKUP DATABASE seedvue TO DISK =
REM =============================== END

It uses the current date and time to create a unique filename.  You’ll need to put in the correct username and password after the -U and -P flags.