Q:
Rolling back a SQL Server 2005 database while retaining data added
Windows Server 2003, SQL Server 2005, Recovery, SQL Server database, SQL Server recovery, SQL Server Database Rollback 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?
ASKED:
Oct 21 2008 8:03 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
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.
