July 24, 2005 at 11:23 pm
Hi Everybody,
Today i had deleted few records from one of my database table .
Now i want to roll back that transaction.
i dont have any savepoint while executing the query. not even transation name
can i roll back that transaction.
from
killer
July 25, 2005 at 3:39 am
Hi,
You can really do that just for confirmation.
1. Do you the have the Tlog backups with you.
2. Pleae specify what is the schedule of backups
3. at what time you have deleted the records.
Chandra Mohan K
Sr.DBA
intelligroup
Hyderabad
July 25, 2005 at 4:14 am
Hi,
Thanx Mr Chandra for ur reply
But i think i had mentioned that i dont have any transaction savepoint , not even transaction name
so i think this is enough to understand that i must not have the latest backup.
secondly what does backup schedule will do in that case.
from
killer
July 26, 2005 at 12:55 am
If you do not have the backups available, then you cannot rolback the transaction.
If you had begin the transaction by using Begin Tran and after firing the sql statements, you wanted to rollback, you could have uses Rollback tran.
If you are not sure of what you are doing, its always to begin the trnsaction block with a Begin Tran statement and then Rollback if needed or Commit it.
--Kishore
July 26, 2005 at 1:37 am
Hi,
Ok, so we can't rollback the transaction.But can u explain the machenism how rollback work. As i gone though BOL but did not find much information.
But by tlog information in BOL microsoft says that we can recover any transaction from tlog.
from
killer
July 26, 2005 at 3:23 am
"But by tlog information in BOL microsoft says that we can recover any transaction from tlog."
You can recover info using the TLOG...PROVIDED it's STILL in the TLOG. The TLOG is not (automatically) a bottomless pit/history of database actions.
You have to setup your database to retain the transactions logged in the TLOG for an appropriate time - BEFORE you run a potentially destructive database action.
The code segment
BEGIN TRANSACTION
UPDATE TABLE1 set cola = 'x'
can be rolled back 'in stream'...by issuing a ROLLBACK TRANSACTION statement. IF you do NOT issue the BEGIN statement, the UPDATE statement is IMPLICITLY followed by a COMMIT TRANSACTION statement.
HOWEVER IF the TLOG is setup correct beforehand, you could STILL rollback the UPDATE statement, by reverting to the previous database backup...and rolling FORWARD and - reapplying ALL database actions that happened 'just before' the UPDATE statement was committed.
You need to read more of BOL and/or execute some dummy/training exercises on a test database.
July 26, 2005 at 8:30 pm
Thanx Andrew,
I understand what u said , now i am facing another problem
i used this code for rollback
begin transaction tl
delete from table
commit transaction tl
rollback transaction tl
sql show message the completed successfully. but do not rollback transaction.
-------------------------------i used this also---------------
SET IMPLICIT_TRANSACTIONS ON
delete from temp1
rollback work;
in this case sql show error message that begin trans not defined
but when i used transaction name with savepoint then i can roll back the transaction.
can u pls help me why in the two cases sql server rollback acquit in this way.
from
killer
July 27, 2005 at 2:55 am
You're missing things alright.
begin transaction tl
delete from table
commit transaction tl
At this point the only way of getting back to the database state BEFORE the delete was executed, is to go back to the last database backup and then applying (aka rolling forward) the database transactions upto the thime the begin transaction command was issued.
You can do
begin transaction tl
delete from table
rollback transaction tl
and this will work....(it's a bit like pressing cancel-instead-of-save when exiting "Notepad"....whereas in the 1st case, it was the same as pressing save-when-exiting "Notepad" and then had to go back to last nights LAN backup to get back to the previous version of the document)....you're working at 2 different levels of recovery.
My statement on the implicit commit was to show that the statement "DELETE from table on it's own, with no TRANSACTION clauses wrapped around it" was equivalent to the 1st code snippet above. I have not used the SET IMPLICIT, etc statement myself...so I can give no direction on it.
July 27, 2005 at 8:01 pm
Thanx,
I already set the SET IMPLICIT_TRANSACTIONS ON but if we work on QA without using commit or rollback commands then how can we rollback the transaction.
As per BOL we can rollback any transaction from TLOG but how.
from
KILLER
August 9, 2005 at 3:26 am
"........is to go back to the last database backup and then applying (aka rolling forward) the database transactions upto the thime the begin transaction command was issued."
Investigate the RESTORE command, with particular reference to the parameter STOPAT.
All the info you need is in SQL Server Books OnLine (BOL)..in the section "how to restore to a point in time (Transact SQL)"...what you want to do is to restore to a point-in-time BEFORE the transaction got committed!
August 9, 2005 at 9:45 pm
HI,
How can i set the Transaction save point or transaction name by default for a query. and for every user.
thanx for help
from
killer
August 11, 2005 at 3:47 am
The following is from today's Question of the Day (QOD). It highlights how to recover to a point in time, using backup datasets and transaction logs....IT should be mandatory reading for everybody, and in particular it answsers the original question posed. Regarding your latest question.....I'm stumped on that one....others may be able to help. If this post doesn't get any other reponses, post else where....sqlteam.com and/or dbforums.com.
Yesterday's QOD
You have the following backup files:
Filename | Type | Date |
myBackup_FULL.bak | Full backup | 5/18/2005 5:00 pm |
myBackup_TX_050518510.trn | Transaction log backup | 5/18/2005 5:10 pm |
myBackup_TX_050518520.trn | Transaction log backup | 5/18/2005 5:20 pm |
myBackup_TX_050518530.trn | Transaction log backup | 5/18/2005 5:30 pm |
At 5:25 pm, your customer accidently deleted all of the rows in the products table. What happens when you run the following script?
RESTORE DATABASE [MyDatabase] FROM DISK = N'e:\sqldata\MSSQL\BACKUP\ myBackup_FULL.bak' , NORECOVERY
GO
RESTORE LOG [MyDatabase] FROM DISK = N'e:\sqldata\MSSQL\BACKUP\ myBackup_TX_050518510.trn'
, RECOVERY
, STOPAT = N'5/18/2005 5:24:00 PM'
GO
RESTORE LOG [MyDatabase] FROM DISK = N'e:\sqldata\MSSQL\BACKUP\ myBackup_TX_050518520.trn'
, RECOVERY
, STOPAT = N'5/18/2005 5:24:00 PM'
GO
RESTORE LOG [MyDatabase] FROM DISK = N'e:\sqldata\MSSQL\BACKUP\ myBackup_TX_050518530.trn'
, RECOVERY
, STOPAT = N'5/18/2005 5:24:00 PM'
Submitted by Kathi Kellenberger
Answer
You are able to recover the products table. Any data modifications made after 5:24 pm must be manually re-entered.
Explanation
You are able to recover the products table. The other statements are false. If the point in time occurs after a transaction log backup, the entire log is restored, and the database stays in "Loading" status. When the point in time is reached, the database is recovered.
In order to use the "STOPBEFOREMARK", you must have used the "BEGIN TRANSACTION" statement along with the "WITH MARK" clause before products table was emptied. Since this was an accidental change, you would not have known to do that.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply