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 |
|
Milo Cold
Starting Member
14 Posts |
 Posted - 2005-07-06 : 14:04:46
|
| Greetings,I'm in the middle of setting up a backup plan for the Transaction Logs (T.Ls). What I'd like to configure are T.Ls that are backed up to a specific file, like Monday_TLs.bak, appending every hour as long as it's Monday. (Each file would correspond to a specific day of the week)Then when it's Monday again, have the initial backup overwrite Monday_TLs.bak and append each additional backup until 12AM Tuesday, following the cycle when the next Monday hits.Can this even be done, without creating 168 .bak files (one for each hour in a week) that are set to hold one hour of one day's log for up to only one week then overwrite each time that exact time frame occurs?Curious, is their a better way to handle transaction logs? Our current plan seems a bit silly to me, but I understand we're trying to sent something simple up that's easy to manage.hpmf, I'm not sure and far from a DBA...any advice? Thanks!Take it easy,M. Cold |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-07-06 : 15:40:28
|
| What is wrong with having a lot of different transaction log files? We backup the transaction logs for each database to a new file every fifteen minutes. We just set the retention for the files, and delete them as they expire.By the way, I usually recommend backing up the transaction logs every 15 minutes; you can lose a lot of data if you have to recover from an hour old transaction log backup.CODO ERGO SUM |
 |
|
|
Milo Cold
Starting Member
14 Posts |
Posted - 2005-07-06 : 15:49:19
|
quote:
We just set the retention for the files, and delete them as they expire.
What do you mean, are the files automatically removed after a certain time period? Thanks for the heads up on backing up transaction logs every 15 minutes; wouldn't be a bad ideal as long as it doesn't involve manual deletions. Thanks! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-07-06 : 16:18:07
|
There are sevral ways to do this. One is the use database maintenance plans where this feature is a standard option. Another is to use one of the scripts that have been posted on this site that perform a similar functin.quote:
Originally posted by Milo ColdWhat do you mean, are the files automatically removed after a certain time period?
CODO ERGO SUM |
|
|
|
Milo Cold
Starting Member
14 Posts |
Posted - 2005-07-06 : 16:36:43
|
| Thanks again! I'm going to most likely use the database maintenance plans.Take it easy,M. Cold |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-07-07 : 04:42:55
|
| i advice against using the wizard, if that's what you refer by maintenance plans.yes, your requirement can be done, check backup database and log in BOL, there is an option for appending and overwriting sets--------------------keeping it simple... |
|
|
|
Milo Cold
Starting Member
14 Posts |
Posted - 2005-07-07 : 12:31:16
|
quote:
Originally posted by jen
i advice against using the wizard, if that's what you refer by maintenance plans.
Yup, I was going to setup the database maintenance plans via wizards, due to my lack of TSQL knowledge and plus my left arm hurts. May I ask why you advice against it? quote:
...there is an option for appending and overwriting sets
Yes, but I couldn't find a way to overwrite at the start of a new week and append through out the week. Basically, I can't find the setting that dictates how long the backups should be kept for using general the backup wizard. However, I found the option in the database maintenance plan's wizard.Thanks in advance,M. Cold |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-07-08 : 03:50:22
|
i advise against wizard because it is so difficult to troubleshoot, errors are usually generalized and your knowledge of what it does is highly abstractedif you lookup backup database in BOL, you'll see there's the option to overwrite or append (see fonts in blue)for the date of backup, you can start using a naming convention like myDB20050706.bak, this way the '20050706' will give you the date and from there can compute the durationquote:
BACKUP DATABASE { database_name | @database_name_var } TO < backup_device > [ ,...n ] [ WITH [ BLOCKSIZE = { blocksize | @blocksize_variable } ] [ [ , ] DESCRIPTION = { 'text' | @text_variable } ] [ [ , ] DIFFERENTIAL ] [ [ , ] EXPIREDATE = { date | @date_var } | RETAINDAYS = { days | @days_var } ] [ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] FORMAT | NOFORMAT ] [ [ , ] { INIT | NOINIT } ] [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ] [ [ , ] { NOSKIP | SKIP } ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ] ]
--------------------keeping it simple... |
|
|
|
|
|
|
|
|
A More Customized Transaction Log Backup?