Database Maintenance Plan

I normally steer clear of the EM wizards, but in this case I used it to crea
te a maintenance plan.
We were looking to do a nightly COMPLETE BACKUP - thus getting a .BAK file t
hat we could restore from in the event of programming disaster, etc.
I decided to say yes to the TRANSACTION LOG backup, but I'm not sure why. I
t defaulted to 12:00 am, as opposed to 2:00 am, what good is that. The COMP
LETE BACKUP has mdf/ldf in it - right?
The customer isn't looking for point-in-time recovery with this plan - just
a simple nightly backup in case of...
Opinions and suggestions would be greatly appeciated - thanks.
Here is the plan it created:
DATABASES
Health
SERVERS
(local)
COMPLETE BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, Satu
rday, at 2:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 4 Week(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
TRANSACTION LOG BACKUP
Occurs every 1 week(s) on Monday, Tuesday, Wednesday, Thursday, Friday, Satu
rday, at 12:00:00 AM.
Backup media: Disk
Store backup files in the default SQL Server Backup directory.
Delete backup files which are older than 4 Week(s).
Verify the backup after completion.
Create a subdirectory for each database, to store the backup files.
Operator 'sqlops' will be e-mailed.Steve,
If you really don't need log backup, then just set the recovery mode to
simple and don't do log backups!
However, log backups is IMO a good thing to have as it gives a number of
good things (point in time recovery, ability to do a log backup if the data
part of the db crashes etc). If you decide to do log backups, I suggest
every hour (or so).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:5856B024-0342-4D26-9715-3123C1A224EC@.microsoft.com...
> I normally steer clear of the EM wizards, but in this case I used it to
create a maintenance plan.
> We were looking to do a nightly COMPLETE BACKUP - thus getting a .BAK file
that we could restore from in the event of programming disaster, etc.
> I decided to say yes to the TRANSACTION LOG backup, but I'm not sure why.
It defaulted to 12:00 am, as opposed to 2:00 am, what good is that. The
COMPLETE BACKUP has mdf/ldf in it - right?
> The customer isn't looking for point-in-time recovery with this plan -
just a simple nightly backup in case of...
> Opinions and suggestions would be greatly appeciated - thanks.
> Here is the plan it created:
> DATABASES
> Health
> SERVERS
> (local)
> COMPLETE BACKUP
> Occurs every 1 week(s) on Monday, Tuesday, Wednesday,
Thursday, Friday, Saturday, at 2:00:00 AM.
> Backup media: Disk
> Store backup files in the default SQL Server Backup directory.
> Delete backup files which are older than 4 Week(s).
> Verify the backup after completion.
> Create a subdirectory for each database, to store the backup
files.
> TRANSACTION LOG BACKUP
> Occurs every 1 week(s) on Monday, Tuesday, Wednesday,
Thursday, Friday, Saturday, at 12:00:00 AM.
> Backup media: Disk
> Store backup files in the default SQL Server Backup directory.
> Delete backup files which are older than 4 Week(s).
> Verify the backup after completion.
> Create a subdirectory for each database, to store the backup
files.
> Operator 'sqlops' will be e-mailed.
>|||Thanks...
To clarify, though, the .BAK file created every night in this plan is a full
y restorable DATA and LOG file from that moment - right?
I like the idea of the hourly transaction log backup - does that mean that I
still keep with this one maintenance plan, but have the TRANSACTION LOG par
t run hourly?
Steve
-- Tibor Karaszi wrote: --
Steve,
If you really don't need log backup, then just set the recovery mode to
simple and don't do log backups!
However, log backups is IMO a good thing to have as it gives a number of
good things (point in time recovery, ability to do a log backup if the data
part of the db crashes etc). If you decide to do log backups, I suggest
every hour (or so).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:5856B024-0342-4D26-9715-3123C1A224EC@.microsoft.com...
> I normally steer clear of the EM wizards, but in this case I used it to
create a maintenance plan.
that we could restore from in the event of programming disaster, etc.
It defaulted to 12:00 am, as opposed to 2:00 am, what good is that. The
COMPLETE BACKUP has mdf/ldf in it - right?
just a simple nightly backup in case of...
> Health
> (local)
> Occurs every 1 week(s) on Monday, Tuesday, Wednesday,
Thursday, Friday, Saturday, at 2:00:00 AM.
> Backup media: Disk
> Store backup files in the default SQL Server Backup directory.
> Delete backup files which are older than 4 Week(s).
> Verify the backup after completion.
> Create a subdirectory for each database, to store the backup
files.
> Occurs every 1 week(s) on Monday, Tuesday, Wednesday,
Thursday, Friday, Saturday, at 12:00:00 AM.
> Backup media: Disk
> Store backup files in the default SQL Server Backup directory.
> Delete backup files which are older than 4 Week(s).
> Verify the backup after completion.
> Create a subdirectory for each database, to store the backup
files.|||transaction log backup has nothing to do with the
database backup (except the first time). keep both.
>--Original Message--
>Thanks...
>To clarify, though, the .BAK file created every night in
this plan is a fully restorable DATA and LOG file from
that moment - right?
>I like the idea of the hourly transaction log backup -
does that mean that I still keep with this one
maintenance plan, but have the TRANSACTION LOG part run
hourly?
>Steve
> -- Tibor Karaszi wrote: --
> Steve,
> If you really don't need log backup, then just set
the recovery mode to
> simple and don't do log backups!
> However, log backups is IMO a good thing to have as
it gives a number of
> good things (point in time recovery, ability to do
a log backup if the data
> part of the db crashes etc). If you decide to do
log backups, I suggest
> every hour (or so).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Steve Z" <szlamany@.antarescomputing.com> wrote in
message
> news:5856B024-0342-4D26-9715-
3123C1A224EC@.microsoft.com...
this case I used it to
> create a maintenance plan.
thus getting a .BAK file
> that we could restore from in the event of
programming disaster, etc.
backup, but I'm not sure why.
> It defaulted to 12:00 am, as opposed to 2:00 am,
what good is that. The
> COMPLETE BACKUP has mdf/ldf in it - right?
recovery with this plan -
> just a simple nightly backup in case of...
appeciated - thanks.
Tuesday, Wednesday,
> Thursday, Friday, Saturday, at 2:00:00 AM.
Server Backup directory.
than 4 Week(s).
database, to store the backup
> files.
Tuesday, Wednesday,
> Thursday, Friday, Saturday, at 12:00:00 AM.
Server Backup directory.
than 4 Week(s).
database, to store the backup
> files.
>.
>|||Don't confuse the mdf and ldf files with database and log backups. A
database backup is a full backup of the database. When you restore such, SQL
Server will create both the mdf and ldf file(s) for you.
Yes, if you want to do log backup, you can do it using the maint plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:769A4790-39ED-41A6-A527-AB6955D094DC@.microsoft.com...
> Thanks...
> To clarify, though, the .BAK file created every night in this plan is a
fully restorable DATA and LOG file from that moment - right?
> I like the idea of the hourly transaction log backup - does that mean that
I still keep with this one maintenance plan, but have the TRANSACTION LOG
part run hourly?
> Steve
> -- Tibor Karaszi wrote: --
> Steve,
> If you really don't need log backup, then just set the recovery mode
to
> simple and don't do log backups!
> However, log backups is IMO a good thing to have as it gives a number
of
> good things (point in time recovery, ability to do a log backup if
the data
> part of the db crashes etc). If you decide to do log backups, I
suggest
> every hour (or so).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Steve Z" <szlamany@.antarescomputing.com> wrote in message
> news:5856B024-0342-4D26-9715-3123C1A224EC@.microsoft.com...
it to
> create a maintenance plan.
.BAK file
> that we could restore from in the event of programming disaster, etc.
sure why.
> It defaulted to 12:00 am, as opposed to 2:00 am, what good is that.
The
> COMPLETE BACKUP has mdf/ldf in it - right?
plan -
> just a simple nightly backup in case of...
> Thursday, Friday, Saturday, at 2:00:00 AM.
directory.
backup
> files.
> Thursday, Friday, Saturday, at 12:00:00 AM.
directory.
backup
> files.|||Hi Steve,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if you still have some question about it besides
our MVP's information. As Tibor said, system will baks up the original
database files and records their locations. It contains:
1)schema and file structure
2) data
3) portion or the transaction log files. the portion of the transaction log
that is backed up contains database activities since the start of the
backup process
The Restore process will use this information to re-create all the files in
their original loacations, complete with objects and data.
For any more question, please post your message here and we are glad to
help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.