Backup Plan

I have been using enterprise manager to run backups. I backup up all the
db's system and user under one maitenance plan. I have it set to remove the
bakups after 7 days. The backups are removed but not the tranaction log
backups. Why is this and how can I resolve this so I don't have to manaully
delete delete the tranaction log backups.Hi Geroge,
Thanks for posting.
I noticed that you posted another post abouit MSDE backup in newsgroup.
From that post, I noticed that you are using simple recovery mode. If so,
there will be no transaction log be backed up.
Regarding backup plan, please refer to:
<http://msdn2.microsoft.com/en-us/library/ms191239.aspx>
Hope this helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Backup Plan
>>thread-index: AcbR8vM/1aiy35xvSLanR5zrbdipVg==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>Subject: Backup Plan
>>Date: Wed, 6 Sep 2006 13:28:02 -0700
>>Lines: 5
>>Message-ID: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444422
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>I have been using enterprise manager to run backups. I backup up all the
>>db's system and user under one maitenance plan. I have it set to remove
the
>>bakups after 7 days. The backups are removed but not the tranaction log
>>backups. Why is this and how can I resolve this so I don't have to
manaully
>>delete delete the tranaction log backups.|||The other post was for an MSDE database on as erver. This post isfor one of
my SQL 2000 Servers using Enterprise manager.
"Vincent Xu [MSFT]" wrote:
> Hi Geroge,
> Thanks for posting.
> I noticed that you posted another post abouit MSDE backup in newsgroup.
> From that post, I noticed that you are using simple recovery mode. If so,
> there will be no transaction log be backed up.
> Regarding backup plan, please refer to:
> <http://msdn2.microsoft.com/en-us/library/ms191239.aspx>
> Hope this helps.
>
> Best regards,
> Vincent Xu
> Microsoft Online Partner Support
> ======================================================> Get Secure! - www.microsoft.com/security
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties,and confers no rights.
> ======================================================>
> --
> >>Thread-Topic: Backup Plan
> >>thread-index: AcbR8vM/1aiy35xvSLanR5zrbdipVg==> >>X-WBNR-Posting-Host: 209.244.152.162
> >>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=> <georgedschneider@.news.postalias>
> >>Subject: Backup Plan
> >>Date: Wed, 6 Sep 2006 13:28:02 -0700
> >>Lines: 5
> >>Message-ID: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="Utf-8"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>Content-Class: urn:content-classes:message
> >>Importance: normal
> >>Priority: normal
> >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >>Newsgroups: microsoft.public.sqlserver.server
> >>Path: TK2MSFTNGXA01.phx.gbl
> >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444422
> >>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >>X-Tomcat-NG: microsoft.public.sqlserver.server
> >>
> >>I have been using enterprise manager to run backups. I backup up all the
> >>db's system and user under one maitenance plan. I have it set to remove
> the
> >>bakups after 7 days. The backups are removed but not the tranaction log
> >>backups. Why is this and how can I resolve this so I don't have to
> manaully
> >>delete delete the tranaction log backups.
> >>
>|||George Schneider wrote:
> I have been using enterprise manager to run backups. I backup up all the
> db's system and user under one maitenance plan. I have it set to remove the
> bakups after 7 days. The backups are removed but not the tranaction log
> backups. Why is this and how can I resolve this so I don't have to manaully
> delete delete the tranaction log backups.
You say you're backing up ALL databases within a single maintenance
plan? Are any of these databases in Simple recovery mode? If so, then
transaction log backups are not possible for those DB's, and that is
likely causing the transaction log part of your maintenance plan to
fail, preventing it from running the deletion step.
Check the job history and the maintenance plan history for error messages.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||how would I change the recovery mode and what should I change the recovery
mode to if I wanted to back up the transaction logs. Wouldn't one need the
transaction logs in a disaster recovery scenario.
"Tracy McKibben" wrote:
> George Schneider wrote:
> > I have been using enterprise manager to run backups. I backup up all the
> > db's system and user under one maitenance plan. I have it set to remove the
> > bakups after 7 days. The backups are removed but not the tranaction log
> > backups. Why is this and how can I resolve this so I don't have to manaully
> > delete delete the tranaction log backups.
> You say you're backing up ALL databases within a single maintenance
> plan? Are any of these databases in Simple recovery mode? If so, then
> transaction log backups are not possible for those DB's, and that is
> likely causing the transaction log part of your maintenance plan to
> fail, preventing it from running the deletion step.
> Check the job history and the maintenance plan history for error messages.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||George Schneider wrote:
> how would I change the recovery mode and what should I change the recovery
> mode to if I wanted to back up the transaction logs. Wouldn't one need the
> transaction logs in a disaster recovery scenario.
>
Refer to Books Online for more info on recovery models. There's not a
blanket answer to your question, you'll have to decide what is
appropriate for your needs.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Geroge,
Thanks for your reply.
To switch recovery mode, please refer to following article:
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_8ik3.asp>
Actually, MSDE & SQL 2000 is the same by the nature. Therefore, you can
refer to my reply in the "MSDE Backup " Thread. For your convenience, I
paste it here
==================If you perform full backup regularly, you can just restore the latest
backup when disaster occurs.
The transaction log is a serial record of all the transactions that have
been performed against the database since the transaction log was last
backed up. With transaction log backups, you can recover the database to a
specific point in time (for example, prior to entering unwanted data), or
to the point of failure. Check following article:
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_565v.asp>
How to: Create a Transaction Log Backup (Transact-SQL)
<http://msdn2.microsoft.com/en-us/library/ms191284.aspx>
How to: Restore to the Point of Failure (Transact-SQL)
<http://msdn2.microsoft.com/en-us/library/ms175093.aspx>
How to: Restore to a Point in Time (Transact-SQL)
<http://msdn2.microsoft.com/en-us/library/ms179451.aspx>
==================
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Backup Plan
>>thread-index: AcbSsnohDScBOuYQRe6ilXpgrPC+Dg==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>References: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
<45002CE9.5010006@.realsqlguy.com>
>>Subject: Re: Backup Plan
>>Date: Thu, 7 Sep 2006 12:19:02 -0700
>>Lines: 27
>>Message-ID: <784A7375-55CC-442D-8E6B-F86A5DE784DD@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444545
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>how would I change the recovery mode and what should I change the
recovery
>>mode to if I wanted to back up the transaction logs. Wouldn't one need
the
>>transaction logs in a disaster recovery scenario.
>>"Tracy McKibben" wrote:
>> George Schneider wrote:
>> > I have been using enterprise manager to run backups. I backup up all
the
>> > db's system and user under one maitenance plan. I have it set to
remove the
>> > bakups after 7 days. The backups are removed but not the tranaction
log
>> > backups. Why is this and how can I resolve this so I don't have to
manaully
>> > delete delete the tranaction log backups.
>> You say you're backing up ALL databases within a single maintenance
>> plan? Are any of these databases in Simple recovery mode? If so, then
>> transaction log backups are not possible for those DB's, and that is
>> likely causing the transaction log part of your maintenance plan to
>> fail, preventing it from running the deletion step.
>> Check the job history and the maintenance plan history for error
messages.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>|||I guess I'm a little confussed here. I thought transaction logs were
required to restore to make the db full operational again. What purpose do
transaction logs perform then if you do not have to restore then in a
disaster sceanrio?
"Vincent Xu [MSFT]" wrote:
> Hi Geroge,
> Thanks for your reply.
> To switch recovery mode, please refer to following article:
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_8ik3.asp>
> Actually, MSDE & SQL 2000 is the same by the nature. Therefore, you can
> refer to my reply in the "MSDE Backup " Thread. For your convenience, I
> paste it here
> ==================> If you perform full backup regularly, you can just restore the latest
> backup when disaster occurs.
> The transaction log is a serial record of all the transactions that have
> been performed against the database since the transaction log was last
> backed up. With transaction log backups, you can recover the database to a
> specific point in time (for example, prior to entering unwanted data), or
> to the point of failure. Check following article:
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_565v.asp>
> How to: Create a Transaction Log Backup (Transact-SQL)
> <http://msdn2.microsoft.com/en-us/library/ms191284.aspx>
> How to: Restore to the Point of Failure (Transact-SQL)
> <http://msdn2.microsoft.com/en-us/library/ms175093.aspx>
> How to: Restore to a Point in Time (Transact-SQL)
> <http://msdn2.microsoft.com/en-us/library/ms179451.aspx>
> ==================> Best regards,
> Vincent Xu
> Microsoft Online Partner Support
> ======================================================> Get Secure! - www.microsoft.com/security
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties,and confers no rights.
> ======================================================>
> --
> >>Thread-Topic: Backup Plan
> >>thread-index: AcbSsnohDScBOuYQRe6ilXpgrPC+Dg==> >>X-WBNR-Posting-Host: 209.244.152.162
> >>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=> <georgedschneider@.news.postalias>
> >>References: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
> <45002CE9.5010006@.realsqlguy.com>
> >>Subject: Re: Backup Plan
> >>Date: Thu, 7 Sep 2006 12:19:02 -0700
> >>Lines: 27
> >>Message-ID: <784A7375-55CC-442D-8E6B-F86A5DE784DD@.microsoft.com>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="Utf-8"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>Content-Class: urn:content-classes:message
> >>Importance: normal
> >>Priority: normal
> >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >>Newsgroups: microsoft.public.sqlserver.server
> >>Path: TK2MSFTNGXA01.phx.gbl
> >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444545
> >>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >>X-Tomcat-NG: microsoft.public.sqlserver.server
> >>
> >>how would I change the recovery mode and what should I change the
> recovery
> >>mode to if I wanted to back up the transaction logs. Wouldn't one need
> the
> >>transaction logs in a disaster recovery scenario.
> >>
> >>"Tracy McKibben" wrote:
> >>
> >> George Schneider wrote:
> >> > I have been using enterprise manager to run backups. I backup up all
> the
> >> > db's system and user under one maitenance plan. I have it set to
> remove the
> >> > bakups after 7 days. The backups are removed but not the tranaction
> log
> >> > backups. Why is this and how can I resolve this so I don't have to
> manaully
> >> > delete delete the tranaction log backups.
> >>
> >> You say you're backing up ALL databases within a single maintenance
> >> plan? Are any of these databases in Simple recovery mode? If so, then
> >> transaction log backups are not possible for those DB's, and that is
> >> likely causing the transaction log part of your maintenance plan to
> >> fail, preventing it from running the deletion step.
> >>
> >> Check the job history and the maintenance plan history for error
> messages.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com
> >>
> >>
>|||George Schneider wrote:
> I guess I'm a little confussed here. I thought transaction logs were
> required to restore to make the db full operational again. What purpose do
> transaction logs perform then if you do not have to restore then in a
> disaster sceanrio?
>
That all depends on the recovery model that you're using. The
transaction log records everything that happens in the database. Let's
say you delete a million rows from a table, and partway through that
delete operation, it fails. The delete will be rolled back, none of the
records will be deleted. In order to perform that rollback, SQL has to
record somewhere what records it's deleting - that's what the
transaction log is used for. The transaction log will need to be large
enough to hold this entire transaction. If the delete is successful,
the transaction is committed, and the events recorded in the transaction
log are flagged as committed.
In SIMPLE mode, SQL will issue periodic "checkpoint" instructions that
will flush out those committed events, and the space that was previously
used for them will be available for future transactions to use. The
transaction log will not shrink at this point, it will remain the same
physical size. Once the committed events have been flushed, they are
permanently written to the database, and the only way to "undo" them is
to restore from a full backup taken prior to the delete, probably the
backup from the night before.
In FULL or BULK-LOGGED modes, these committed events are not
automatically flushed out of the transaction log. They are flushed when
you issue a BACKUP LOG command. This gives you the ability to recover
the database to virtually any point in time. For sake of example, let's
assume you're taking transaction log backups every 5 minutes. You
delete the million rows, and an hour later, decide that you need those
rows back. You simply restore the previous full backup, and then
restore each transaction log backup up to the point where you issued the
delete. You lose an hour's worth of work instead of a full day.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi All,
Tracy provided great information. Well done!.
George, if youi still have anything unclear, please feel free to reply.
I'll try my best to be of assistance.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Message-ID: <4501C028.9030606@.realsqlguy.com>
>>Date: Fri, 08 Sep 2006 14:10:32 -0500
>>From: Tracy McKibben <tracy@.realsqlguy.com>
>>User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
>>MIME-Version: 1.0
>>Subject: Re: Backup Plan
>>References: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
<45002CE9.5010006@.realsqlguy.com>
<784A7375-55CC-442D-8E6B-F86A5DE784DD@.microsoft.com>
<$tCEyDv0GHA.5212@.TK2MSFTNGXA01.phx.gbl>
<BFF0BF45-3B81-4813-98CD-EBCF3828C52D@.microsoft.com>
>>In-Reply-To: <BFF0BF45-3B81-4813-98CD-EBCF3828C52D@.microsoft.com>
>>Content-Type: text/plain; charset=UTF-8; format=flowed
>>Content-Transfer-Encoding: 7bit
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: ncsgate.ncspearson.com 12.106.111.10
>>Lines: 1
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444677
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>George Schneider wrote:
>> I guess I'm a little confussed here. I thought transaction logs were
>> required to restore to make the db full operational again. What
purpose do
>> transaction logs perform then if you do not have to restore then in a
>> disaster sceanrio?
>>
>>That all depends on the recovery model that you're using. The
>>transaction log records everything that happens in the database. Let's
>>say you delete a million rows from a table, and partway through that
>>delete operation, it fails. The delete will be rolled back, none of the
>>records will be deleted. In order to perform that rollback, SQL has to
>>record somewhere what records it's deleting - that's what the
>>transaction log is used for. The transaction log will need to be large
>>enough to hold this entire transaction. If the delete is successful,
>>the transaction is committed, and the events recorded in the transaction
>>log are flagged as committed.
>>In SIMPLE mode, SQL will issue periodic "checkpoint" instructions that
>>will flush out those committed events, and the space that was previously
>>used for them will be available for future transactions to use. The
>>transaction log will not shrink at this point, it will remain the same
>>physical size. Once the committed events have been flushed, they are
>>permanently written to the database, and the only way to "undo" them is
>>to restore from a full backup taken prior to the delete, probably the
>>backup from the night before.
>>In FULL or BULK-LOGGED modes, these committed events are not
>>automatically flushed out of the transaction log. They are flushed when
>>you issue a BACKUP LOG command. This gives you the ability to recover
>>the database to virtually any point in time. For sake of example, let's
>>assume you're taking transaction log backups every 5 minutes. You
>>delete the million rows, and an hour later, decide that you need those
>>rows back. You simply restore the previous full backup, and then
>>restore each transaction log backup up to the point where you issued the
>>delete. You lose an hour's worth of work instead of a full day.
>>
>>--
>>Tracy McKibben
>>MCDBA
>>http://www.realsqlguy.com|||If I understand you correctly then if I'm in Simple recovery mode for my
databases then the maitenance plan that I'm using to include tranaction logs
is not needed. Can you explain what is happening in simple recovery mode to
why tranaction log backups are not needed.
"Tracy McKibben" wrote:
> George Schneider wrote:
> > I guess I'm a little confussed here. I thought transaction logs were
> > required to restore to make the db full operational again. What purpose do
> > transaction logs perform then if you do not have to restore then in a
> > disaster sceanrio?
> >
> That all depends on the recovery model that you're using. The
> transaction log records everything that happens in the database. Let's
> say you delete a million rows from a table, and partway through that
> delete operation, it fails. The delete will be rolled back, none of the
> records will be deleted. In order to perform that rollback, SQL has to
> record somewhere what records it's deleting - that's what the
> transaction log is used for. The transaction log will need to be large
> enough to hold this entire transaction. If the delete is successful,
> the transaction is committed, and the events recorded in the transaction
> log are flagged as committed.
> In SIMPLE mode, SQL will issue periodic "checkpoint" instructions that
> will flush out those committed events, and the space that was previously
> used for them will be available for future transactions to use. The
> transaction log will not shrink at this point, it will remain the same
> physical size. Once the committed events have been flushed, they are
> permanently written to the database, and the only way to "undo" them is
> to restore from a full backup taken prior to the delete, probably the
> backup from the night before.
> In FULL or BULK-LOGGED modes, these committed events are not
> automatically flushed out of the transaction log. They are flushed when
> you issue a BACKUP LOG command. This gives you the ability to recover
> the database to virtually any point in time. For sake of example, let's
> assume you're taking transaction log backups every 5 minutes. You
> delete the million rows, and an hour later, decide that you need those
> rows back. You simply restore the previous full backup, and then
> restore each transaction log backup up to the point where you issued the
> delete. You lose an hour's worth of work instead of a full day.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||George Schneider wrote:
> If I understand you correctly then if I'm in Simple recovery mode for my
> databases then the maitenance plan that I'm using to include tranaction logs
> is not needed. Can you explain what is happening in simple recovery mode to
> why tranaction log backups are not needed.
>
In Simple mode, committed transactions are automatically purged from the
log file by SQL's checkpointing process, so there's nothing in the log
for you to back up.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Correct me if I'm wrong on this one concerning recover modes. In sim[ple
recovery mode the tranactions are recorded in the log but as soon as they are
applied to the databse sql purges them from the log so in essece the log is
empty in this recovery mode.
If I'm running simple recovery then I should change the maitenace plan
backup configuration not to backup the tranaction logs correct?
Is it alright to have one maintenace plan to backup all the db's both system
and user?
"Tracy McKibben" wrote:
> George Schneider wrote:
> > If I understand you correctly then if I'm in Simple recovery mode for my
> > databases then the maitenance plan that I'm using to include tranaction logs
> > is not needed. Can you explain what is happening in simple recovery mode to
> > why tranaction log backups are not needed.
> >
> In Simple mode, committed transactions are automatically purged from the
> log file by SQL's checkpointing process, so there's nothing in the log
> for you to back up.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||George Schneider wrote:
> Correct me if I'm wrong on this one concerning recover modes. In sim[ple
> recovery mode the tranactions are recorded in the log but as soon as they are
> applied to the databse sql purges them from the log so in essece the log is
> empty in this recovery mode.
Correct
> If I'm running simple recovery then I should change the maitenace plan
> backup configuration not to backup the tranaction logs correct?
Correct. You cannot run a transaction log backup against a Simple mode
database. However, you should consider if the risk of data loss, and
decide if Simple mode is right for your needs.
> Is it alright to have one maintenace plan to backup all the db's both system
> and user?
Yes
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Now I'm back to square one. I thought the problem with not removing the
tranaction backups was becuase I was running backups on db's that were in
simple recovery mode. This is not the case. I have some db's in simple and
some in full recovery mode. The one's in simple recovery mode are fine and
do not have a tranaction log backup created. Its the four pr have db's that
are in full recovery mode. The tranaction logs backups are not being deleted
like the bak files from the backup directory. What can be causing this and
how cna I resolve this?
"Tracy McKibben" wrote:
> George Schneider wrote:
> > Correct me if I'm wrong on this one concerning recover modes. In sim[ple
> > recovery mode the tranactions are recorded in the log but as soon as they are
> > applied to the databse sql purges them from the log so in essece the log is
> > empty in this recovery mode.
> Correct
> > If I'm running simple recovery then I should change the maitenace plan
> > backup configuration not to backup the tranaction logs correct?
> Correct. You cannot run a transaction log backup against a Simple mode
> database. However, you should consider if the risk of data loss, and
> decide if Simple mode is right for your needs.
> > Is it alright to have one maintenace plan to backup all the db's both system
> > and user?
> Yes
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Don't use the same maint plans for the databases in full vs. simple mode. If you do, maint plan is a
little bit daft and doesn't remove old tlog backups for the ones in full mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:F2886839-6488-47B7-8AE6-4C0E2E7294D6@.microsoft.com...
> Now I'm back to square one. I thought the problem with not removing the
> tranaction backups was becuase I was running backups on db's that were in
> simple recovery mode. This is not the case. I have some db's in simple and
> some in full recovery mode. The one's in simple recovery mode are fine and
> do not have a tranaction log backup created. Its the four pr have db's that
> are in full recovery mode. The tranaction logs backups are not being deleted
> like the bak files from the backup directory. What can be causing this and
> how cna I resolve this?
> "Tracy McKibben" wrote:
>> George Schneider wrote:
>> > Correct me if I'm wrong on this one concerning recover modes. In sim[ple
>> > recovery mode the tranactions are recorded in the log but as soon as they are
>> > applied to the databse sql purges them from the log so in essece the log is
>> > empty in this recovery mode.
>> Correct
>> > If I'm running simple recovery then I should change the maitenace plan
>> > backup configuration not to backup the tranaction logs correct?
>> Correct. You cannot run a transaction log backup against a Simple mode
>> database. However, you should consider if the risk of data loss, and
>> decide if Simple mode is right for your needs.
>> > Is it alright to have one maintenace plan to backup all the db's both system
>> > and user?
>> Yes
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||George Schneider wrote:
> Now I'm back to square one. I thought the problem with not removing the
> tranaction backups was becuase I was running backups on db's that were in
> simple recovery mode. This is not the case. I have some db's in simple and
> some in full recovery mode. The one's in simple recovery mode are fine and
> do not have a tranaction log backup created. Its the four pr have db's that
> are in full recovery mode. The tranaction logs backups are not being deleted
> like the bak files from the backup directory. What can be causing this and
> how cna I resolve this?
>
The problem is that you're doing all of this from ONE maintenance plan,
and have told the maintenance plan to do transaction log backups of ALL
your databases. When it attempts to do that, it fails on the ones that
are in Simple mode, and never gets to the cleanup step where it deletes
the old files.
Create two seperate maintenance plans, one for the Simple mode
databases, one for the others. OR, better yet, dump the maintenance
plan altogether and use scripts to perform these tasks. Here is one:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com