Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a database that is in full recovery mode. The database is not in production yet so does not have a backup plan in place and is thus not being backed up. In other words, there is no existing backup. Recently, during an update, about 400 rows of data were wiped out.

Is there a way to recover those rows given that we have full transaction logging? I understand that if we had a full backup w/ transaction logs we could do a point in time recovery. Will this work if we now take a full backup of transaction logs and the database, then do a recover?

I feel like there is one path to recovery, I'm just unclear of the order in which to proceed.

Thanks for your assistance!

UPDATE: We ended up using this tool http://www.apexsql.com/sql_tools_recover.aspx I would still be interested if anyone comes across this and knows a better way. For now, we paid the price, but we got the results. Thanks for the responses!

share|improve this question
    
Interesting, I'll bookmark that just in case! –  Gaius Feb 17 '11 at 21:08
add comment

1 Answer 1

Not just using the transaction log, no. It records redo, but not undo, so you can only roll forwards from a point in time, which is the time of a backup. It may be possible to replay the entire log from the start on a blank database, but I don't know you'd "tell" SQL Server that you wanted to do that, even creating a database "for load" probably wouldn't allow it...

share|improve this answer
    
Right - that's how I am thinking about it as well. I wonder if I created a fresh database on a SQL Server back dated a year and took a backup of that fresh empty database, then did a restore if the transaction logs would iterate forward correctly given that I should have all transaction logs from the time the database was created. –  JpMaxMan Feb 17 '11 at 20:22
    
You would have to somehow convince SQL Server that the blank database really was an old backup. If you do DBCC FILEHEADER you will see GUIDs in there (BindingID being the most important in this case), and they'll have to match. –  Gaius Feb 17 '11 at 20:26
1  
You might as well read the following questions and their answers: Get back deleted records - especially Brent's answer and How to restore database using old full backup and current log file. They might help you create the appropriate plans for the future. –  Marian Feb 17 '11 at 21:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.