I've been working on a way to Backup SQL Server 2000/2005/2008 databases to a compressed file recently, but without using temporary files. I recently found that it is actually pretty easy.

msbp.exe Backup[model] gzip file:///c:\model.bak.gz

• msbp.exe restore file:///c:\model.bak.gz gzip [model]
  Here gzip knows to uncompress since it is in "restore" mode.
• msbp.exe Backup[model] bzip2(level=5) file:///c:\model.bak.bz2
  Each plugin in the pipeline can have parameters.
• msbp.exe Backup[model] rate(ratemb=5.0) bzip2(level=5) file:///c:\model.bak.bz2
  You can have any number of plugins in the pipeline. The rate plugin limits the impact of a Backup on your server by restricting the speed of theBackup(or restore).

Exemple of a MSSQL 2000 backup

Install the MSBP (download HERE) on the SQL server

Create a BAT file with the following data (let say c:\mssql-backup\msbackup.bat)

- Forfiles -p C:\SQLBackup-Compressed -s -m *.* -d -1 -c "cmd /c del /q @path"

Delete files older then 1 day (-d -1)

- mkdir C:\SQLBackup-Compressed\Master

Create a directory where the database will Backup this will make sure the dir is there

- msbp.exe Backup"db(database=Master)" "gzip(level=5)" "local(path=C:\SQLBackup-Compressed\Master\master.bak.gz)"

Make a Backup of the Master database

-rename C:\SQLBackup-Compressed\Master\*.BAK.GZ Master_%date:~-4,4%%date:~-10,2%%date:~-7,2%%time:~-8,2%%time:~-5,2%.BAK.GZ

Put a datestamp on the Backupfile : YYYY:DD:MM:MIN:SEC

Now you can create a scheduled task (if needed).

When you have many databases you can try the following if you don't want type till you drop :

Create a BAT file with the following data (let say c:\mssql-backup\msbackup.bat)

Then create in C:\MSSQL-backup a Databases.txt file, there you can place your databases that will be done for a backup.

In SQL you can run the query "sp_databases" so you can do a cute and paste of all the database names, don't forget to exclude the TEMPDB !!!!