DB restore

Question

Post reply

DB restore

Eswin

SSCrazy Eights

Points: 8320
More actions
June 1, 2009 at 12:37 am

#204953

Hi Expertz

I have taken backup of DB_test as follows

Full Backup

Differential Backup_1

Differential Backup_2

Transactional log Backup_1

Transactional log Backup_2

Transactional log Backup_3

Transactional Tail of log Backup

Now the database is in restoring state....

Can you please tell me which all backup should i use and in which order should i RESTORE the database using the above backups........

Tanx 😀

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

First the full backup, then the last differential, then all of the logs, in order. Restore them all with the NORECOVERY option then, once you're certain that you have all the backups restored that you need, recover the database

RESTORE DATABASE <dbname> WITH RECOVERY

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

It is hard to understand from your message what you need. Database doesn't get into restoring state on its own. Did you start the restore?

Having said that, if you need to restore your database without losing data, you'll need to restore the full backup with no recovery. Then you need to restore the second differential backup with no recovery (skip the first differential backup). After that you need to restore the log backups using the same order that they were taken (the first backup should be restored first, the second backup should be restored second, etc') with no recovery. Only the last log backup should be restored with recovery.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eswin SSCrazy Eights Points: 8320 More actions · Answer 3

Eswin

SSCrazy Eights

Points: 8320

June 1, 2009 at 2:22 am

#1002598

IN last transactional log backup I checked the back up the tail log.

and leave the database in restoring state option....

That made database in restore state....

So when should I restore the transactional Tail backup.....

Is it the last restore i have to do with or without recovery.......

Tanx 😀

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

The tail log backup is the last of the log backups. Hence you restore it last.

You can restore it with recovery it you want, or with norecovery and then recover the database with the RESTORE DATABASE command that I gave. Both options work.

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