Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dasilva_john
Starting Member
2 Posts |
 Posted - 2005-11-02 : 17:03:29
|
| Hello,The size of my MS SQL BAK files are getting ridiculously large. Does anyone know how I could decrease their size? Thanks for any help.John |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-11-02 : 18:00:26
|
| If youre talking about decreasing the size for transport/archive, just compress it using Winzip or WinRar.Nathan Skerl |
 |
|
|
dasilva_john
Starting Member
2 Posts |
Posted - 2005-11-02 : 18:11:59
|
quote:
Originally posted by nathans
If youre talking about decreasing the size for transport/archive, just compress it using Winzip or WinRar.Nathan Skerl
Thanks for your response. The daily SQL backup keeps appending to this file and it is getting larger and larger. None of the backup data is expiring and getting purged from this file. I am not trying to transport it. If I zip the file that will cause the file to be renamed which may cause the backup not to work anymore.John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-02 : 18:43:55
|
| Why are you appending backups?Tara Kizer |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-11-02 : 18:48:06
|
| You are appending everyday when perhaps you should be overwriting? Review WITH INIT option in BOL.Nathan Skerl |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-11-02 : 18:48:27
|
Ahhh... Tara will set you straight. Nathan Skerl |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-02 : 18:53:27
|
| Look at Tara's scripts at http://weblogs.sqlteam.com/tarad. You really shouldn't append backups ever if you can avoid it. If you lose one file, you've lost everything.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-03 : 08:27:21
|
If you can't easily modify the backup routine [as folk have usefully suggested above] how about a BATCH file that renames the Backup File once a day - and then deletes ones that are older than, say, 7 days?DEL MyBackupFile_V7.BAKREN MyBackupFile_V6.BAK MyBackupFile_V7.BAKREN MyBackupFile_V5.BAK MyBackupFile_V6.BAKREN MyBackupFile_V4.BAK MyBackupFile_V5.BAKREN MyBackupFile_V3.BAK MyBackupFile_V4.BAKREN MyBackupFile_V2.BAK MyBackupFile_V3.BAKREN MyBackupFile_V1.BAK MyBackupFile_V2.BAKREN MyBackupFile.BAK MyBackupFile_V1.BAK This would give you the last seven (or maybe eight!) days backup - V7 would be the oldest, V1 the newest, and avoids the need for complicated processes to use today's date & time etc. and figuring out what the date was 7 days ago.However, each time this routine runs the previous V7 file will be deleted, so take care that it only runs once a day!Kristen |
|
|
|
|
|
|
|
|
How to decrease the size of MS SQL BAK files