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.
========================================
==============
--[vbcol=seagreen]
<georgedschneider@.news.postalias>[vbcol=seagreen]
the[vbcol=seagreen]
manaully[vbcol=seagreen]|||The other post was for an MSDE database on as erver. This post isfor one o
f
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.
> ========================================
==============
>
> --
> <georgedschneider@.news.postalias>
> the
> manaully
>|||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 t
he
> 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 manaul
ly
> 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:
> 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 th
e
> 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/d...n-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/d...n-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.
========================================
==============
--[vbcol=seagreen]
<georgedschneider@.news.postalias>[vbcol=seagreen]
<45002CE9.5010006@.realsqlguy.com>[vbcol=seagreen]
recovery[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
remove the[vbcol=seagreen]
log[vbcol=seagreen]
manaully[vbcol=seagreen]
messages.[vbcol=seagreen]|||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:
> <[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a[/ur
l]
> 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/d...y/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.
> ========================================
==============
>
> --
> <georgedschneider@.news.postalias>
> <45002CE9.5010006@.realsqlguy.com>
> recovery
> the
> the
> remove the
> log
> manaully
> messages.
>|||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 d
o
> 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.
========================================
==============
--[vbcol=seagreen]
<45002CE9.5010006@.realsqlguy.com>
<784A7375-55CC-442D-8E6B-F86A5DE784DD@.microsoft.com>
<$tCEyDv0GHA.5212@.TK2MSFTNGXA01.phx.gbl>
<BFF0BF45-3B81-4813-98CD-EBCF3828C52D@.microsoft.com>[vbcol=seagreen]
purpose do[vbcol=seagreen]