Autonumber field: log

Showing posts with label log. Show all posts

Tuesday, March 27, 2012

Backing up secondary DB in log shipping ...

Hello can someone clear this up for me?
In BOL "Log Shipping Tips and Best Practices" is the following
recommendation:
"Generating Database Backups from the Secondary
Since the log shipped database is only a small increment of time out
of sync with the primary database, it is a good idea to use it to
generate the full database backups. This would keep the primary
production server free of the potential overhead and contention
incurred by a daily (or scheduled) backup. "
In my experience and from searching other threads in this group this
is actually not true - backing up the log shipped database will not
work, it will fail with the error:
"Database is in warm-standby state (set by executing RESTORE WITH
STANDBY) and cannot be backed up until the entire load sequence is
completed."
Am I missing something or is BOL just being misleading?
thanks,
PeterPeter,
http://support.microsoft.com/kb/311115 contains the correction.
Note: With SQL Server 2005, you it might be possible to create a snapshot
database over the secondary and back that up. (But I have no experience
with trying.)
RLF
<peter.wismer@.gmail.com> wrote in message
news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
> Hello can someone clear this up for me?
> In BOL "Log Shipping Tips and Best Practices" is the following
> recommendation:
> "Generating Database Backups from the Secondary
> Since the log shipped database is only a small increment of time out
> of sync with the primary database, it is a good idea to use it to
> generate the full database backups. This would keep the primary
> production server free of the potential overhead and contention
> incurred by a daily (or scheduled) backup. "
> In my experience and from searching other threads in this group this
> is actually not true - backing up the log shipped database will not
> work, it will fail with the error:
> "Database is in warm-standby state (set by executing RESTORE WITH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed."
> Am I missing something or is BOL just being misleading?
> thanks,
> Peter
>|||On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Peter,
> http://support.microsoft.com/kb/311115contains the correction.
> Note: With SQL Server 2005, you it might be possible to create a snapshot
> database over the secondary and back that up. (But I have no experience
> with trying.)
> RLF<peter.wis...@.gmail.com> wrote in message
> news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
>
> > Hello can someone clear this up for me?
> > In BOL "Log Shipping Tips and Best Practices" is the following
> > recommendation:
> > "Generating Database Backups from the Secondary
> > Since the log shipped database is only a small increment of time out
> > of sync with the primary database, it is a good idea to use it to
> > generate the full database backups. This would keep the primary
> > production server free of the potential overhead and contention
> > incurred by a daily (or scheduled) backup. "
> > In my experience and from searching other threads in this group this
> > is actually not true - backing up the log shipped database will not
> > work, it will fail with the error:
> > "Database is in warm-standby state (set by executing RESTORE WITH
> > STANDBY) and cannot be backed up until the entire load sequence is
> > completed."
> > Am I missing something or is BOL just being misleading?
> > thanks,
> > Peter- Hide quoted text -
> - Show quoted text -
Thanks very much Russell (I was beginning to mistrust my command of
the english language - thought I might need to find a Canadian
translation of BOL...)
Peter|||I think it was a case of wishes versus reality. - RLF
<peter.wismer@.gmail.com> wrote in message
news:1187288061.528821.290710@.j4g2000prf.googlegroups.com...
> On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
>> Peter,
>> http://support.microsoft.com/kb/311115contains the correction.
>> Note: With SQL Server 2005, you it might be possible to create a snapshot
>> database over the secondary and back that up. (But I have no experience
>> with trying.)
>> RLF<peter.wis...@.gmail.com> wrote in message
>> news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
>>
>> > Hello can someone clear this up for me?
>> > In BOL "Log Shipping Tips and Best Practices" is the following
>> > recommendation:
>> > "Generating Database Backups from the Secondary
>> > Since the log shipped database is only a small increment of time out
>> > of sync with the primary database, it is a good idea to use it to
>> > generate the full database backups. This would keep the primary
>> > production server free of the potential overhead and contention
>> > incurred by a daily (or scheduled) backup. "
>> > In my experience and from searching other threads in this group this
>> > is actually not true - backing up the log shipped database will not
>> > work, it will fail with the error:
>> > "Database is in warm-standby state (set by executing RESTORE WITH
>> > STANDBY) and cannot be backed up until the entire load sequence is
>> > completed."
>> > Am I missing something or is BOL just being misleading?
>> > thanks,
>> > Peter- Hide quoted text -
>> - Show quoted text -
> Thanks very much Russell (I was beginning to mistrust my command of
> the english language - thought I might need to find a Canadian
> translation of BOL...)
> Peter
>

Backing up secondary DB in log shipping ...

Backing up online log after loss of primary data file

My question is this:

Is there anyway to backup the current online log after complete loss of the current correponding datafile?

Example:

(2) Logical Disk Volumes

Disk 1 (D:) contains pubs_data.mdf
Disk 2 (E:) contains pubs_log.ldf

Disk 1 becomes corrupt and goes offline leaving the the database pubs in a suspect state. Is backup of the current online log pubs_log.ldf possible? If a backup of the log is not possible are there any other restoration methods that can be used to bring this database back online, rescuing as much information as possible from the current online log pubs_log.ldf.

Thanks,
PI can't beleive there is no way to get at the information in the current online log depite the fact that the database file is gone.

So, if I can't get at the current online log I must rely on a backup of the log which I would take every 5 or 15 minutes but that 0-5 minute period is not retrievable even if its just the data file that fails.

Any thoughts?

Sunday, March 25, 2012

Backing up log when recovery set to Simple

I have a job that I created that backs up transaction logs on a server that has many databases on it. If a db recovery model is set to simple, I want the job to skip over that database, rather then trying to backup the log.

It would be easy to just let it try, and fail, however, the job status will show failed, even if it backs up all database logs, and only fails on one. Is there a way to easily programatically detect if a db is set to simple, and then I can skip backing up that db log?

Thanks
TommySELECT DATABASEPROPERTYEX('Northwind','Recovery')|||Thanks brett, that's what I needed, in case anyone else is interested, here's the syntax I'm using:

Declare @.Recovery_Model nvarchar(20),
Select @.Recovery_Model = CONVERT(nvarchar(20), DATABASEPROPERTYEX( @.Database_Name , 'Recovery' ))
if (@.Recovery_Model = 'SIMPLE')
PRINT 'Simple Mode so skip'
else
PRINT 'do the needful'

Backing up log files

Hi,
I'd like to know what happens to a log file when you back it up. I received
an error that my log file had grown too much & that I should back it up,
which I did & I no longer received the error.
But what happens when you simply back up a log file? Does it also truncate
unused space or what? It obviously doesn't simply make a backup of it.
Many thanks for helping me understand what is happening here
AntBACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
reason don't want to do transaction log backup then set the recovery model for the database to
"simple".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
> Hi,
> I'd like to know what happens to a log file when you back it up. I received
> an error that my log file had grown too much & that I should back it up,
> which I did & I no longer received the error.
> But what happens when you simply back up a log file? Does it also truncate
> unused space or what? It obviously doesn't simply make a backup of it.
> Many thanks for helping me understand what is happening here
> Ant|||Tibor
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file).
I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
virtual logs file that LOG file is built by and delete only inactive
(written to disk) transaction
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
> BACKUP LOG will indeed empty the log file (also known as "truncate" the
> file). If you for some reason don't want to do transaction log backup then
> set the recovery model for the database to "simple".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>|||Hi Uri,
It all depends on what abstraction level we want to be. :-)
At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate", whichever
in English gets the message through). I prefer to say "empty", since I believe it makes it clearer
that the file isn't made smaller.
There are of course much more details what happens when you BACKUP LOG. I stay away from those
details when I see a basic question and I suspect the OP has limited experience with transaction log
internals - like virtual log files. The reasoning is that more details will only cloud the message.
:-)
As for the details, this is how I believe it works. Anyone is of course free to add and correct,
below if off the top of my head:
A VLF can be in four states:
A. Never ever used
B. Used, and all log records are prior than the last checkpoint and have also been written to disk.
C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
D. The active log (where the current log record, "head of the log", is).
BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever the
head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C and D
have status 2.
Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
tend to stick with the higher abstraction level for basic questions. :-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
> LOG file is built by and delete only inactive (written to disk) transaction
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>|||Tibor
The ability to think on abstaction level given from God, I mean you canot
learn this. :-)
And you I think have it.
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file
Yep, that it was my understanding as well
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Tibor
The two middle states have nothing to do with Checkpoint. The have to do
with whether those transactions have been backed up, so the log space can be
reused.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Hi Uri,
> It all depends on what abstraction level we want to be. :-)
> At a higher abstraction level, I think it suffices to say "empty the log"
> (or "truncate", whichever in English gets the message through). I prefer
> to say "empty", since I believe it makes it clearer that the file isn't
> made smaller.
> There are of course much more details what happens when you BACKUP LOG. I
> stay away from those details when I see a basic question and I suspect the
> OP has limited experience with transaction log internals - like virtual
> log files. The reasoning is that more details will only cloud the message.
> :-)
> As for the details, this is how I believe it works. Anyone is of course
> free to add and correct, below if off the top of my head:
> A VLF can be in four states:
> A. Never ever used
> B. Used, and all log records are prior than the last checkpoint and have
> also been written to disk.
> C. Used, but some log records are more recent than last checkpoint and/or
> not written to disk.
> D. The active log (where the current log record, "head of the log", is).
> BACKUP will not actually delete anything from the log file, it will just
> allow for re-use of the virtual log file. Basically turning C above into
> B. A and B can be re-used (overwritten whenever the head of the log moves
> to this virtual log file). These have status 0 in DBCC LOGINFO, while C
> and D have status 2.
> Now, above takes some thinking and some visualization in the head (or
> whiteboard). Which is why I tend to stick with the higher abstraction
> level for basic questions. :-).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use
>> virtual logs file that LOG file is built by and delete only inactive
>> (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the
>> file). If you for some reason don't want to do transaction log backup
>> then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>|||Hi Kalen,
Thanks. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi Tibor
> The two middle states have nothing to do with Checkpoint. The have to do with whether those
> transactions have been backed up, so the log space can be reused.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the log" (or "truncate",
>> whichever in English gets the message through). I prefer to say "empty", since I believe it makes
>> it clearer that the file isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG. I stay away from those
>> details when I see a basic question and I suspect the OP has limited experience with transaction
>> log internals - like virtual log files. The reasoning is that more details will only cloud the
>> message. :-)
>> As for the details, this is how I believe it works. Anyone is of course free to add and correct,
>> below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have also been written to
>> disk.
>> C. Used, but some log records are more recent than last checkpoint and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just allow for re-use of the
>> virtual log file. Basically turning C above into B. A and B can be re-used (overwritten whenever
>> the head of the log moves to this virtual log file). These have status 0 in DBCC LOGINFO, while C
>> and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or whiteboard). Which is why I
>> tend to stick with the higher abstraction level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to re-use virtual logs file that
>> LOG file is built by and delete only inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate" the file). If you for some
>> reason don't want to do transaction log backup then set the recovery model for the database to
>> "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I received
>> an error that my log file had grown too much & that I should back it up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also truncate
>> unused space or what? It obviously doesn't simply make a backup of it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>|||You're welcome.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWx%23pC1YIHA.3880@.TK2MSFTNGP05.phx.gbl...
> Hi Kalen,
> Thanks. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23F5ejyrYIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi Tibor
>> The two middle states have nothing to do with Checkpoint. The have to do
>> with whether those transactions have been backed up, so the log space can
>> be reused.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:eAHQNOlYIHA.5160@.TK2MSFTNGP05.phx.gbl...
>> Hi Uri,
>> It all depends on what abstraction level we want to be. :-)
>> At a higher abstraction level, I think it suffices to say "empty the
>> log" (or "truncate", whichever in English gets the message through). I
>> prefer to say "empty", since I believe it makes it clearer that the file
>> isn't made smaller.
>> There are of course much more details what happens when you BACKUP LOG.
>> I stay away from those details when I see a basic question and I suspect
>> the OP has limited experience with transaction log internals - like
>> virtual log files. The reasoning is that more details will only cloud
>> the message. :-)
>> As for the details, this is how I believe it works. Anyone is of course
>> free to add and correct, below if off the top of my head:
>> A VLF can be in four states:
>> A. Never ever used
>> B. Used, and all log records are prior than the last checkpoint and have
>> also been written to disk.
>> C. Used, but some log records are more recent than last checkpoint
>> and/or not written to disk.
>> D. The active log (where the current log record, "head of the log", is).
>> BACKUP will not actually delete anything from the log file, it will just
>> allow for re-use of the virtual log file. Basically turning C above into
>> B. A and B can be re-used (overwritten whenever the head of the log
>> moves to this virtual log file). These have status 0 in DBCC LOGINFO,
>> while C and D have status 2.
>> Now, above takes some thinking and some visualization in the head (or
>> whiteboard). Which is why I tend to stick with the higher abstraction
>> level for basic questions. :-).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ucYuxAlYIHA.3964@.TK2MSFTNGP03.phx.gbl...
>> Tibor
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file).
>> I was thinking that by issuing BACKUP LOG , SQL Server is able to
>> re-use virtual logs file that LOG file is built by and delete only
>> inactive (written to disk) transaction
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:uinyn2kYIHA.3400@.TK2MSFTNGP03.phx.gbl...
>> BACKUP LOG will indeed empty the log file (also known as "truncate"
>> the file). If you for some reason don't want to do transaction log
>> backup then set the recovery model for the database to "simple".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:505DB4BD-BEF0-4708-BEEE-BC4088A82527@.microsoft.com...
>> Hi,
>> I'd like to know what happens to a log file when you back it up. I
>> received
>> an error that my log file had grown too much & that I should back it
>> up,
>> which I did & I no longer received the error.
>> But what happens when you simply back up a log file? Does it also
>> truncate
>> unused space or what? It obviously doesn't simply make a backup of
>> it.
>> Many thanks for helping me understand what is happening here
>> Ant
>>
>>
>>
>>
>

Backing up log file with a script setup as a job

Is there any way to include in this script below something
to tell the job to backup will the log file when it gets
to a certain percentage say 85%.
Thanks,
Aboki.
/* Script for backing up the transaction log when it is
getting full and clear the space for the log file. */
/* This can also apply to any other system but you need to
change drive and part location */
/* to match where backup location */
Backup LOG IntegraProd TO
DISK = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\IntegraProdTran.bak'
GOYou could write a job to run every so often, which uses DBCC PERFLOG
to get information about log space and log space used. If log space for your
database is > 85% then issue your t-sql command.
The log is cyclic, it should wrap around and re-use empty space
where possible. It needs to keep information on the oldest active
transaction
for rollback. If your log is constantly at a certain size, this will most
likely be
right for the database anyway.
HTH
"Aboki" <waco361@.hotmail.com> wrote in message
news:08d801c3627b$37d262a0$a001280a@.phx.gbl...
> Is there any way to include in this script below something
> to tell the job to backup will the log file when it gets
> to a certain percentage say 85%.
> Thanks,
> Aboki.
>
> /* Script for backing up the transaction log when it is
> getting full and clear the space for the log file. */
> /* This can also apply to any other system but you need to
> change drive and part location */
> /* to match where backup location */
>
> Backup LOG IntegraProd TO
> DISK = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\IntegraProdTran.bak'
> GO|||You can set an alert to start the job. There should be an example alert you
can modify already installed with SQL.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Aboki" <waco361@.hotmail.com> wrote in message
news:08d801c3627b$37d262a0$a001280a@.phx.gbl...
> Is there any way to include in this script below something
> to tell the job to backup will the log file when it gets
> to a certain percentage say 85%.
> Thanks,
> Aboki.
>
> /* Script for backing up the transaction log when it is
> getting full and clear the space for the log file. */
> /* This can also apply to any other system but you need to
> change drive and part location */
> /* to match where backup location */
>
> Backup LOG IntegraProd TO
> DISK = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\IntegraProdTran.bak'
> GOsql

Backing Up Issue

My transaction log is growing really big few hundred MB.
However before I do transaction log backup, I read that I
need to do a full database backup at least once. Also
clearing the transaction log before you backup the
database results in a smaller backup of the full database
I should have done earlier when the log was small.
My question is : Does it mean that I have no other way
except to back up the database with the full transaction
log.. ?
After the full database backup, I will then do a
transaction log backupYou could truncate the log and immediately perform a full database backup.
Given that you're never performed a full database backup anyway, this should
be fine in your situation.
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
followed by DBCC SHRINKFILE .. command for the log file.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>|||When the database is in FULL recovery mode, the log will automatically
truncate until you do the first full db backup, because it has no value
until then...So your log should not be growing if you haven't made the first
db backup...
Peter's response is one of your options...
Long term, if you need to be able to recover all of the data.
Start scheduling a regular full database backup, followed by regular log
backups as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>|||Thanks for the advice..
So even though I've not done even one full database
backup before, I can still safely 1) Backup the log with
truncate only, followed by 2) DBCC Shrinkfile for the log
3) Then do a full database backup...
Can I say it is safe to NOT do at least one full database
backup before doing a log ?|||> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
Yes, since the log is useless without a full database backup. I believe SQL
Server makes this check too i.e. prevents you from making a trx log backup
if no full backup has ever been done.

> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
What do you mean by 'doing a log'?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:49aa01c4803a$aa694260$a501280a@.phx.gbl...
> Thanks for the advice..
> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
>
>|||Hi Peter,
Thanks for you reply to the first part of my question..

database[vbcol=seagreen]
>What do you mean by 'doing a log'?
I mean 'doing a transaction log backup' Please excuse me
for my type error.|||If you change from Simple to Full or Bulk-logged recovery, or if you are
intializing log shipping, you must take a full database backup before
starting transaction log backups. The database annot be in the Simple
recovery model if you want to take transaction log backups.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:564501c4810b$02640830$a501280a@.phx.gbl...
> Hi Peter,
> Thanks for you reply to the first part of my question..
>
> database
> I mean 'doing a transaction log backup' Please excuse me
> for my type error.
>

Backing Up Issue

My transaction log is growing really big few hundred MB.
However before I do transaction log backup, I read that I
need to do a full database backup at least once. Also
clearing the transaction log before you backup the
database results in a smaller backup of the full database
I should have done earlier when the log was small.
My question is : Does it mean that I have no other way
except to back up the database with the full transaction
log.. ?
After the full database backup, I will then do a
transaction log backup
You could truncate the log and immediately perform a full database backup.
Given that you're never performed a full database backup anyway, this should
be fine in your situation.
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
followed by DBCC SHRINKFILE .. command for the log file.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>
|||When the database is in FULL recovery mode, the log will automatically
truncate until you do the first full db backup, because it has no value
until then...So your log should not be growing if you haven't made the first
db backup...
Peter's response is one of your options...
Long term, if you need to be able to recover all of the data.
Start scheduling a regular full database backup, followed by regular log
backups as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>
|||Thanks for the advice..
So even though I've not done even one full database
backup before, I can still safely 1) Backup the log with
truncate only, followed by 2) DBCC Shrinkfile for the log
3) Then do a full database backup...
Can I say it is safe to NOT do at least one full database
backup before doing a log ?
|||> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
Yes, since the log is useless without a full database backup. I believe SQL
Server makes this check too i.e. prevents you from making a trx log backup
if no full backup has ever been done.

> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
What do you mean by 'doing a log'?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:49aa01c4803a$aa694260$a501280a@.phx.gbl...
> Thanks for the advice..
> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
>
>
|||Hi Peter,
Thanks for you reply to the first part of my question..
[vbcol=seagreen]
database
>What do you mean by 'doing a log'?
I mean 'doing a transaction log backup' Please excuse me
for my type error.
|||If you change from Simple to Full or Bulk-logged recovery, or if you are
intializing log shipping, you must take a full database backup before
starting transaction log backups. The database annot be in the Simple
recovery model if you want to take transaction log backups.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:564501c4810b$02640830$a501280a@.phx.gbl...
> Hi Peter,
> Thanks for you reply to the first part of my question..
> database
> I mean 'doing a transaction log backup' Please excuse me
> for my type error.
>

Backing Up Issue

My transaction log is growing really big few hundred MB.
However before I do transaction log backup, I read that I
need to do a full database backup at least once. Also
clearing the transaction log before you backup the
database results in a smaller backup of the full database
I should have done earlier when the log was small.
My question is : Does it mean that I have no other way
except to back up the database with the full transaction
log.. ?
After the full database backup, I will then do a
transaction log backupYou could truncate the log and immediately perform a full database backup.
Given that you're never performed a full database backup anyway, this should
be fine in your situation.
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
followed by DBCC SHRINKFILE .. command for the log file.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>|||When the database is in FULL recovery mode, the log will automatically
truncate until you do the first full db backup, because it has no value
until then...So your log should not be growing if you haven't made the first
db backup...
Peter's response is one of your options...
Long term, if you need to be able to recover all of the data.
Start scheduling a regular full database backup, followed by regular log
backups as well...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:39bd01c47f48$57251f40$a301280a@.phx.gbl...
> My transaction log is growing really big few hundred MB.
> However before I do transaction log backup, I read that I
> need to do a full database backup at least once. Also
> clearing the transaction log before you backup the
> database results in a smaller backup of the full database
> I should have done earlier when the log was small.
> My question is : Does it mean that I have no other way
> except to back up the database with the full transaction
> log.. ?
> After the full database backup, I will then do a
> transaction log backup
>|||Thanks for the advice..
So even though I've not done even one full database
backup before, I can still safely 1) Backup the log with
truncate only, followed by 2) DBCC Shrinkfile for the log
3) Then do a full database backup...
Can I say it is safe to NOT do at least one full database
backup before doing a log ?|||> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
Yes, since the log is useless without a full database backup. I believe SQL
Server makes this check too i.e. prevents you from making a trx log backup
if no full backup has ever been done.
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
What do you mean by 'doing a log'?
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:49aa01c4803a$aa694260$a501280a@.phx.gbl...
> Thanks for the advice..
> So even though I've not done even one full database
> backup before, I can still safely 1) Backup the log with
> truncate only, followed by 2) DBCC Shrinkfile for the log
> 3) Then do a full database backup...
> Can I say it is safe to NOT do at least one full database
> backup before doing a log ?
>
>|||Hi Peter,
Thanks for you reply to the first part of my question..
>> Can I say it is safe to NOT do at least one full
database
>> backup before doing a log ?
>What do you mean by 'doing a log'?
I mean 'doing a transaction log backup' Please excuse me
for my type error.|||If you change from Simple to Full or Bulk-logged recovery, or if you are
intializing log shipping, you must take a full database backup before
starting transaction log backups. The database annot be in the Simple
recovery model if you want to take transaction log backups.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:564501c4810b$02640830$a501280a@.phx.gbl...
> Hi Peter,
> Thanks for you reply to the first part of my question..
> >> Can I say it is safe to NOT do at least one full
> database
> >> backup before doing a log ?
> >
> >What do you mean by 'doing a log'?
> I mean 'doing a transaction log backup' Please excuse me
> for my type error.
>

Backing up databases in a mirror configuration

I have a user database maintance plan with subplans for full and
transaction log backups deployed to both the Principal and Mirror
servers. Each subplan has its own schedule.
Obviously, the principal server executes a successfully backup, while
the mirror copy fails. This generates errors that are picked up by
MOM and also from the jobs themselves.
In order to eliminate these errors, I emplyed a method that prevents
the jobs from running if the server is hosting the mirror copy (note
that all the databases on the server are mirrored):
1) Remove the schedule from the job that runs the SSIS package - job
name 'User Databases.Full Database Backup'
2) Create a new job called 'User Databases.Full Database Backup Status
Check' that checks for the read/write and online status of the
databases and executes the job in step one if they are online
3) Add a schedule to the job 'User Databases.Full Database Backup
Status Check' with the same details as that removed in step 1
I have a big problem with this approach: if any of the databases are
the mirror, then the entire backup subplan is not run - even if some
of the databases are the principal on the same server - it's an all or
nothing approach since the databases checked in step 2 may not
necessarily match the list of databases defined in the subplan.
Clearly, I'd like the checking step to only be affected by the
databases defined in the subplan, but I cannot think of a way of
interrogating the subplan details using SQL code to determine this.
Ideally, I would like to edit the maintenance plan's list of databases
to only include databases that are the principal on that server and to
do it each time the job is executed - maybe using the two job approach
as described above.
I would like some advice on a way to do this or whether I am barking
up the wrong tree and should try a different angle - perhaps defining
my own backup process (I really want to avoid doing this). Clearly,
if the maintenance plan could detect the fact that the database is a
mirror copy and just skip it, the whole use goes away, but it does
not.
Thanks> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this).
This is what you would have to do. Maint plans are for simpler installations
, and conditionally
handling a mirrored database *inside* a backup task is not available. Consid
er wishing this at
connect.microsoft.com.
Another options is to have one backup task per database, and have a TSQL tas
k before each which
check the mirroring catalog views and if a mirror, moves to the next databas
e instead. A bit messy,
but should be doable.
Having your own scripts for performing a backup isn't difficult, though. You
can still use the maint
plan to remove old backup files. (Google for how to generate the backup comm
and with a time stamp in
the file name and you will find examples...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viracochas" <warmtea@.hotmail.com> wrote in message
news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
>I have a user database maintance plan with subplans for full and
> transaction log backups deployed to both the Principal and Mirror
> servers. Each subplan has its own schedule.
> Obviously, the principal server executes a successfully backup, while
> the mirror copy fails. This generates errors that are picked up by
> MOM and also from the jobs themselves.
> In order to eliminate these errors, I emplyed a method that prevents
> the jobs from running if the server is hosting the mirror copy (note
> that all the databases on the server are mirrored):
> 1) Remove the schedule from the job that runs the SSIS package - job
> name 'User Databases.Full Database Backup'
> 2) Create a new job called 'User Databases.Full Database Backup Status
> Check' that checks for the read/write and online status of the
> databases and executes the job in step one if they are online
> 3) Add a schedule to the job 'User Databases.Full Database Backup
> Status Check' with the same details as that removed in step 1
> I have a big problem with this approach: if any of the databases are
> the mirror, then the entire backup subplan is not run - even if some
> of the databases are the principal on the same server - it's an all or
> nothing approach since the databases checked in step 2 may not
> necessarily match the list of databases defined in the subplan.
> Clearly, I'd like the checking step to only be affected by the
> databases defined in the subplan, but I cannot think of a way of
> interrogating the subplan details using SQL code to determine this.
> Ideally, I would like to edit the maintenance plan's list of databases
> to only include databases that are the principal on that server and to
> do it each time the job is executed - maybe using the two job approach
> as described above.
> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this). Clearly,
> if the maintenance plan could detect the fact that the database is a
> mirror copy and just skip it, the whole use goes away, but it does
> not.
> Thanks
>|||On 21 May, 13:43, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is what you would have to do. Maint plans are for simpler installatio
ns, and conditionally
> handling a mirrored database *inside* a backup task is not available. Cons
ider wishing this at
> connect.microsoft.com.
> Another options is to have one backup task per database, and have a TSQL t
ask before each which
> check the mirroring catalog views and if a mirror, moves to the next datab
ase instead. A bit messy,
> but should be doable.
> Having your own scripts for performing a backup isn't difficult, though. Y
ou can still use the maint
> plan to remove old backup files. (Google for how to generate the backup co
mmand with a time stamp in
> the file name and you will find examples...)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Viracochas" <warm...@.hotmail.com> wrote in message
> news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
Thanks for the feedback. I feared there was no easy way, though it
won't be too difficult to set it up.

Backing up databases in a mirror configuration

I have a user database maintance plan with subplans for full and
transaction log backups deployed to both the Principal and Mirror
servers. Each subplan has its own schedule.
Obviously, the principal server executes a successfully backup, while
the mirror copy fails. This generates errors that are picked up by
MOM and also from the jobs themselves.
In order to eliminate these errors, I emplyed a method that prevents
the jobs from running if the server is hosting the mirror copy (note
that all the databases on the server are mirrored):
1) Remove the schedule from the job that runs the SSIS package - job
name 'User Databases.Full Database Backup'
2) Create a new job called 'User Databases.Full Database Backup Status
Check' that checks for the read/write and online status of the
databases and executes the job in step one if they are online
3) Add a schedule to the job 'User Databases.Full Database Backup
Status Check' with the same details as that removed in step 1
I have a big problem with this approach: if any of the databases are
the mirror, then the entire backup subplan is not run - even if some
of the databases are the principal on the same server - it's an all or
nothing approach since the databases checked in step 2 may not
necessarily match the list of databases defined in the subplan.
Clearly, I'd like the checking step to only be affected by the
databases defined in the subplan, but I cannot think of a way of
interrogating the subplan details using SQL code to determine this.
Ideally, I would like to edit the maintenance plan's list of databases
to only include databases that are the principal on that server and to
do it each time the job is executed - maybe using the two job approach
as described above.
I would like some advice on a way to do this or whether I am barking
up the wrong tree and should try a different angle - perhaps defining
my own backup process (I really want to avoid doing this). Clearly,
if the maintenance plan could detect the fact that the database is a
mirror copy and just skip it, the whole use goes away, but it does
not.
Thanks> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this).
This is what you would have to do. Maint plans are for simpler installations, and conditionally
handling a mirrored database *inside* a backup task is not available. Consider wishing this at
connect.microsoft.com.
Another options is to have one backup task per database, and have a TSQL task before each which
check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
but should be doable.
Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
the file name and you will find examples...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viracochas" <warmtea@.hotmail.com> wrote in message
news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
>I have a user database maintance plan with subplans for full and
> transaction log backups deployed to both the Principal and Mirror
> servers. Each subplan has its own schedule.
> Obviously, the principal server executes a successfully backup, while
> the mirror copy fails. This generates errors that are picked up by
> MOM and also from the jobs themselves.
> In order to eliminate these errors, I emplyed a method that prevents
> the jobs from running if the server is hosting the mirror copy (note
> that all the databases on the server are mirrored):
> 1) Remove the schedule from the job that runs the SSIS package - job
> name 'User Databases.Full Database Backup'
> 2) Create a new job called 'User Databases.Full Database Backup Status
> Check' that checks for the read/write and online status of the
> databases and executes the job in step one if they are online
> 3) Add a schedule to the job 'User Databases.Full Database Backup
> Status Check' with the same details as that removed in step 1
> I have a big problem with this approach: if any of the databases are
> the mirror, then the entire backup subplan is not run - even if some
> of the databases are the principal on the same server - it's an all or
> nothing approach since the databases checked in step 2 may not
> necessarily match the list of databases defined in the subplan.
> Clearly, I'd like the checking step to only be affected by the
> databases defined in the subplan, but I cannot think of a way of
> interrogating the subplan details using SQL code to determine this.
> Ideally, I would like to edit the maintenance plan's list of databases
> to only include databases that are the principal on that server and to
> do it each time the job is executed - maybe using the two job approach
> as described above.
> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this). Clearly,
> if the maintenance plan could detect the fact that the database is a
> mirror copy and just skip it, the whole use goes away, but it does
> not.
> Thanks
>|||On 21 May, 13:43, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > I would like some advice on a way to do this or whether I am barking
> > up the wrong tree and should try a different angle - perhaps defining
> > my own backup process (I really want to avoid doing this).
> This is what you would have to do. Maint plans are for simpler installations, and conditionally
> handling a mirrored database *inside* a backup task is not available. Consider wishing this at
> connect.microsoft.com.
> Another options is to have one backup task per database, and have a TSQL task before each which
> check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
> but should be doable.
> Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
> plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
> the file name and you will find examples...)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Viracochas" <warm...@.hotmail.com> wrote in message
> news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
Thanks for the feedback. I feared there was no easy way, though it
won't be too difficult to set it up.

Backing up Database

When I try to backup a SQL 2000 database I recieve the following error in
the output log. Any ideas? Thank you.
SQL 2000 w/ SP4
[39] Database SolRounder: Database Backup...
Destination: [G:\Backups\SolRounder\SolRounder_db_200704131221.BAK]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL
Server Driver][SQL Server]Write on
'G:\Backups\SolRounder\SolRounder_db_200704131221.BAK' failed, status = 112.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
[40] Database SolRounderKids: Database Backup...
Destination:
[G:\Backups\SolRounderKids\SolRounderKids_db_200704131223.BAK]Gabe,
Error 112 is probably the Windows ERROR_DISK_FULL, which means that your G:
drive does not have enough space to hold your backup file.
See if you can clean up some things from the drive to give it more space.
RLF
"Gabe Matteson\" <gmatteson.rounder.com.nospam> wrote in message
news:Oz8yowefHHA.4552@.TK2MSFTNGP04.phx.gbl...
> When I try to backup a SQL 2000 database I recieve the following error in
> the output log. Any ideas? Thank you.
> SQL 2000 w/ SP4
> [39] Database SolRounder: Database Backup...
> Destination: [G:\Backups\SolRounder\SolRounder_db_200704131221.BAK]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Write on
> 'G:\Backups\SolRounder\SolRounder_db_200704131221.BAK' failed, status => 112. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> [40] Database SolRounderKids: Database Backup...
> Destination:
> [G:\Backups\SolRounderKids\SolRounderKids_db_200704131223.BAK]
>|||Working now. Thank you Russell!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ueOlD4efHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Gabe,
> Error 112 is probably the Windows ERROR_DISK_FULL, which means that your
> G: drive does not have enough space to hold your backup file.
> See if you can clean up some things from the drive to give it more space.
> RLF
> "Gabe Matteson\" <gmatteson.rounder.com.nospam> wrote in message
> news:Oz8yowefHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> When I try to backup a SQL 2000 database I recieve the following error in
>> the output log. Any ideas? Thank you.
>> SQL 2000 w/ SP4
>> [39] Database SolRounder: Database Backup...
>> Destination: [G:\Backups\SolRounder\SolRounder_db_200704131221.BAK]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Write on
>> 'G:\Backups\SolRounder\SolRounder_db_200704131221.BAK' failed, status =>> 112. See the SQL Server error log for more details.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [40] Database SolRounderKids: Database Backup...
>> Destination:
>> [G:\Backups\SolRounderKids\SolRounderKids_db_200704131223.BAK]
>

Thursday, March 22, 2012

Backing up and Truncating a transaction log in SQL

I have a transaction log on on of my databases that is very big, and I need to backup the logs and truncate them to get some free disk space. The backup of the log needs to be placed on one of my remote backup servers. I would like to perform this task in SQL so I can put the code in a scheduled job. Thanks in advance for any help.Let me know if you mange to arrive at a defined process !!!
I do have the same issue|||Do you care about what is in the log? What I mean is do you think you will have to recover from the log backup or not? If not, you can just dump it without creating a backup file. Then you can shrink the log file using DBCC Shrinkfile.|||My company likes to keep a backup of all logs no matter what for auditing purposes.|||You need to set up a shared folder in the reomote server and run the following script,

BACKUP LOG mydb TO DISK = '\\remoteservername\sharedfoledername\mydb_log_bac kup' WITH INIT, NOUNLOAD, NAME = 'mydb_log_backup', NOSKIP , STATS = 10, NOFORMAT|||Would that script also truncate the logs, or do I have to run BACKUP LOG DB WITH TRUNCATE_ONLY afterwards. Also what extension do I use for the backup log file. Example: DB_Logs_2003.bak. Is that OK? Thanks for all you help so far.|||??!! That's what I was asking you about. 'With Truncate only' will only dump the log for you without actually backing up the log for you. If you need keep the backup file then you should not use that option. The script DOES backup. Extension doesn't matter.|||Oh! I see what you are saying. Now I fully understand. Thanks for all the help.

Backing up a restoring or standby database

Hi,
I would like to backup a secondary log shippping database (in restoring or
standby mode). I need to use the backup as an up to date test database on the
same server but don't have the bandwidth to copy the primary backups over.
I've tried the backup command which results in an error saying that the
restore sequence must be completed first.
I also tried detaching the database with a view to copying the files but
when attempting to attach it again sql server produced another restore
sequence error.
Anyone know if it is possible to do a backup of a log shipping secondary, by
foul means or fair?
Cheers,
Kev.
You'd have to use recovery to be able to do the backup but this'd invalidate
the log-shipping chain. for a test database you might roll your own 'database
shipping' solution which is something a few people on this newsgroup seem to
be using...
HTH,
Paul Ibison
|||Okay, thanks.
Kev.
"Paul Ibison" wrote:

> You'd have to use recovery to be able to do the backup but this'd invalidate
> the log-shipping chain. for a test database you might roll your own 'database
> shipping' solution which is something a few people on this newsgroup seem to
> be using...
> HTH,
> Paul Ibison
>

Backing up a primary log shipping database

Hi,
Does backing up a primary database in a log shipping pair cause problem in
the log shipping?
Aside from an on-going log shipping maintenance between a Primary &
Secondary (latency 15 minutes) database, I would like to schedule a daily
complete backup of the primary database and an hourly transaction log backup.
I already tested this and so far log shipping continued but I just want to
confirm if this has no lurking issues. AFAIK, transaction log backup
truncates the log files that is why I am wondering how can the other
transaction log backup the log files if it is already truncated.
Your comments will be highly appreciated.
--
...A database backup does *not* break the sequence of log backups. I.e., it is safe to do database
backups of a log shipped database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CarloVino" <CarloVino@.discussions.microsoft.com> wrote in message
news:714B75B2-AA8C-46FF-8B1F-A1B6B348F2CA@.microsoft.com...
> Hi,
> Does backing up a primary database in a log shipping pair cause problem in
> the log shipping?
> Aside from an on-going log shipping maintenance between a Primary &
> Secondary (latency 15 minutes) database, I would like to schedule a daily
> complete backup of the primary database and an hourly transaction log backup.
> I already tested this and so far log shipping continued but I just want to
> confirm if this has no lurking issues. AFAIK, transaction log backup
> truncates the log files that is why I am wondering how can the other
> transaction log backup the log files if it is already truncated.
> Your comments will be highly appreciated.
> --
> ...|||This is a multi-part message in MIME format.
--010003020805070908090803
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
But what about transaction log backups? Does xp_sqlmaint (when it's
doing the copy phase on the secondary server) look up the msdb.backupset
& msdb.backupmediafamily tables on the primary server to get the
location of the physical log backup files (even the ones done by
processes other than the logshipping plan's xp_sqlmaint call, like
another maint plan or ad-hoc tlog backups)? Or does it just pick up log
backup files from a certain directory or created by the xp_sqlmaint
backup phase on the primary?
If it's the later then ad-hoc log backups, or log backups done by
another maint plan, would disrupt the logshipping sync (and in fact the
log shipping tlog backups would make the log backups done by the other
maint plan out of sequence).
On the whole, having 2 different plans (one log shipping plan and one
other maint plan) doing transaction log backups just sounds like a bad
idea (and redundant anyway). Why have another plan doing hourly tlog
backups when the logshipping plan is doing them every 15 minutes anyway?
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tibor Karaszi wrote:
>A database backup does *not* break the sequence of log backups. I.e., it is safe to do database
>backups of a log shipped database.
>
>
--010003020805070908090803
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>But what about transaction log backups? Does xp_sqlmaint (when
it's doing the copy phase on the secondary server) look up the
msdb.backupset & msdb.backupmediafamily tables on the primary
server to get the location of the physical log backup files (even the
ones done by processes other than the logshipping plan's xp_sqlmaint
call, like another maint plan or ad-hoc tlog backups)? Or does it just
pick up log backup files from a certain directory or created by the
xp_sqlmaint backup phase on the primary? 
 
If it's the later then ad-hoc log backups, or log backups done by
another maint plan, would disrupt the logshipping sync (and in fact the
log shipping tlog backups would make the log backups done by the other
maint plan out of sequence). 
 
On the whole, having 2 different plans (one log shipping plan and one
other maint plan) doing transaction log backups just sounds like a bad
idea (and redundant anyway). Why have another plan doing hourly tlog
backups when the logshipping plan is doing them every 15 minutes anyway? 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Tibor Karaszi wrote:
<blockquote cite="mideqP33exzFHA.3312@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">A database backup does *not* break the sequence of log backups. I.e., it is safe to do database
backups of a log shipped database.
</pre>
</blockquote>
</body>
</html>
--010003020805070908090803--|||I don't use xp_sqlmaint for log shipping. But I'd be very surprise if it would pick up out-of-band
backups. My point was that out-of-band *database* backup doesn't disrupt log shipping. Out-of-band
log backups does. I was under the impression that the OP was asking about database backup, so thanks
for pushing the point, Mike. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:ONlx0j4zFHA.1192@.TK2MSFTNGP10.phx.gbl...
> But what about transaction log backups? Does xp_sqlmaint (when it's
> doing the copy phase on the secondary server) look up the msdb.backupset
> & msdb.backupmediafamily tables on the primary server to get the
> location of the physical log backup files (even the ones done by
> processes other than the logshipping plan's xp_sqlmaint call, like
> another maint plan or ad-hoc tlog backups)? Or does it just pick up log
> backup files from a certain directory or created by the xp_sqlmaint
> backup phase on the primary?
> If it's the later then ad-hoc log backups, or log backups done by
> another maint plan, would disrupt the logshipping sync (and in fact the
> log shipping tlog backups would make the log backups done by the other
> maint plan out of sequence).
> On the whole, having 2 different plans (one log shipping plan and one
> other maint plan) doing transaction log backups just sounds like a bad
> idea (and redundant anyway). Why have another plan doing hourly tlog
> backups when the logshipping plan is doing them every 15 minutes anyway?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Tibor Karaszi wrote:
>>A database backup does *not* break the sequence of log backups. I.e., it is safe to do database
>>backups of a log shipped database.
>>
>|||This is a multi-part message in MIME format.
--050800090501060406070700
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
How do you implement log shipping if you don't use xp_sqlmaint (as
that's what the DB Maintenance Wizard uses when it creates all the
SQLAgent jobs)? Have you implemented your own home-grown log shipping
solution?
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tibor Karaszi wrote:
>I don't use xp_sqlmaint for log shipping. But I'd be very surprise if it would pick up out-of-band
>backups. My point was that out-of-band *database* backup doesn't disrupt log shipping. Out-of-band
>log backups does. I was under the impression that the OP was asking about database backup, so thanks
>for pushing the point, Mike. :-)
>
>
--050800090501060406070700
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>How do you implement log shipping if you don't use xp_sqlmaint (as
that's what the DB Maintenance Wizard uses when it creates all the
SQLAgent jobs)? Have you implemented your own home-grown log shipping
solution?</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Tibor Karaszi wrote:
<blockquote cite="mide9cjH47zFHA.3152@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">I don't use xp_sqlmaint for log shipping. But I'd be very surprise if it would pick up out-of-band
backups. My point was that out-of-band *database* backup doesn't disrupt log shipping. Out-of-band
log backups does. I was under the impression that the OP was asking about database backup, so thanks
for pushing the point, Mike. :-)
</pre>
</blockquote>
</body>
</html>
--050800090501060406070700--|||> Have you implemented your own home-grown log shipping
> solution?
Yep. I'm using www.dbmaint.com (since I write part of it, and while implementing test-restore I
realized it was pretty much same thing as implementing log shipping), but lots of people here also
just use their own jobs (I'm, pretty certain some has been posted here).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:uayUtD8zFHA.1168@.TK2MSFTNGP10.phx.gbl...
> How do you implement log shipping if you don't use xp_sqlmaint (as
> that's what the DB Maintenance Wizard uses when it creates all the
> SQLAgent jobs)? Have you implemented your own home-grown log shipping
> solution?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Tibor Karaszi wrote:
>>I don't use xp_sqlmaint for log shipping. But I'd be very surprise if it would pick up out-of-band
>>backups. My point was that out-of-band *database* backup doesn't disrupt log shipping. Out-of-band
>>log backups does. I was under the impression that the OP was asking about database backup, so
>>thanks
>>for pushing the point, Mike. :-)
>>
>

Tuesday, March 27, 2012

Backing up secondary DB in log shipping ...

Backing up secondary DB in log shipping ...

Backing up secondary DB in log shipping ...

Backing up online log after loss of primary data file

Sunday, March 25, 2012

Backing up log when recovery set to Simple

Backing up log files

Backing up log file with a script setup as a job

Backing Up Issue

Backing Up Issue

Backing Up Issue

Backing up databases in a mirror configuration

Backing up databases in a mirror configuration

Backing up Database

Thursday, March 22, 2012

Backing up and Truncating a transaction log in SQL

Backing up a restoring or standby database

Backing up a primary log shipping database

Backing up a primary log shipping database

Backing up a primary log shipping database

Autonumber field

Blog Archive

About Me