Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
trouserpressface
Starting Member
2 Posts |
 Posted - 2007-11-12 : 10:05:37
|
| I have just started in the scary world of SQL Server admin and am trying to unravel the mysteries of backups etc. If I run 'BACKUP DATABASE xxx TO DISK = 'D:\DB_Backups\xxx.bak' WITH RETAINDAYS = 7' each day, each db backup if appended to the same '.bak' file and the RETAINDAYS protects the backup from being deleted by SQL Server. OK so far. But does anyone understand what criteria is used to decide when to overwrite the older backups? My backup file is getting bigger everyday, with no sign of any of the old data being deleted! Do I have to wait for the entire disk to become full before they start to get overwritten? Or should I just not worry and trust that it will do it all correctly?Any ideas would be much appreciated. |
|
|
anonymous1
Posting Yak Master
185 Posts |
Posted - 2007-11-12 : 12:09:30
|
| you appear to have the correct sql, if the file is growing at a moderate rate it may simply be because the size of the database is growing. the 8th backup is 20% larger than the 1st backup, so even though backup1 is gone the overall file size is not the same. if you begin the restore process using mgmt studio and choose that file as the from device, do you see backup sets with a start date older than 7 days? |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-11-12 : 16:45:25
|
| If you are backing up to a disk, the backups are just appended to the existing file, unless you use the INIT option in your backup command.It is better to create a new backup file every day with a timestamp in the name: MyDatabaseName_db_yyyymmddhhmm.bakExample: master_db_200711050100.bakThis is done automatically by Database Maintenance plans, and there are stored procedures available on this site that will also do this. You should use one of these fro what you want to do.CODO ERGO SUM |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-12 : 22:32:09
|
| And RETAINDAYS works for tape device only. |
|
|
|
trouserpressface
Starting Member
2 Posts |
Posted - 2007-11-13 : 06:07:02
|
| Thanks for the advice. I'd thought about using different files for different days but was hoping that there might be an easier way! I'll have a crack at it and see how it goes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-14 : 16:18:17
|
| "I'd thought about using different files for different days but was hoping that there might be an easier way!"How do you mean "easier" ? |
|
|
|
|
|
|
Backup data retention time?