Rolling back a SQL Server 2005 database while retaining data added

Tags:
Recovery
SQL Server 2005
SQL Server database
SQL Server Database Rollback
SQL Server recovery
Windows Server 2003
I have a database that has been running for about six months in SQL Server 2005 on 64-bit Windows Server 2003. There are no backups, but recovery mode is set to full and I have never deleted the log file. I need to rollback to a recent point in time while also retaining data added since that point in time. I am open to working with copies of the files in their current state under a different database name, but how do I do this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

First of all you MUST start taking backups. At present if your disk crashed you would loose all of your data.

A good plan is to take Full backups each day, and Log backups each hour. This can be done by creating one maintenance plan for the daily Full backup, and a second maintenance plan for the hourly Log backup. Do not use any options to shrink your database in the maintenance plan, as this will lead to poor performance of your queries. There is lots of advice in Books Online and on the web about database maintenance – it is worth taking an hour or two to read and learn more about what you can do.

There is no method with the software provided with SQL Server to selectively undo unwanted data. However, there are a number of third-party products that can analyse your log file and generate SQL statements to remove changes according to set criteria. In this way you could remove all changes made by user XXX between two dates, etc. The hard bit in this type of selective deletion is working out what criteria to use. You should definitely try this on a copy database before changing your live data. Especially as you are very very close to loosing what data you currently have.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following