Backup Scenario

Question

Backup Scenario

y.koteswarrao-652921

SSCrazy

Points: 2010
More actions
October 13, 2009 at 6:31 am

#385568

Hi,
Backup Scenario
sunday 5 pm - full backup
Daily 7 pm - differital backup
Every 15 min - Transacional backup
Thrusday at 8:25 pm system was crash, what is approach in bringing the database back.
Can any give solution to above scenario?
Thank you
Koti

Viewing 8 posts - 1 through 7 (of 7 total)

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

Animal Magic SSChampion Points: 13964 More actions · Answer 1

y.koteswarrao-652921 (10/13/2009)
Hi,
Backup Scenario
sunday 5 pm - full backup
Daily 7 pm - differital backup
Every 15 min - Transacional backup
Thrusday at 8:25 pm system was crash, what is approach in bringing the database back.
Can any give solution to above scenario?
Thank you
Koti

backup tail log

restore sunday backup

restore thursdays diff backup

restore tlogs for 7.15,7.30,7.45,8,8.15

restore tail log backup

is this an interview question? do i get the job?

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 2

Hi,

Thank you every much i have give same reply , but i missed the tale backup.

Thanks

Koteswar Rao.

y.koteswarrao-652921 SSCrazy Points: 2010 More actions · Answer 3

Hi,

As you said to take the tale backup of transaction log,my doubt is how to take the tale back up if the system crashes!

Could you please reply

Koteswar rAo

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

If your db crashes for whatever reason, you have to backup your transaction log so that you can do point in time recovery. This last backup is called Tail log backup. If your data file (MDF) is unavailable you need to use WITH NO_TRUNCATE option:

-- Back up the AdventureWorks tail-log

BACKUP LOG AdventureWorks

TO DISK = N'c:\AdventureWorksTailLog.bak' WITH NO_TRUNCATE

If your database is in OFFLINE or EMERGENCY state then tail log backup isn't possible.

http://www.sqlteam.com/article/introduction-to-sql-server-database-backups

MJ

satheessh SSC Rookie Points: 44 More actions · Answer 5

first we restore fullback up

after latest differential backup

after that 7 to 8.15 transactional backup (5 sets)

after tat we should take tail log back up of particular database by putting db in single user mode which is in restoring state

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

satheessh (12/28/2011)
after tat we should take tail log back up of particular database by putting db in single user mode which is in restoring state

No. Tail log backup has to be done before the restores start (otherwise there's nothing to back up)

Please in future post new questions in a new thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 7

satheessh (12/28/2011)
first we restore fullback up
after latest differential backup
after that 7 to 8.15 transactional backup (5 sets)
after tat we should take tail log back up of particular database by putting db in single user mode which is in restoring state

Two points to mention here...

1) Its OLD thread. (Posted Tuesday, October 13, 2009 7:01 PM)

2) Tail Backup should be first step.