Need advice on db maintenance plan

Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoon
What version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegr oups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>
|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon
|||Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>
|||Tibor Karaszi
Thanks.

>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon
|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.

> will the log backup block the full database
> backup ?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi
> Thanks.
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>
|||Thanks
JCVoon

Need advice on db maintenance plan

Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoonWhat version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegroups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon|||Prior to 2005, a database backup will block a log backup. So you can have yo
ur log backups scheduled
if you wish, the log backup job will just sit a wait until the database has
been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>|||Tibor Karaszi
Thanks.

>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.

> will the log backup block the full database
> backup ?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi
> Thanks.
>
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>|||Thanks
JCVoon