June 1, 2009 at 12:37 am
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 😀
June 1, 2009 at 1:28 am
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
June 1, 2009 at 1:29 am
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/
June 1, 2009 at 2:22 am
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 😀
June 1, 2009 at 3:54 am
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
June 1, 2009 at 3:55 am
Thanks a lot guyz
Tanx 😀
June 2, 2009 at 4:50 am
When you take backup next time donot check the option of "copy the tail of transaction log and leave database in restoring mode".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply