How to recover data to a specific time in SQL ServerDavid Lozano.
I use to write (and read) in a DBA forum.
A question that made an administrator not very experienced in SQL in a message whose subject was "Urgent!!", was how to recover data of the previous day, if he had not made back-up in the last few days.
The answer was clear: recovering of the Transactions Log.
In the first answer perhaps I didn't explain to it too much clear, because later I had another message wondering how to make it goes step by step.
Taking advantage that I already have it written, I publish it here:
De: chakry [mailto:[email protected]]
Enviado el: jueves 28 de abril de 2005 18:57
Para: [email protected]
Asunto: RE: Urgent !!
Can you please tell me how to recove the data from Log file to database?
Do it with the Enterprise Manager, is simpliest, and then if you want you can look up in the help how to do it with the Query Analyzer.
It'is supposed that you have the database working in a Recovery model different of Simple Recovery Model, if you have it in Simple, you don't have the transaction log.
Also is supposed that you have a full backup of some days or months ago, and since that backup you have not deleted the Transaction Log.
Imagine that you lose data today at 15:00. You realice at 15:15. So you have to do a backup of the Transaction Log at 15:20.
Then, you can use the Enterprise Manager Restore Wizard (right click on the database name / Restore Database). Now, since you have done the backup of your log, you can specify until what time do you want to revover. In this case 14:59.
SQL Server will use your last full back-up and then will add the changes until the date and time specified.
David Lozano Lucas
MCSE, MCSA, MCDBA, OCA & CLP
Madrid - Spain