SBS 2003 and SQL Server Back Up Files

Question

 

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?

Thanks Kevin..

Answer 1

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

 

Madhu

 

 

 

Answer 2

 

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?

Answer 3

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

 

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/aa615add-54e6-40f5-8b55-3728b26884ee.htm

 

backupfilegroup (Transact-SQL)
backupmediafamily (Transact-SQL)
backupmediaset (Transact-SQL)
backupset (Transact-SQL)
System Tables (Transact-SQL)

 

Madhu

Answer 4

I am sorry what is BOL? thank you for your help! Kevin

 

Answer 5

Books Online help for SQL Server. You can download it from

http://www.microsoft.com/downloads/details.aspx?FamilyID=A6F79CB1-A420-445F-8A4B-BD77A7DA194B&displaylang=en

 

Madhu

 

Answer 6

will this work for SQL 2005?

 

Answer 7

This is for 2005 only

 

Answer 8

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.