August 18, 2007 at 11:55 am
Hi Friends,
My database plan is
Every day 12:00 noon Full backup
Every day every two hours Differential Backups
Every Day every Fifteen min Take Transactionlog Backups
But Today my database is crassed at 4:15pm how can i resore my database evening 6:00 clock also all Transactions going on how can i restore plz help me How can i restore my database
August 18, 2007 at 2:26 pm
Hi,
The first thing you need to do is run:
RESTORE DATBASE [DatabaseName] FROM DISK='path to full backup' WITH NO RECOVERY, <Any move options required>
You then need to restore any differential backups just changing the path to the backup keeping the WITH NO RECOVERY option,
once you have done this for all of your differential backups up to the point you want to - finally you need to restore the transaction log backups. Run this command for each of the transaction log backups taken between the differential and the time you want to restore to:
RESTORE LOG [DatabaseName] FROM DISK='Path to transaction log backup' WITH NO RECOVERY, STOPAT='<Time you need to stop at>'
Finally run:
RESTORE DATABASE [DatabaseName] WITH RECOVERY.
James
--
James Moore
Red Gate Software Ltd
August 18, 2007 at 10:16 pm
Hi James,
Thank u for u reply,
what is the difference between with recovery and recovery options it will apply Transaction log files also what is use of those
Thanks & Regaurds
subu
August 19, 2007 at 2:50 am
Hi subu,
WITH NO RECOVERY leaves the database offline so you cannot modify it and it is then safe to apply further backups to the database. WITH RECOVERY brings the database back online.
James
--
James Moore
Red Gate Software Ltd
August 20, 2007 at 12:05 pm
James, I think your advice is a little bit off. Once the FULL backup is restored, Subu only has to restore the most recent Differential backup and then all the transaction logs up to the point of time he/she wants.
Per BOL "A differential backup is based on the most recent, previous full backup. This is known as the base of the differential. A differential backup includes only the data that has changed since the differential base. ", which means restoring multiple differential backups after the last full backup is kind of pointless.
Also, Subu, you cannot restore any further to your database if you use the WITH RECOVERY command. SQL Server simply won't allow it.
Lastly, I would change the times of your FULL backup if I were you. Unless you don't have anyone using your database at noon. Schedule your bigger backups for times when you have the least number of users on the database so your database doesn't get bogged down by all the processing going on.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply