Backup transaction logs

Currently my backup plan for my databases has them going to file backup ever
y
day and then to tape. My DB backup is set with the option of "with init" to
allow for backup to be overwritten.
For my transaction logs they get backed up to disk every hour and then once
a day the backup file gets backed up to disk. The problem is on these they
are set to use "with noinit" so these backup files are growing.
If I switch the init option my transaction log backups will get overwritten
every hour.
Would this work if I had to do a a full restore? I would have the full DB
backup from the night before and the trans backup from the latest hour. But
I
would not have the backup of the other transactions (hopefully that makes
sense) between that latest hour and the when the full DB backup was done.
The problem I have now is that with my trans bakcups being appended is that
there is backups of transactions that are older that the current day. I
don't need to keep these as a do a full DB backup every day...
Thanks.Two options come to mind:
You could modify your T-Log backup job to issue a WITH INIT before you issue
your nightly database backup.
You could modify your hourly T-Log backups to backup WITH INIT. You will
also need to add a job step which renames the files and includes a datetime
identifier in the file name. Something like this:
DECLARE @.str varchar(1000),
@.dte varchar(100)
SELECT @.dte = CONVERT(varchar(8),getdate(),112) + '.' +
CONVERT(varchar(2),datepart(hh,getdate()
))
SELECT @.str = 'master..xp_cmdshell ''rename x:\testfoo.txt testfoo.' +@.dte +
'.txt'''
SELECT @.str /*if you want to see the command that will be executed*/
EXEC (@.str) /*performs the file rename*/
Keith
"Tyler" <Tyler@.discussions.microsoft.com> wrote in message
news:CB42C0B9-D402-4275-ACC0-9FAE0384A7EC@.microsoft.com...
> Currently my backup plan for my databases has them going to file backup
every
> day and then to tape. My DB backup is set with the option of "with init"
to
> allow for backup to be overwritten.
> For my transaction logs they get backed up to disk every hour and then
once
> a day the backup file gets backed up to disk. The problem is on these
they
> are set to use "with noinit" so these backup files are growing.
> If I switch the init option my transaction log backups will get
overwritten
> every hour.
> Would this work if I had to do a a full restore? I would have the full DB
> backup from the night before and the trans backup from the latest hour.
But I
> would not have the backup of the other transactions (hopefully that makes
> sense) between that latest hour and the when the full DB backup was done.
> The problem I have now is that with my trans bakcups being appended is
that
> there is backups of transactions that are older that the current day. I
> don't need to keep these as a do a full DB backup every day...
> Thanks.
>|||I had a similar problem. I have created 8 backup devices (for each hour
between 9 AM and 5 pm) with a name convention (transDump9, transDump10,
transDump11, transDump12, transDump13...transDump17). I also have a job to
dump the transaction log WITH INIT every hour...
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Keith Kratochvil" wrote:

> Two options come to mind:
> You could modify your T-Log backup job to issue a WITH INIT before you iss
ue
> your nightly database backup.
> You could modify your hourly T-Log backups to backup WITH INIT. You will
> also need to add a job step which renames the files and includes a datetim
e
> identifier in the file name. Something like this:
> DECLARE @.str varchar(1000),
> @.dte varchar(100)
> SELECT @.dte = CONVERT(varchar(8),getdate(),112) + '.' +
> CONVERT(varchar(2),datepart(hh,getdate()
))
>
> SELECT @.str = 'master..xp_cmdshell ''rename x:\testfoo.txt testfoo.' +@.dte
+
> '.txt'''
> SELECT @.str /*if you want to see the command that will be executed*/
> EXEC (@.str) /*performs the file rename*/
>
> --
> Keith
>
> "Tyler" <Tyler@.discussions.microsoft.com> wrote in message
> news:CB42C0B9-D402-4275-ACC0-9FAE0384A7EC@.microsoft.com...
> every
> to
> once
> they
> overwritten
> But I
> that
>|||Yep, that solution will work but now you probably have
n number of jobs and n number of backup devices that you have to
maintain/monitor.
SQL Server does not have to backup to a device. It can simply backup to a
file.
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
(replace foo and x with appropriate values).
So, it is easy enough to do this all within one job that is scheduled to run
at whatever interval during the day that is needed. If it needs to run more
often that hourly it is possible to append the minute to the file name as
well. All that is needed is to increase the frequency of the job execution
and to include the minute within the file rename.
Keith
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:2C8D3FFA-02B7-4C80-AA2F-DC965AAD0C71@.microsoft.com...[vbcol=seagreen]
> I had a similar problem. I have created 8 backup devices (for each hour
> between 9 AM and 5 pm) with a name convention (transDump9, transDump10,
> transDump11, transDump12, transDump13...transDump17). I also have a job to
> dump the transaction log WITH INIT every hour...
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
> "Keith Kratochvil" wrote:
>
issue[vbcol=seagreen]
will[vbcol=seagreen]
datetime[vbcol=seagreen]
+@.dte +[vbcol=seagreen]
backup[vbcol=seagreen]
init"[vbcol=seagreen]
full DB[vbcol=seagreen]
hour.[vbcol=seagreen]
makes[vbcol=seagreen]
done.[vbcol=seagreen]
I[vbcol=seagreen]|||why not just use a db maintenance plan to backup the databases? the
backup files created from it will have the backup time as part of the
filename, hence they are unique filenames. use your tape backup to
backup the folders that contain the backup files. use the delete backup
files option to remove the older backup files from disk.
Tyler wrote:
> Currently my backup plan for my databases has them going to file backup ev
ery
> day and then to tape. My DB backup is set with the option of "with init"
to
> allow for backup to be overwritten.
> For my transaction logs they get backed up to disk every hour and then onc
e
> a day the backup file gets backed up to disk. The problem is on these the
y
> are set to use "with noinit" so these backup files are growing.
> If I switch the init option my transaction log backups will get overwritte
n
> every hour.
> Would this work if I had to do a a full restore? I would have the full DB
> backup from the night before and the trans backup from the latest hour. Bu
t I
> would not have the backup of the other transactions (hopefully that makes
> sense) between that latest hour and the when the full DB backup was done.
> The problem I have now is that with my trans bakcups being appended is tha
t
> there is backups of transactions that are older that the current day. I
> don't need to keep these as a do a full DB backup every day...
> Thanks.