question

craja26 avatar image
craja26 asked

SQL Server 2000 backup file compression issue

Hi All, I have SQL Server 2000 backup file which has 3GB size. I tried to compress using a batch file like below. set FILETOZIP=E:\SourceDB.bak set TEMPDIR=E:\BatchTemp rmdir %TEMPDIR% mkdir %TEMPDIR% copy %FILETOZIP% %TEMPDIR% echo Set objArgs = WScript.Arguments > _zipIt.vbs echo InputFolder = objArgs(0) >> _zipIt.vbs echo ZipFile = objArgs(1) >> _zipIt.vbs echo CreateObject("Scripting.FileSystemObject").CreateTextFile(ZipFile, True).Write "PK" ^& Chr(5) ^& Chr(6) ^& String(18, vbNullChar) >> _zipIt.vbs echo Set objShell = CreateObject("Shell.Application") >> _zipIt.vbs echo Set source = objShell.NameSpace(InputFolder).Items >> _zipIt.vbs echo objShell.NameSpace(ZipFile).CopyHere(source) >> _zipIt.vbs echo wScript.Sleep 2000 >> _zipIt.vbs CScript _zipIt.vbs %TEMPDIR% E:\BatchResult\someArchive.zip pause If .bak file is small, it's working fine but if source file is large(2.5GB) it showing "Cannot compress large file...." Could you please let me know if you have any script or let me know any alternative way.. Note: I am new to writing batch scripts and I don't have a permission to use 3rd party tools. Thanks, Raja.
backup-compression
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Venkataraman avatar image
Venkataraman answered
Refer to this article for compressing SQL Server 2000 backup: http://www.bahramov.com/2009/03/19/creating-a-compressed-sql-server-database-backup/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
You can consider third party tools for doing SQL Server 2000 compressed backups such as [Red Gate SQL Backup][1]. [1]: http://www.red-gate.com/products/dba/sql-backup/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
One way to mitigate the size limitation is to have your backups created over more than one .BAK file. If you're at 2 GB say, then 4 would only be 500 MB each, certainly within your system's capabilities to compress, and placate it until your backup files total in excess of 8 GB. If you feel you may hit that size soon start with 5, 6 or 10. It adds complexity as you need to set up the backups to produce multiple files and you have to tweak your script's logic to handle processing more than one file but it does work around your current problem.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.