Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DeepSeed
Yak Posting Veteran
52 Posts |
 Posted - 2009-03-10 : 14:12:43
|
| I noticed that SQL keeps logs of all transactions. I figure I might be able to make use of these logs to some extent.Say I ran a script an hour ago that mangled the database. Rather than deleting the database and restoring a backup can I do the following?:Tell the database to rollback all transactions over the last hour or something similar? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-10 : 14:33:08
|
| You can run a database restore from your last full database backup, and restore forward to a point in time from your transaction log backups (if you have them).CODO ERGO SUM |
 |
|
|
DeepSeed
Yak Posting Veteran
52 Posts |
Posted - 2009-03-10 : 15:01:53
|
| I was wondering if there was a way to do it without backing up at all or restoring from a backup. Since the database has logs of all transactions, is there a way to rollback the last so many transactions? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-10 : 15:05:26
|
| You can't do it unless you are storing all the changes in log table with Triggers. |
|
|
|
DeepSeed
Yak Posting Veteran
52 Posts |
Posted - 2009-03-10 : 16:22:02
|
| So here is what my co-workers are doing, they have a 10GB database. They want to test a new script they are writing for it. It takes them say 25 tries to get the script working, each time they run it they check the front end and see that it messed up the data and did not work. They have to restore their backup of this 10GB database each and every time. It takes a long time to restore and gives them errors about database being in use etc.It seems so inefficient to restore the entire database just to jump back to before they ran the script. Is this the only way Microsoft intended this to be done? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-10 : 17:16:28
|
| There are other way you could handle this:1. Setup a script to reload the tables that were updated.2. Run tests on a Virtual Server, and then shutdown the Virtual Server without saving the changes after you test. When you start it again, your server will be in the state that it was when you started.CODO ERGO SUM |
|
|
|
DeepSeed
Yak Posting Veteran
52 Posts |
Posted - 2009-03-10 : 18:38:43
|
| What would it use to reload the table? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-03-20 : 09:25:15
|
| why not just put the data changes inside an "UNCOMMITTED" transaction? can your front end view the impact of such trransactions?if they are happy, "COMMIT" the transaction. If not...ROLLBACK the transaction? |
|
|
|
|
|
|
Going back to a previouse state