Log backups to the same file

SQL Server 2000
If you write log backups to the same filename, does the data append, or does
it overwrite?Hi Jay
The INIT/NOINIT options for the backup command can be used for BACKUP LOG as
well as BACKUP DATABASE. In general the INIT will overwrite although there
are a few caveats
From BOL:
INIT
Specifies that all backup sets should be overwritten, but preserves the
media header. If INIT is specified, any existing backup set data on that
device is overwritten.
The backup media is not overwritten if any one of the following conditions
is met:
All backup sets on the media have not yet expired. For more information.
The backup set name given in the BACKUP statement, if provided, does not
match the name on the backup media. For more information, see the NAME
clause.
Use the SKIP option to override these checks. For more information about
interactions when using SKIP, NOSKIP, INIT, and NOINIT.
Note If the backup media is password protected, SQL Server does not write
to the media unless the media password is supplied. This check is not
overridden by the SKIP option. Password-protected media may be overwritten
only by reformatting it.
NOINIT
Indicates that the backup set is appended to the specified disk or tape
device, preserving existing backup sets. NOINIT is the default.
John
"Jay" wrote:
> SQL Server 2000
> If you write log backups to the same filename, does the data append, or does
> it overwrite?
>
>|||That depends on your usage of the WITH INIT/NOINIT in your BACKUP statement
"Jay" <spam@.nospam.org> wrote in message
news:eSo2bsf6HHA.3400@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2000
> If you write log backups to the same filename, does the data append, or
> does it overwrite?
>|||I have always put a date/time stamp on the TRN filenames, but the system I'm
currently looking at is just appending to the same TRN file all day, with
another system doing the write to tape.
I never even thought about doing it like that, but it does seem cleaner, in
that the pickup routine can specify exact filenames. The only real problem I
see is when the log backup is running and the pickup tries to take the file.
Which is best practices?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECB75004-5B14-4F10-9D3F-A4C9D64617FA@.microsoft.com...
> Hi Jay
> The INIT/NOINIT options for the backup command can be used for BACKUP LOG
> as
> well as BACKUP DATABASE. In general the INIT will overwrite although there
> are a few caveats
> From BOL:
> INIT
> Specifies that all backup sets should be overwritten, but preserves the
> media header. If INIT is specified, any existing backup set data on that
> device is overwritten.
> The backup media is not overwritten if any one of the following conditions
> is met:
> All backup sets on the media have not yet expired. For more information.
> The backup set name given in the BACKUP statement, if provided, does not
> match the name on the backup media. For more information, see the NAME
> clause.
> Use the SKIP option to override these checks. For more information about
> interactions when using SKIP, NOSKIP, INIT, and NOINIT.
> Note If the backup media is password protected, SQL Server does not write
> to the media unless the media password is supplied. This check is not
> overridden by the SKIP option. Password-protected media may be overwritten
> only by reformatting it.
> NOINIT
> Indicates that the backup set is appended to the specified disk or tape
> device, preserving existing backup sets. NOINIT is the default.
> John
> "Jay" wrote:
>> SQL Server 2000
>> If you write log backups to the same filename, does the data append, or
>> does
>> it overwrite?
>>

Log and DB backups without writing infomsgs to error log?

I was wondering if anyone knows of a way that you can have your backups not
write info messages to the sql server error log in SQL SERVER 2k5. We have
500 databases per instance of sql server and when we run hourly tran and
nightly full's we end up with 24*500 = 12000 rows of data that I don't need
to see in my error logs every night. As you can see with that nightly
volume of log data it is a nightmare when you have to go searching through
the logs. I've written a script that parses the results of the undocumented
proc 'sp_readerrorlog' and e-mails our DBA team any pertinent info it finds,
however it's a memory hog when it goes through a week's worth of log data (I
see excessive waits in the mssql_xp type when it runs). I ideally would
like to be able to parse through the logs every 15 minutes or so and alert
back through e-mail to the DBA's when it sees something concerning. Can I
set up db alerts to do this? If not I guess the only other option I have is
writing a standalone external app that can parse the error log and email me
when it sees concerning information. Has anyone else encountered this
situation? Any help/insight would be appreciated.
-AbairThere's a trace flag...
http://sqlblog.com/blogs/andrew_kelly/archive/2007/10/29/successful-backup-messages-no-more.aspx
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Michael Abair" <mabair@.autotask.com> wrote in message
news:OUZx$lKSIHA.3316@.TK2MSFTNGP02.phx.gbl...
>I was wondering if anyone knows of a way that you can have your backups not
>write info messages to the sql server error log in SQL SERVER 2k5. We have
>500 databases per instance of sql server and when we run hourly tran and
>nightly full's we end up with 24*500 = 12000 rows of data that I don't need
>to see in my error logs every night. As you can see with that nightly
>volume of log data it is a nightmare when you have to go searching through
>the logs. I've written a script that parses the results of the
>undocumented proc 'sp_readerrorlog' and e-mails our DBA team any pertinent
>info it finds, however it's a memory hog when it goes through a week's
>worth of log data (I see excessive waits in the mssql_xp type when it
>runs). I ideally would like to be able to parse through the logs every 15
>minutes or so and alert back through e-mail to the DBA's when it sees
>something concerning. Can I set up db alerts to do this? If not I guess
>the only other option I have is writing a standalone external app that can
>parse the error log and email me when it sees concerning information. Has
>anyone else encountered this situation? Any help/insight would be
>appreciated.
> -Abair
>|||It is interesting that I have seen some Oracle DBAs in the past doing just
that: parsing the logs to find errors and send alerts.
In SQL Server that is the job of the SQL Server Agent and you can define
Alerts to do the same job.
Anyway, in my opinion, even with alerts set up, it is always a good idea to
look at the error log once in a while.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Michael Abair" wrote:
> I was wondering if anyone knows of a way that you can have your backups not
> write info messages to the sql server error log in SQL SERVER 2k5. We have
> 500 databases per instance of sql server and when we run hourly tran and
> nightly full's we end up with 24*500 = 12000 rows of data that I don't need
> to see in my error logs every night. As you can see with that nightly
> volume of log data it is a nightmare when you have to go searching through
> the logs. I've written a script that parses the results of the undocumented
> proc 'sp_readerrorlog' and e-mails our DBA team any pertinent info it finds,
> however it's a memory hog when it goes through a week's worth of log data (I
> see excessive waits in the mssql_xp type when it runs). I ideally would
> like to be able to parse through the logs every 15 minutes or so and alert
> back through e-mail to the DBA's when it sees something concerning. Can I
> set up db alerts to do this? If not I guess the only other option I have is
> writing a standalone external app that can parse the error log and email me
> when it sees concerning information. Has anyone else encountered this
> situation? Any help/insight would be appreciated.
> -Abair
>
>|||> Anyway, in my opinion, even with alerts set up, it is always a good idea to
> look at the error log once in a while.
This statement seems to validate the 'Oracle DBA' approach. I mean, you
can't possibly just manually 'look at the error log once in a while' unless
you only have one, two, or a very small number of servers. Even that, it's
boring to look at these errorlog files manually. It's much better to let a
program do the borign work for you.
BTW, that is not an Oracle DBA approach, but is a generally effective
practice in my opinion.
Linchi
"Ben Nevarez" wrote:
> It is interesting that I have seen some Oracle DBAs in the past doing just
> that: parsing the logs to find errors and send alerts.
> In SQL Server that is the job of the SQL Server Agent and you can define
> Alerts to do the same job.
> Anyway, in my opinion, even with alerts set up, it is always a good idea to
> look at the error log once in a while.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Michael Abair" wrote:
> > I was wondering if anyone knows of a way that you can have your backups not
> > write info messages to the sql server error log in SQL SERVER 2k5. We have
> > 500 databases per instance of sql server and when we run hourly tran and
> > nightly full's we end up with 24*500 = 12000 rows of data that I don't need
> > to see in my error logs every night. As you can see with that nightly
> > volume of log data it is a nightmare when you have to go searching through
> > the logs. I've written a script that parses the results of the undocumented
> > proc 'sp_readerrorlog' and e-mails our DBA team any pertinent info it finds,
> > however it's a memory hog when it goes through a week's worth of log data (I
> > see excessive waits in the mssql_xp type when it runs). I ideally would
> > like to be able to parse through the logs every 15 minutes or so and alert
> > back through e-mail to the DBA's when it sees something concerning. Can I
> > set up db alerts to do this? If not I guess the only other option I have is
> > writing a standalone external app that can parse the error log and email me
> > when it sees concerning information. Has anyone else encountered this
> > situation? Any help/insight would be appreciated.
> >
> > -Abair
> >
> >
> >