Backup Plan advice / suggestion

Hello guys,
I would like to know if someone can advice me on that.
I have A MyDB more system DB (master, msdb, model, tempdb)
I would like to set up a Backup plan.
I would like to:
* Backup daily MyDB and system DB
* Backup LOG MyDB every one hours
* Backup LOG System DB (every 4 hours).
Any suggestion how to plan the better schedule time? 22h? 23h for
backup?
InaSee responses in-line...
ina wrote:
> Hello guys,
> I would like to know if someone can advice me on that.
> I have A MyDB more system DB (master, msdb, model, tempdb)
> I would like to set up a Backup plan.
> I would like to:
> * Backup daily MyDB and system DB
No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> * Backup LOG MyDB every one hours
This depends on your needs and tolerance for risk. You could
*potentially* lose an hour's worth of data, is that an acceptable risk
for you?
> * Backup LOG System DB (every 4 hours).
The only system DB that you can perform log backups on is MSDB, and this
is probably unnecessary.
> Any suggestion how to plan the better schedule time? 22h? 23h for
> backup?
> Ina
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
> No need to backup TEMPDB or MODEL, unless you have modified MODEL.
What is model crashes? If you have a backup, you just restore that backup. If not, you have to
rebuild the system databases (or do something unsupported like grabbing the files from another
system and worry about collations, if it works etc). This is why I always include database backup of
model in my backups schedules.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45264804.8030302@.realsqlguy.com...
> See responses in-line...
>
> ina wrote:
>> Hello guys,
>> I would like to know if someone can advice me on that.
>> I have A MyDB more system DB (master, msdb, model, tempdb)
>> I would like to set up a Backup plan.
>> I would like to:
>> * Backup daily MyDB and system DB
> No need to backup TEMPDB or MODEL, unless you have modified MODEL.
>> * Backup LOG MyDB every one hours
> This depends on your needs and tolerance for risk. You could *potentially* lose an hour's worth
> of data, is that an acceptable risk for you?
>> * Backup LOG System DB (every 4 hours).
> The only system DB that you can perform log backups on is MSDB, and this is probably unnecessary.
>> Any suggestion how to plan the better schedule time? 22h? 23h for
>> backup?
>> Ina
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thank you.
what I am doing now it is the full backup for MyDB at 11 PM and log
files this MyDB every hours between 2 AM until 10 PM
Is it fine?
Ina
Tibor Karaszi wrote:
> Hi Tracy,
> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> What is model crashes? If you have a backup, you just restore that backup. If not, you have to
> rebuild the system databases (or do something unsupported like grabbing the files from another
> system and worry about collations, if it works etc). This is why I always include database backup of
> model in my backups schedules.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45264804.8030302@.realsqlguy.com...
> > See responses in-line...
> >
> >
> > ina wrote:
> >> Hello guys,
> >>
> >> I would like to know if someone can advice me on that.
> >>
> >> I have A MyDB more system DB (master, msdb, model, tempdb)
> >> I would like to set up a Backup plan.
> >> I would like to:
> >> * Backup daily MyDB and system DB
> >
> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> >
> >> * Backup LOG MyDB every one hours
> >
> > This depends on your needs and tolerance for risk. You could *potentially* lose an hour's worth
> > of data, is that an acceptable risk for you?
> >
> >> * Backup LOG System DB (every 4 hours).
> >
> > The only system DB that you can perform log backups on is MSDB, and this is probably unnecessary.
> >
> >>
> >> Any suggestion how to plan the better schedule time? 22h? 23h for
> >> backup?
> >>
> >> Ina
> >>
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com|||> what I am doing now it is the full backup for MyDB at 11 PM and log
> files this MyDB every hours between 2 AM until 10 PM
I assume you mean "transaction log backup every hour between 2AB and 10PM".
> Is it fine?
We cannot answer that question. You have to determine that max amount of data loss you accept in
case of some catastrophe and based on that determine what types of backup and frequency. Also, you
didn't mention what backup you do if the system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1160222216.686213.118770@.e3g2000cwe.googlegroups.com...
> Thank you.
> what I am doing now it is the full backup for MyDB at 11 PM and log
> files this MyDB every hours between 2 AM until 10 PM
> Is it fine?
> Ina
> Tibor Karaszi wrote:
>> Hi Tracy,
>> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
>> What is model crashes? If you have a backup, you just restore that backup. If not, you have to
>> rebuild the system databases (or do something unsupported like grabbing the files from another
>> system and worry about collations, if it works etc). This is why I always include database backup
>> of
>> model in my backups schedules.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45264804.8030302@.realsqlguy.com...
>> > See responses in-line...
>> >
>> >
>> > ina wrote:
>> >> Hello guys,
>> >>
>> >> I would like to know if someone can advice me on that.
>> >>
>> >> I have A MyDB more system DB (master, msdb, model, tempdb)
>> >> I would like to set up a Backup plan.
>> >> I would like to:
>> >> * Backup daily MyDB and system DB
>> >
>> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
>> >
>> >> * Backup LOG MyDB every one hours
>> >
>> > This depends on your needs and tolerance for risk. You could *potentially* lose an hour's
>> > worth
>> > of data, is that an acceptable risk for you?
>> >
>> >> * Backup LOG System DB (every 4 hours).
>> >
>> > The only system DB that you can perform log backups on is MSDB, and this is probably
>> > unnecessary.
>> >
>> >>
>> >> Any suggestion how to plan the better schedule time? 22h? 23h for
>> >> backup?
>> >>
>> >> Ina
>> >>
>> >
>> >
>> > --
>> > Tracy McKibben
>> > MCDBA
>> > http://www.realsqlguy.com
>|||Thanks Tibor,
My DB is organize like this everyday I backup Master, Model and Msdb
and every hours a back up the log of Mdsb
For my MyDB is the same backup everyday and backup log every hours.
One question when I change the schedule of the backup (i.e MyDB log
backup) do you thing is better to change backup file or device or it
is enough to change the schedule?
Ina
Tibor Karaszi wrote:
> > what I am doing now it is the full backup for MyDB at 11 PM and log
> > files this MyDB every hours between 2 AM until 10 PM
> I assume you mean "transaction log backup every hour between 2AB and 10PM".
>
> > Is it fine?
> We cannot answer that question. You have to determine that max amount of data loss you accept in
> case of some catastrophe and based on that determine what types of backup and frequency. Also, you
> didn't mention what backup you do if the system databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ina" <roberta.inalbon@.gmail.com> wrote in message
> news:1160222216.686213.118770@.e3g2000cwe.googlegroups.com...
> > Thank you.
> >
> > what I am doing now it is the full backup for MyDB at 11 PM and log
> > files this MyDB every hours between 2 AM until 10 PM
> >
> > Is it fine?
> >
> > Ina
> > Tibor Karaszi wrote:
> >> Hi Tracy,
> >>
> >> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> >>
> >> What is model crashes? If you have a backup, you just restore that backup. If not, you have to
> >> rebuild the system databases (or do something unsupported like grabbing the files from another
> >> system and worry about collations, if it works etc). This is why I always include database backup
> >> of
> >> model in my backups schedules.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:45264804.8030302@.realsqlguy.com...
> >> > See responses in-line...
> >> >
> >> >
> >> > ina wrote:
> >> >> Hello guys,
> >> >>
> >> >> I would like to know if someone can advice me on that.
> >> >>
> >> >> I have A MyDB more system DB (master, msdb, model, tempdb)
> >> >> I would like to set up a Backup plan.
> >> >> I would like to:
> >> >> * Backup daily MyDB and system DB
> >> >
> >> > No need to backup TEMPDB or MODEL, unless you have modified MODEL.
> >> >
> >> >> * Backup LOG MyDB every one hours
> >> >
> >> > This depends on your needs and tolerance for risk. You could *potentially* lose an hour's
> >> > worth
> >> > of data, is that an acceptable risk for you?
> >> >
> >> >> * Backup LOG System DB (every 4 hours).
> >> >
> >> > The only system DB that you can perform log backups on is MSDB, and this is probably
> >> > unnecessary.
> >> >
> >> >>
> >> >> Any suggestion how to plan the better schedule time? 22h? 23h for
> >> >> backup?
> >> >>
> >> >> Ina
> >> >>
> >> >
> >> >
> >> > --
> >> > Tracy McKibben
> >> > MCDBA
> >> > http://www.realsqlguy.com
> >