DB Backup too big- running out of disk space

[mattisontara]

Bear with me- I am not experienced in SQL server backup issues at all. The database backup is now at 26.6 GIGs and the disk it is on is a 50 gig disk.
I suspect that it is dangerous and not at all what we want to do to delete the backup from the day before BEFORE we backup again, but of course if we do not do that, the backup fails because there isn't enough space.
I know that there are commands to shrink the database or shrink the file... What I want to know is, what course of action would an experienced dba take in this situation?

 

[karluk]

We recently ran into the same issue. We are in the process of installing our first really large SQL Server database, and finding space for the backups is quite a challenge.

In the short term we are setting up a share to a network drive and copying the backups there, then deleting the old backup on the server before running a new backup.

In the longer term we are looking at purchasing Veritas Backup Exec software, which we are already using for Oracle backups. The advantage of using a third party tool like Backup Exec is that it allows you to write backups directly to tape, rather than disk. At least that's how it works for Oracle.

Of course the brute force approach is simply to buy a bunch of extra hard drives. They are so cheap now that storing a 26.6 Gb backup on disk doesn't sound too extravagant.

 

[mattisontara]

We have veritas. I will request that the backup be set to be copied to tape via veritas after the backup runs. Thanks!I didn't even think of that.

Other SQL Server DBAs- I would still like to hear your ideas. I want several plans of action

 

[karluk]

Let me be more descriptive of what we are currently doing. We've always used Backup Exec to do nightly file system backups. That means that the SQL Server backups should always have a tape backup once they've been on disk for 24 hours. After that they are candidates for deletion. The key is to be able to store them for 24 hours, which isn't always possible with our current shortage of disk space.

Thus our long term plans involve very specifically purchasing a product called "Veritas Backup Exec agent for MS SQL Server". If it works the same way as Backup Exec agent for Oracle, the actual backups are written directly to tape, and never are stored on disk at all. We've needed this direct write to tape for our Oracle databases because historically our Oracle databases have been much larger than the SQL Server dbs. Now that we are getting a large SQL Server application the same space constraints are forcing us to look at direct tape backups for SQL Server.

 

[SQLSister]

How big is your database? if it isn't anywhere close to this size perhaps you just have forgotten to occasionallly overwrite the file instead of adding on to it. Usually I like to overwrite the file when I do my full backup and add to the file when I do transactional or differntial backups. Of course I also like to copy the backup file to another location or have the network admin backup the .bak file to tape before I run the backup that overwrites it. That way the old one is recoverable if something happens during the overwrite.

 

[mattisontara]

It is the same size.