Backing up and ever growing database

Dear Sirs,
I have an sql 2000 ever growing databse, 24 hours a day 7 days a week,
what is the best to back it up. All the data is critical to be
preserved.
Any method would be apreciated.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Elie
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1 --administaiting
best practices
"elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
>
> Dear Sirs,
> I have an sql 2000 ever growing databse, 24 hours a day 7 days a week,
> what is the best to back it up. All the data is critical to be
> preserved.
> Any method would be apreciated.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi,
I suggest you to do below backup strategy:-
1. Sunday - Full database Backup
2. Every day night - Differential backup
3. Every 30 minutes or 1 hour Transaction log backup.
have a look into the details of above strategies:-
http://techrepublic.com.com/5100-6268-1043671-3.html
Thanks
Hari
MCDBA
"elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
>
> Dear Sirs,
> I have an sql 2000 ever growing databse, 24 hours a day 7 days a week,
> what is the best to back it up. All the data is critical to be
> preserved.
> Any method would be apreciated.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hari
Each Diff backup will be grown so on Saturday you will have a pretty big
file and look,if you want to restore database you will need to restore FULL
backup and then a bigger (last) DIFF backup which will consume more time
and resourse.
In my opinion he can do it every night FULL backup of the database and then
every 30 min or an hour LOG file.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23SfnEVrUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I suggest you to do below backup strategy:-
> 1. Sunday - Full database Backup
> 2. Every day night - Differential backup
> 3. Every 30 minutes or 1 hour Transaction log backup.
> have a look into the details of above strategies:-
> http://techrepublic.com.com/5100-6268-1043671-3.html
>
> --
> Thanks
> Hari
> MCDBA
> "elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
> news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
> >
> >
> > Dear Sirs,
> >
> > I have an sql 2000 ever growing databse, 24 hours a day 7 days a week,
> > what is the best to back it up. All the data is critical to be
> > preserved.
> >
> > Any method would be apreciated.
> >
> > *** Sent via Devdex http://www.devdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>|||Well
What is about restoring ?
I'd use DIFF backup if amount of time spent in recovering the database by
applying all the transaction logs is not acceptable.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OpHxsgsUEHA.3944@.TK2MSFTNGP12.phx.gbl...
> Hi Uri,
> I agree with you 90%, rest 10% I disagree because if the database size
grows
> above 50Gb, it is always
> advised to do a differential during week days. This will save your backup
> time as well as reduce the IO used for backup operations.
> Do you agree with me or :)
>
> --
> Thanks
> Hari
> MCDBA
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ukTnZprUEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > Hari
> > Each Diff backup will be grown so on Saturday you will have a pretty big
> > file and look,if you want to restore database you will need to restore
> FULL
> > backup and then a bigger (last) DIFF backup which will consume more
time
> > and resourse.
> > In my opinion he can do it every night FULL backup of the database and
> then
> > every 30 min or an hour LOG file.
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > news:%23SfnEVrUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I suggest you to do below backup strategy:-
> > >
> > > 1. Sunday - Full database Backup
> > > 2. Every day night - Differential backup
> > > 3. Every 30 minutes or 1 hour Transaction log backup.
> > >
> > > have a look into the details of above strategies:-
> > >
> > > http://techrepublic.com.com/5100-6268-1043671-3.html
> > >
> > >
> > > --
> > > Thanks
> > > Hari
> > > MCDBA
> > > "elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
> > > news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
> > > >
> > > >
> > > > Dear Sirs,
> > > >
> > > > I have an sql 2000 ever growing databse, 24 hours a day 7 days a
week,
> > > > what is the best to back it up. All the data is critical to be
> > > > preserved.
> > > >
> > > > Any method would be apreciated.
> > > >
> > > > *** Sent via Devdex http://www.devdex.com ***
> > > > Don't just participate in USENET...get rewarded for it!
> > >
> > >
> >
> >
>|||Hi,
OK I Agree, Both areas got it own demerits. But still if you have a huge
database (1 TB) the full database
backup is not a feasible solution because it will take a long time even in a
SAN .
Restore?
In case of Restore, I will restore the Differential backup taken last night
and start applying the Transaction log one by one till last file.
Please explain me if you have a better approach.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:urcN7nsUEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Well
> What is about restoring ?
> I'd use DIFF backup if amount of time spent in recovering the database by
> applying all the transaction logs is not acceptable.
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:OpHxsgsUEHA.3944@.TK2MSFTNGP12.phx.gbl...
> > Hi Uri,
> >
> > I agree with you 90%, rest 10% I disagree because if the database size
> grows
> > above 50Gb, it is always
> > advised to do a differential during week days. This will save your
backup
> > time as well as reduce the IO used for backup operations.
> >
> > Do you agree with me or :)
> >
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:ukTnZprUEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > > Hari
> > > Each Diff backup will be grown so on Saturday you will have a pretty
big
> > > file and look,if you want to restore database you will need to restore
> > FULL
> > > backup and then a bigger (last) DIFF backup which will consume more
> time
> > > and resourse.
> > > In my opinion he can do it every night FULL backup of the database and
> > then
> > > every 30 min or an hour LOG file.
> > >
> > >
> > > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > > news:%23SfnEVrUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > I suggest you to do below backup strategy:-
> > > >
> > > > 1. Sunday - Full database Backup
> > > > 2. Every day night - Differential backup
> > > > 3. Every 30 minutes or 1 hour Transaction log backup.
> > > >
> > > > have a look into the details of above strategies:-
> > > >
> > > > http://techrepublic.com.com/5100-6268-1043671-3.html
> > > >
> > > >
> > > > --
> > > > Thanks
> > > > Hari
> > > > MCDBA
> > > > "elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
> > > > news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
> > > > >
> > > > >
> > > > > Dear Sirs,
> > > > >
> > > > > I have an sql 2000 ever growing databse, 24 hours a day 7 days a
> week,
> > > > > what is the best to back it up. All the data is critical to be
> > > > > preserved.
> > > > >
> > > > > Any method would be apreciated.
> > > > >
> > > > > *** Sent via Devdex http://www.devdex.com ***
> > > > > Don't just participate in USENET...get rewarded for it!
> > > >
> > > >
> > >
> > >
> >
> >
>|||In addition to the other comments... Good data placement will be a
requirement for up to the minute recovery..
1. Keep log away from database data ( different physical drive)
2. Keep database data away from master( Different physical drive).
3. Mirror the log
Doing so with good backup practices ( including doing your dbcc checkdb)
will get you up-to the minute recovery under most conditions.
The risk area is the time between log backups... If the Server simply
explodes, what you have lost is the changes since the last log backup...
Doing log backups frequently reduce this potential ( and probably rare)
opportunity for data loss.
Neither Log Shipping nor clustering will improve your recoverability.
Clustering increases availability - nothing more.
Hope this helps...
But congratulations to you... You are doing the right things, and thinking
the right way... This forum is the perfect place to have such a
discussion...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
>
> Dear Sirs,
> I have an sql 2000 ever growing databse, 24 hours a day 7 days a week,
> what is the best to back it up. All the data is critical to be
> preserved.
> Any method would be apreciated.
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hari
> In case of Restore, I will restore the Differential backup taken last
night
> and start applying the Transaction log one by one till last file.
But do not forget restore FULL backup database before it (1T):)
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:umQy62sUEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> OK I Agree, Both areas got it own demerits. But still if you have a huge
> database (1 TB) the full database
> backup is not a feasible solution because it will take a long time even in
a
> SAN .
> Restore?
> In case of Restore, I will restore the Differential backup taken last
night
> and start applying the Transaction log one by one till last file.
> Please explain me if you have a better approach.
>
> --
> Thanks
> Hari
> MCDBA
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:urcN7nsUEHA.4064@.TK2MSFTNGP11.phx.gbl...
> > Well
> > What is about restoring ?
> > I'd use DIFF backup if amount of time spent in recovering the database
by
> > applying all the transaction logs is not acceptable.
> >
> > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > news:OpHxsgsUEHA.3944@.TK2MSFTNGP12.phx.gbl...
> > > Hi Uri,
> > >
> > > I agree with you 90%, rest 10% I disagree because if the database size
> > grows
> > > above 50Gb, it is always
> > > advised to do a differential during week days. This will save your
> backup
> > > time as well as reduce the IO used for backup operations.
> > >
> > > Do you agree with me or :)
> > >
> > >
> > > --
> > > Thanks
> > > Hari
> > > MCDBA
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:ukTnZprUEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > > > Hari
> > > > Each Diff backup will be grown so on Saturday you will have a pretty
> big
> > > > file and look,if you want to restore database you will need to
restore
> > > FULL
> > > > backup and then a bigger (last) DIFF backup which will consume more
> > time
> > > > and resourse.
> > > > In my opinion he can do it every night FULL backup of the database
and
> > > then
> > > > every 30 min or an hour LOG file.
> > > >
> > > >
> > > > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > > > news:%23SfnEVrUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > I suggest you to do below backup strategy:-
> > > > >
> > > > > 1. Sunday - Full database Backup
> > > > > 2. Every day night - Differential backup
> > > > > 3. Every 30 minutes or 1 hour Transaction log backup.
> > > > >
> > > > > have a look into the details of above strategies:-
> > > > >
> > > > > http://techrepublic.com.com/5100-6268-1043671-3.html
> > > > >
> > > > >
> > > > > --
> > > > > Thanks
> > > > > Hari
> > > > > MCDBA
> > > > > "elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
> > > > > news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
> > > > > >
> > > > > >
> > > > > > Dear Sirs,
> > > > > >
> > > > > > I have an sql 2000 ever growing databse, 24 hours a day 7 days a
> > week,
> > > > > > what is the best to back it up. All the data is critical to be
> > > > > > preserved.
> > > > > >
> > > > > > Any method would be apreciated.
> > > > > >
> > > > > > *** Sent via Devdex http://www.devdex.com ***
> > > > > > Don't just participate in USENET...get rewarded for it!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Uri,
I agree with you 90%, rest 10% I disagree because if the database size grows
above 50Gb, it is always
advised to do a differential during week days. This will save your backup
time as well as reduce the IO used for backup operations.
Do you agree with me or :)
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ukTnZprUEHA.1012@.TK2MSFTNGP09.phx.gbl...
> Hari
> Each Diff backup will be grown so on Saturday you will have a pretty big
> file and look,if you want to restore database you will need to restore
FULL
> backup and then a bigger (last) DIFF backup which will consume more time
> and resourse.
> In my opinion he can do it every night FULL backup of the database and
then
> every 30 min or an hour LOG file.
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23SfnEVrUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I suggest you to do below backup strategy:-
> >
> > 1. Sunday - Full database Backup
> > 2. Every day night - Differential backup
> > 3. Every 30 minutes or 1 hour Transaction log backup.
> >
> > have a look into the details of above strategies:-
> >
> > http://techrepublic.com.com/5100-6268-1043671-3.html
> >
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
> > news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
> > >
> > >
> > > Dear Sirs,
> > >
> > > I have an sql 2000 ever growing databse, 24 hours a day 7 days a week,
> > > what is the best to back it up. All the data is critical to be
> > > preserved.
> > >
> > > Any method would be apreciated.
> > >
> > > *** Sent via Devdex http://www.devdex.com ***
> > > Don't just participate in USENET...get rewarded for it!
> >
> >
>|||Not to detract what has already been said, but if the data
is that critical then you have a case for clustering or
having a warm standby. Both of these option should give
you near enough immediate recovery without data loss.
J
>--Original Message--
>
>Dear Sirs,
>I have an sql 2000 ever growing databse, 24 hours a day 7
days a week,
>what is the best to back it up. All the data is critical
to be
>preserved.
>Any method would be apreciated.
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
>.
>|||Hi Uri,
Thanks for poiting out. I accept that.
--
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OYALqEtUEHA.2484@.TK2MSFTNGP11.phx.gbl...
> Hari
> > In case of Restore, I will restore the Differential backup taken last
> night
> > and start applying the Transaction log one by one till last file.
> But do not forget restore FULL backup database before it (1T):)
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:umQy62sUEHA.2564@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > OK I Agree, Both areas got it own demerits. But still if you have a huge
> > database (1 TB) the full database
> > backup is not a feasible solution because it will take a long time even
in
> a
> > SAN .
> >
> > Restore?
> >
> > In case of Restore, I will restore the Differential backup taken last
> night
> > and start applying the Transaction log one by one till last file.
> >
> > Please explain me if you have a better approach.
> >
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:urcN7nsUEHA.4064@.TK2MSFTNGP11.phx.gbl...
> > > Well
> > > What is about restoring ?
> > > I'd use DIFF backup if amount of time spent in recovering the database
> by
> > > applying all the transaction logs is not acceptable.
> > >
> > > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > > news:OpHxsgsUEHA.3944@.TK2MSFTNGP12.phx.gbl...
> > > > Hi Uri,
> > > >
> > > > I agree with you 90%, rest 10% I disagree because if the database
size
> > > grows
> > > > above 50Gb, it is always
> > > > advised to do a differential during week days. This will save your
> > backup
> > > > time as well as reduce the IO used for backup operations.
> > > >
> > > > Do you agree with me or :)
> > > >
> > > >
> > > > --
> > > > Thanks
> > > > Hari
> > > > MCDBA
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:ukTnZprUEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > > > > Hari
> > > > > Each Diff backup will be grown so on Saturday you will have a
pretty
> > big
> > > > > file and look,if you want to restore database you will need to
> restore
> > > > FULL
> > > > > backup and then a bigger (last) DIFF backup which will consume
more
> > > time
> > > > > and resourse.
> > > > > In my opinion he can do it every night FULL backup of the database
> and
> > > > then
> > > > > every 30 min or an hour LOG file.
> > > > >
> > > > >
> > > > > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > > > > news:%23SfnEVrUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> > > > > > Hi,
> > > > > >
> > > > > > I suggest you to do below backup strategy:-
> > > > > >
> > > > > > 1. Sunday - Full database Backup
> > > > > > 2. Every day night - Differential backup
> > > > > > 3. Every 30 minutes or 1 hour Transaction log backup.
> > > > > >
> > > > > > have a look into the details of above strategies:-
> > > > > >
> > > > > > http://techrepublic.com.com/5100-6268-1043671-3.html
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Thanks
> > > > > > Hari
> > > > > > MCDBA
> > > > > > "elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
> > > > > > news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
> > > > > > >
> > > > > > >
> > > > > > > Dear Sirs,
> > > > > > >
> > > > > > > I have an sql 2000 ever growing databse, 24 hours a day 7 days
a
> > > week,
> > > > > > > what is the best to back it up. All the data is critical to be
> > > > > > > preserved.
> > > > > > >
> > > > > > > Any method would be apreciated.
> > > > > > >
> > > > > > > *** Sent via Devdex http://www.devdex.com ***
> > > > > > > Don't just participate in USENET...get rewarded for it!
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||What if you get a corrupt log backup?
Diff backups give a bit more security.
"Uri Dimant" wrote:
> Well
> What is about restoring ?
> I'd use DIFF backup if amount of time spent in recovering the database by
> applying all the transaction logs is not acceptable.
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:OpHxsgsUEHA.3944@.TK2MSFTNGP12.phx.gbl...
> > Hi Uri,
> >
> > I agree with you 90%, rest 10% I disagree because if the database size
> grows
> > above 50Gb, it is always
> > advised to do a differential during week days. This will save your backup
> > time as well as reduce the IO used for backup operations.
> >
> > Do you agree with me or :)
> >
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:ukTnZprUEHA.1012@.TK2MSFTNGP09.phx.gbl...
> > > Hari
> > > Each Diff backup will be grown so on Saturday you will have a pretty big
> > > file and look,if you want to restore database you will need to restore
> > FULL
> > > backup and then a bigger (last) DIFF backup which will consume more
> time
> > > and resourse.
> > > In my opinion he can do it every night FULL backup of the database and
> > then
> > > every 30 min or an hour LOG file.
> > >
> > >
> > > "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> > > news:%23SfnEVrUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > I suggest you to do below backup strategy:-
> > > >
> > > > 1. Sunday - Full database Backup
> > > > 2. Every day night - Differential backup
> > > > 3. Every 30 minutes or 1 hour Transaction log backup.
> > > >
> > > > have a look into the details of above strategies:-
> > > >
> > > > http://techrepublic.com.com/5100-6268-1043671-3.html
> > > >
> > > >
> > > > --
> > > > Thanks
> > > > Hari
> > > > MCDBA
> > > > "elie chucrallah" <eliechucrallah@.hotmail.com> wrote in message
> > > > news:eYA8V5qUEHA.808@.tk2msftngp13.phx.gbl...
> > > > >
> > > > >
> > > > > Dear Sirs,
> > > > >
> > > > > I have an sql 2000 ever growing databse, 24 hours a day 7 days a
> week,
> > > > > what is the best to back it up. All the data is critical to be
> > > > > preserved.
> > > > >
> > > > > Any method would be apreciated.
> > > > >
> > > > > *** Sent via Devdex http://www.devdex.com ***
> > > > > Don't just participate in USENET...get rewarded for it!
> > > >
> > > >
> > >
> > >
> >
> >
>
>