Compressed Backup of SQL Server 6.5, 7.0 and 2000 on Windows 2000

August 20, 2003

by MAK [Muthusamy Anantha Kumar]

DBAs are always running out of hard disk space when handling large databases such as data-warehouse databases and other VLDB. Backing up such databases to disk occupies more disk space, since Microsoft SQL Server does not have compression built into the backup process. SQL Server customers usually go for third party compression utilities such as PKWare, Winzip, SQLZip, etc. In this article, I am going to walk you through using the Windows 2000 archiving feature for compressing database backups.

The Process:


Create a folder D:\Backup.


Right click on the created folder and click on properties


Click on the 'Advanced' command button and check the option to "Compress to save disk space" and "Folder is ready for archiving."

Step 4:

If you have a subfolder under the folder you created, you would be prompted for enabling subfolder to archive. It is always better to create one subfolder before enabling this archive.

Check the second option if your maintenance plan is going to create folders for each database backup.


Change the settings of the DB Backup maintenance plan to point to this folder that we have just enabled for archiving.

Step 6:

Check the size of the backup file ion the folder after backing up a database.


Using this method, there is no need to buy third party software to compress the backup. This article is used only for saving space on the disk. In order to transfer database backups from one place to another, such as FTPing to a remote server, we need to buy third party tools until Microsoft provides that utility along with the SQL Server backup process.