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 |
|
tocroi72
Yak Posting Veteran
89 Posts |
 Posted - 2005-08-22 : 12:38:58
|
| Hello everyone,I have schedules backup my database (full backup, differential backup and transaction log backup) -There is feature i am confused about when i make a transaction log backup - the "Overwrite" option. One is append to the media and the other one is overwrite existing media. If if choose the append to the media, i was able to restore all the transaction logs , but if i choose the overwrite the existing media, i wasn't able to restore the transaction log. From my little understanding, ofcourse if i overwrite the existing media, the next schedule running backup transaction log will delete the old one and create the new one, therefore - i lost the backup of the old transaction log. But if i do the "Append" option , the backup of the transaction log will be getting really big in a short time.My question is - is there a way to schedule a transaction log backup which only overwrite the existing media if it is the first schedule run right after the Full backup job completed, and after that the transaction log backup should append to the current one?Thanks - I am still a newbie with SQL server. Any supports are really appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-22 : 13:02:30
|
| We always overwrite the file if it exists. You just need to make sure that your filename is unique so that you aren't actually overwriting anything though. How did you create the jobs?Tara |
 |
|
|
mpetanovitch
Yak Posting Veteran
52 Posts |
Posted - 2005-08-22 : 13:54:26
|
| if you use a database maitenance plan to set up your backups and time/date will be appended to each .bak file. However if you are manually backing up the transaction logs and you choose to append you have one .bak file with multiple transaction logs. When restoring you will see that you can choose more then one file to restore. However if you choose to overwrite only one tran log backup will existing in the .bak file. I suggest using the db maitnenance plan, this gives you the option to clean up backfiles that are old and ensures unique file names by timestamping the filename. If you manually do it, you have to create custom jobs to clean up your transaction logs or clear out your .bak file(if you use only one) so it doesn't grow huge because you keep appending.Mike Petanovitch |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tocroi72
Yak Posting Veteran
89 Posts |
Posted - 2005-08-22 : 14:22:25
|
| Thanks everyone for your support, I think Mike understood my question.Tara, hope you don't mind my next question, you said you always overwrite file if exists...for Full for Diff backup, i understand, but for Transaction log backup, if you overwrite it, the next time the backup run, would you lost the old one? - if you do, then how can you restore the transaction log since you lost the previous one? THanksHannah |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-22 : 15:13:56
|
| We don't lose the old one because it's in its own file. Each file is unique. So even though the code says overwrite, it isn't actually doing that as no such file will exist on disk with that same filename.Tara |
|
|
|
tocroi72
Yak Posting Veteran
89 Posts |
Posted - 2005-08-22 : 15:20:19
|
| I see. Thanks Tara. |
|
|
|
tocroi72
Yak Posting Veteran
89 Posts |
Posted - 2005-08-22 : 15:28:26
|
| Oh i forgot - Thanks for the backup stored procedures - It is very helpful. |
|
|
|
|
|
|
|
|
Backup transaction log question