Back up Schedulling

Question

Back up Schedulling

Ali Tailor

Hall of Fame

Points: 3769
More actions
November 10, 2010 at 10:40 pm

#388205

Hi Gurus
I asked a question in an InterView as following.
There is One Plus TB Database You are Admin of that database;
What Backup strategy you will adopt for a minimal data loss?
Please replye, thank in Advance.
Ali
MCTS SQL Server2k8

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Ali Tailor (11/10/2010)
Hi Gurus
I asked a question in an InterView as following.
There is Onle Plus TB Database Yoy are Admin of that datababase;
What Backup strategy you will adopt for a minimal data loss?
Please replye, thank in Advance.

Onle Plus TB Database --Not understand

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 2

TB = TeraByte??

You can perform Full Backup Once a Week, Preferably on Weekends,

and a Transaction Log Backup Every Hour ....,,

A better approach is to divide the Database in multiple file groups based on how frequently the data is modified,

By this way you can put all data that is read only in a single file group and backup that only once a week,

And, take a differential partial backup of the file groups everyday that are frequently modified.

Vishal Gajjar
http://SqlAndMe.com

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 3

Hi

Say Hey Kid

Actualy It was typing mistake

I meen 'One Terebite Plus Database,

Thanks

Ali
MCTS SQL Server2k8

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 4

Vishal.Gajjar (11/11/2010)
TB = TeraByte??
You can perform Full Backup Once a Week, Preferably on Weekends,
and a Transaction Log Backup Every Hour ....,,

Thanks Vihsal,

I Answered the Same (Above in quote) but, He ask again at the time of restoration there is too much transaction backup After Restoring the Fullbackup. And I think He is Not Satisfyed.

I think there should be a deffrential backup on every night.

But I'm Not answered any thing.

Ali
MCTS SQL Server2k8

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 5

Ali Tailor (11/11/2010)
Hi
Say Hey Kid
Actualy It was typing mistake
I meen 'One Terebite Plus Database,
Thanks

The think is you have to ask couple of questions to the interviewer .

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 6

How critical is the application ?

How much time acceptable in case of DB unavailable?

How much data loss acceptable (Minimum)?

Finally, What type of disk level you have in place ? (to take a Terabyte bak )

If you asked these questions then may be he/she will satisfied.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

Ali Tailor (11/11/2010)
Vishal.Gajjar (11/11/2010)
TB = TeraByte??
You can perform Full Backup Once a Week, Preferably on Weekends,
and a Transaction Log Backup Every Hour ....,,
Thanks Vihsal,
I Answered the Same (Above in quote) but, He ask again at the time of restoration there is too much transaction backup After Restoring the Fullbackup. And I think He is Not Satisfyed.
I think there should be a deffrential backup on every night.
But I'm Not answered any thing.

Yep,

Differential method saves time at restoration.

Differential back minimize the time of T-log backups

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 8

Thaks

muthukkumaran for responce,

May I keep

One Full Backup Weekly at Weekend Night

One Defferential Daily at Night

And Trans.LogBackup On Every one Hrs.

is it OK?

thanks

Ali
MCTS SQL Server2k8

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 9

Ali Tailor (11/11/2010)
Thaks
muthukkumaran for responce,
May I keep
One Full Backup Weekly at Weekend Night
One Defferential Daily at Night
And Trans.LogBackup On Every one Hrs.
is it OK?
thanks

The answer is "It depends"

Its ok for me.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

saidapur_satish SSC Veteran Points: 288 More actions · Answer 10

Hi Ali,

1+TB data means not a jock..........

If you give the full backup means take too much time & also that time your server is down, so i suggest you...

1. Give the full backup on 1st saturday night....

2. Give difrencial backup on every night (in peak hour)

3. If your dava is very very importent then give transaction backup at every 4 hours becouse you telling your data is in TB.......

This is the best way to save & secure the data.....

Regards

Satish

grahamc SSCertifiable Points: 6762 More actions · Answer 11

saidapur_satish (11/11/2010)
Hi Ali,
1+TB data means not a jock..........
If you give the full backup means take too much time & also that time your server is down, so i suggest you...
1. Give the full backup on 1st saturday night....
2. Give difrencial backup on every night (in peak hour)
3. If your dava is very very importent then give transaction backup at every 4 hours becouse you telling your data is in TB.......
This is the best way to save & secure the data.....
Regards
Satish

Whats a "jock"?

Why would the server be down during a backup??

As stated before, it all depends... many questions would need to be asked, including what the current storage looks like. You may have better options at a SAN level if you are running a SAN.

But generally, I would go for:

-Full backup when system is doing least amount of work (I stay away from weekends as that is when you should be doing other maintenance tasks)

-Diff backup every evening

-Log backups (frequency would be anywhere from 15 minutes to 60 depending on accept data loss)

Considering that you are only taking 1 full backup a week, I would want to be restoring this somewhere (applying diffs and logs as well) just to make sure.

As suggested earlier, splitting the DB up into separate filegroups would make your life a lot easier.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 12

saidapur_satish (11/11/2010)
Hi Ali,
1+TB data means not a jock..........
If you give the full backup means take too much time & also that time your server is down, so i suggest you...
1. Give the full backup on 1st saturday night....
2. Give difrencial backup on every night (in peak hour)
3. If your dava is very very importent then give transaction backup at every 4 hours becouse you telling your data is in TB.......
This is the best way to save & secure the data.....
Regards
Satish

Satish the thing is its interview question ...

Edited :Spelling

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 13

Thanks All,

I got the point , that I should keep the schedule as

On in weekend night Full backup

daily At night deff. backup

And depending on data loss Accepted, log backup(In minuts).

Ali
MCTS SQL Server2k8