Tuesday, November 13, 2007 3:47 AM
I am trying to figure out how the back up is performed on my sql as I seem to have quite a few very large BAK files that I want to know if I can delete them.
These files have grown to about 85Gs
The IT group told me that the were going to set up the SQL to back up as a file and then SBS 2003 would back up this file onto the USB drive. The IT people that set this up have really sold me a lot of BS about so much that I have decided to get control of all aspect of the SBS2003.
How to I verify that the SQL is being backed up and can I delete these really big old dated BAK files?
Tuesday, November 13, 2007 5:20 AMModerator
What is the backup policy in your organization. From Disaster recovery point of view, you just need to have latest FullBackup and latest Differential backup (if you have) and all the TL backup after the Full backup or Diff backup to get the database to current state.
You may need to keep Full backup for sometime. Consider, you have a requirement like “check what was the data as on some date” then you may need to keep full backups. So you may need to keep full backup of each month or each week depends upon your requirement.
Also check the backup script. Whether you are appending each backup in the backup set or you are overwriting. If you are using WITH INIT you are overwriting other wise appending
Thursday, November 15, 2007 2:34 AM
Basically I should be ok with deleting any of the BAK files? What is the differnce between a BAK file and a TRN file?
How do I ensure that the SQL is backing up each night has I can not find any BAK files dated later than 10/25/07?
Thursday, November 15, 2007 3:21 AMModerator
TRN file may be the backup of transaction log file. It may need when you restore database using STOPAT option. Read more about Backup /Restore Archietecture in BOL
How do I ensure that the SQL is backing up each night
Either you can check the Job history or Check the tables in MSDB database.
Read about Backup and Restore Tables in BOL
Thursday, November 15, 2007 3:24 AMI am sorry what is BOL? thank you for your help! Kevin
Thursday, November 15, 2007 3:33 AMModerator
Books Online help for SQL Server. You can download it from
Thursday, November 15, 2007 3:36 AMwill this work for SQL 2005?
Thursday, November 15, 2007 3:44 AMModeratorThis is for 2005 only
Saturday, November 17, 2007 12:45 AMModerator
It sounds like what is happening is that they set up a job to append each day's backups to the existing backup file.
With this setup, the backup files will grow indefinitely.
you can use the command
RESTORE HEADERONLY FROM FILE = 'YourBackupFile.BAK'
This should list all of the backups contained in the backup file, and tell you more about what you've got.
Another thing to check is the modification time on the backup file. If the creation time is a month ago and the modification time is last night, then you're probably appending.
By convention, .BAK files are database backups, and .TRN files are transaction log backups.