doubt in backup and recovery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

doubt in backup and recovery

Author

Topic

thaniparthi.rao
Yak Posting Veteran

96 Posts

Posted - 2008-04-01 : 03:25:31

every sunday 12 am fullback up.
for every 6 hours differential backup.
every 15 min t log back up.

If my server crashes at wednesday 3.30 pm

my restore order is fullbackup on sunday, last differential backup (wednesday 12pm) and the t logs from last full backup or t-log backups from latest differebntail backup

I have doubt after restoring the latest differentail backup we have to restore all t log backups from fullbackuop or all tlog backup from latest diff backup.
Which order we have to follow

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-01 : 05:47:34

from the latest diff backup (or the one that you restore).
Also you can't recover the database before restoring the last log.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

thaniparthi.rao
Yak Posting Veteran

96 Posts

Posted - 2008-04-01 : 07:41:05

my doubt is.
F1 is full back up on sunday at 6 am.

D1 is differential backup on SUNDAY 9 am
T is transcation log backup for every 30 min

F1 6 .0 am
T1 6.30 am
T2 7.00 am
T3 7.30 am
T4 8 .00am
T5 8.30 am
D1 9am
T6 9.30 am
T7 10.0am
T8 10 .30 am

if my server crashes at 10 .45 am then the restor order is

F1 D1 T1 T2 T3 T4 T5 T6 T7 T8
or
F1 D1 T6 T7 T8

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-01 : 07:57:01

Well, full, latest diff all logs following it so

F1, D1, T6, T7, T8
if you lose the diff you could also do
F1, T1, T2, T3, T4, T5, T6, T7, T8

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

beyonder422
Posting Yak Master

124 Posts

Posted - 2008-05-06 : 16:56:17

to ellaborate on this.

if full_1 on sunday, diff1 on monday, diff2 on tuesday, diff3 on wed.

Then crash.

Restore full_1 and then straight to diff3?

I was under the impression I had to restore all diffs (full_1, diff1, diff2, diff3)

but I can simply jump straight to last diff?

meaning there is no reason to keep the intermediate diffs (diff1, diff2)?

I do not define what is obvious...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-06 : 22:02:59

You only need apply most recent diff backup. Diff backup contains all changes since last full backup.

beyonder422
Posting Yak Master

124 Posts

Posted - 2008-05-07 : 08:36:13

That's what I thought I was reading.

Just wanted to verify.

thanx for the info...

I do not define what is obvious...

r.amutha
Starting Member

6 Posts

Posted - 2009-03-16 : 02:16:56

Monday at 2 AM - Full backup and then
Everyday 9PM -Differential backup and then
every ½ hour- Transactional log backup
in that time, on Wednesday at 11.45 AM the Server Crash(sql server 2005 express edition).
How will you restore the data files up to 11.45AM ?
i can able to restore the data upto 11.30 AM but want to restore the
data from 11.30-11.45 AM.
If database crash means v can use tail log backup
but if server crash means wat to do
plz help me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources