shrink sql backup file?

[gtjr92]

I am trying to find a way to shrink the backup file for our DB. The database file it self is only 607 megs, the transaction log is only 6 megs. The backup file is 11 GIGS. I run a complete backup on the DB. Part of this may be due to me having to delete millions of records every few months and maybe somehow they are staying stored in the Backup file. I tried using dbcc shrinkfile (d:\itabsbackup\itabsfullbackup, 1000)
I kept getting incorrect syntax near d. If I try it this way dbcc shrinkfile ('d:\itabsbackup\itabsfullbackup', 1000) I get
Could not locate file 'd:\itabsbackup\itabsfullbackup' in sysfiles. DBCC execution completed
.How can I accomplish this?

Thanks

 

[DrSql]

Try this.
Try 2 or 3 times. Second time change 10 to 5 then try 5 to 0.

DBCC SHRINKDATABASE (itabs, 10)
GO
DBCC SHRINKDATABASE (itabs, 5)
GO
DBCC SHRINKDATABASE (itabs, 0)
GO

Good luck. Make sure take backup and no users where logged in.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[gtjr92]

This will shrink my backupfile of the database? Not the actual file?

 

[mrdenny]

You can not shrink the backup file. There is no free space in the backup file. Every time you backup the database are you backing up to the same file, or to a different file each time?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[gtjr92]

I am backing up to the same file.
If it is better to backup to different files each time then how can i set up sql to make a new file each time it backs up?
Thanks

 

[Catadmin]

You can use the Database Maintence Planner to run backups. It will add the date and time of backup to the backup file name, which will automatically create a new backup file every time the job runs.

There are a lot of people here (not me) who dislike the DBMP, though, for various reasons. If you want to do this via a T-SQL script instead of the DBMP, you'll need to create several scripts, one for each day of the week or something similar. Then schedule them to run alternately on the day(s) the backup file is for. You'll also want to use the INIT option on the script to overwrite the old data.

The caveat to the second method is that if the backup file gets to be a large size (it only takes one busy day), then you will see that it remains that size "just in case" it needs to fill up that large again. At that point, the only option I know of is to delete and recreate your backup device.

If you use the DBCC ShrinkFile or DBCC ShrinkDatabase in conjunction with a good backup plan, though, your size issues just might go away. Just remember to plan accordingly. Shrinking the Log & Data files on a bad schedule (every day for example) could cause you just as many headaches as the overly large backup files. Find out how much DB growth your database goes through on a weekly or twice weekly basis and plan with that in mind.

I hope I didn't just confuse you. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[SQLBill]

It sounds like your backup job is APPENDING (WITH NO INIT) instead of overwriting (WITH INIT) the backup file. How are you doing the backups? Are you using the WITH INIT option?

-SQLBill

Posting advice: FAQ481-4875

 

[gtjr92]

Well it's been so long since i had set this job up It seems as if i had one job set to backup with init and another set up to backup without. I think i'll just set up a new backup plan!! I guess I was sleeping when I set this job up!

 

[mrdenny]

That's what I though, you've been backing up to the same file. Run this to confirm.

restore headeronly from disk='path\to\backup\file'
go

It will show you all the backups in the file.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)