SQL Backups and Compression / Delete a Database

Question

SQL Backups and Compression / Delete a Database

HookSQLDBA7

SSChampion

Points: 10709
More actions
January 15, 2008 at 2:32 pm

#121777

When a SQL Server 2000 Database on Windows 2003 is backed up and it creates .bak and .trn files, are these files compressed? Is the free space or fragmented space removed during the backup process? Is it a 'bad thing' to try to zip these files up to make them even smaller? Is this possible?
When you delete a Database via Enterprise Manager does it removes the operating system files (.mdf and .ldf)?
Thanks in advance, Kevin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

By default, the backup files are not compressed unless the backups are in a compressed folder.

Free space should be removed, but I am not sure about the fragmentation. You can see that the sizes of backup files are normally smaller than original databases.

zipping backup files is one of alternatives to solve the disk space temporarily. It can make the size of backup files smaller. I would not say it is a "bad" thing. But you may use LiteSpeed to compress your backups directly.

When we delete a database from Enterprise Manager, we delete mdf and ldf files, but not backup files.

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 2

What option or sql command can be used to remove fragmentation?

Thanks, Kevin

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

If you are talking about index fragmentation, you can use

DBCC REINDEX

DBCC INDEXDEFRAG

If you you are talking about the disk fragmentation, you need to do it on OS level using defrag, diskeeper, ...

Tong-373768 Grasshopper Points: 20 More actions · Answer 4

How are we going to backup the .bak files in a compressed folder for SQL 2000

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Just to add:

SQL Server 2008 includes backup file compression and that too to a good extent.

Manu

Tong-373768 Grasshopper Points: 20 More actions · Answer 6

MANU (7/15/2008)
Just to add:
SQL Server 2008 includes backup file compression and that too to a good extent.
Manu

Yeah, we know SQL 2008 have the file compression option. However, we are still using SQL2000, is there any ways to backup it straight to a compressed folder?

Thanks.

Sean M Valued Member Points: 53 More actions · Answer 7

A folder can be set with the compression attribute, so any SQL backup files are compressed as they are created. I've done this when disk space has been tight and getting more disk space isn't an easy option (SQL 2000).

A few notes on this:

1. If you set a folder to be compressed, and it isn't empty, Windows will ask if you want to apply changes to the folder only, or apply changes to the folder, subfolders, and files. I'd suggest renaming the SQL backup folder, create a new one, then set the compression attribute, so only future backups are compressed. If you compress existing files, it could take a lot of time depending on how much data is there.

2. For some reason Windows Explorer needs to think about things when compressed files are deleted (it pauses for several seconds).

3. Configure Windows Explorer to show compressed files in color.

4. The command "compact" will show you what sort of compression you are getting.

-Sean