How to trim .bak file

[jieyin]

I have a database regularly backed up in a .bak file. Over time this file grows larger and larger taking up a lot spaces. I want to trim it down a little bit by getting rid of some earlier versions of backup. Can I do that and how?

Thanks

 

[ClaireHCM]

first execute
restore headeronly from disk = 'path_name or backup_device_name'

Execiuting the above query will give you a table with column name "position"
Then check which position you want to take.And then execute the restore like the following

restore database dbname from disk = 'path_name' with file =
1 or 2 or 3 or 4 (depends which one you choose)

 

[jieyin]

Thanks. But this doesn't solve my problem. My problem is not to retore database from any given backup. What I wanted is to trim the .bak file itself.

For example, the .bak file now contains 26 versions of my backups dated from 7/1/2003 to 7/26/2003. I want to get rid of all backups before 7/15/2003 from the .bak file so that it occupies less space, containing only backups from 7/16/2003 to 7/26/2003.

 

[SonOfEmidec1100]

I had a similar problem and could not solve it directly, as far a could see you can only initialize the device, ie delete all backups.

So what I did was after each back up I rename the .bak file with the date and time as a prefix. When the next backup runs it creates a new initialized bak file. I then added a job to delete individual files when they where no longer required.

 

[jieyin]

Thanks, SonOfEmidec1100. I think your solution works fine for me.

 

[Patrick2002]

Sounds you guys are doing manually what can be done by the built-in maintenance plan wizard. We've been using that for the past year, and it works just fine. Backupfiles are automatically named with date/time in the name, and you can specify how long you want to keep the file, after which it is automatically deleted.Take a look at "database maintenance plans" under "management" in the Enterprise manager. Just don't do optimizations and backup in one single plan, that tends to fail.

 

[SQLBill]

Patrick,

The problem with what you suggest is that when you copy the backup file to tape, the tape backup program needs to know which file to copy. Unless you direct it to copy everything, which isn't effective.

For example, I use SQL Server's BACKUP DATABASE command to backup my database to disk. It creates a .bak file. I then use Backup Exec to copy that file to tape. If the .bak filename keeps changing, BE won't know what to backup. If I have the .bak file go to a specific folder, BE needs to backup EVERY file in that folder to make sure it gets the right one. That's not efficient.

So, it all depends on what the total backup process is. A maintenance plan like you suggest won't work for me and may not work for jieyin. But it's a great suggestion for anyone who doesn't then backup to tape or who can afford to backup every file in a backup folder.

-SQLBill

 

[eja2000]

i seem to get one big .bak file with containing the full,differential and transaction logs..

i want each file seperated and datestamped..
how can i do this?