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:
Step1:
Create a folder D:\Backup.
Step2:
Right click on the created folder
and click on properties
Step3:
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.
Step5:
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.
Conclusion:
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.