none
undo delete statement...is it possible

    질문

  • I've made a very silly mistake. I was supposed to run this statement

    delete table_columns
    where col_id = 1223

    but instead I mistakenly executed just the 1st line.

    I have no backups....is there any possible way to undo this before my boss finds out?

    thanks....

    2007년 2월 23일 금요일 오후 4:03

답변

  • There is no "undelete"  function in MS SQL. 

    There are tools which can read your transaction log and undo your command.  But "before my boss finds out" is probably not going to happen.

    Take a look at: http://www.apexsql.com/sql_tools_log.asp

    2007년 2월 23일 금요일 오후 7:28
  • I know that this advice doesn't help you with your current predicament, but it is often a good idea to issue a BEGIN TRANSACTION statement before performing manual data modifications. Once the data modification statement has completed you can then issue either ROLLBACK TRANSACTION or COMMIT TRANSACTION as appropriate.

    Chris

    2007년 2월 25일 일요일 오전 9:13
  • I'm afraid you are out of luck as far as native SQL Server is concerned. I believe that Red Gate and Apex have tools that might let you read the transaction log. But since you have no backups, you are probably running in Simple recovery mode (otherwise your transaction log would have filled up), so even those tools probably will not be able to help you.
    2007년 2월 24일 토요일 오후 5:41

모든 응답

  • no transaction log backups?  No database backups?

    you're SOL.

    2007년 2월 23일 금요일 오후 4:13
  • There is no "undelete"  function in MS SQL. 

    There are tools which can read your transaction log and undo your command.  But "before my boss finds out" is probably not going to happen.

    Take a look at: http://www.apexsql.com/sql_tools_log.asp

    2007년 2월 23일 금요일 오후 7:28
  • I'm afraid you are out of luck as far as native SQL Server is concerned. I believe that Red Gate and Apex have tools that might let you read the transaction log. But since you have no backups, you are probably running in Simple recovery mode (otherwise your transaction log would have filled up), so even those tools probably will not be able to help you.
    2007년 2월 24일 토요일 오후 5:41
  • I know that this advice doesn't help you with your current predicament, but it is often a good idea to issue a BEGIN TRANSACTION statement before performing manual data modifications. Once the data modification statement has completed you can then issue either ROLLBACK TRANSACTION or COMMIT TRANSACTION as appropriate.

    Chris

    2007년 2월 25일 일요일 오전 9:13

  • OK, take a deep breath.
    Write a resignation letter.
    Go into your bosses office.
    Own up.

    You might lose your job, but at least you'll be respected more.
    2008년 3월 10일 월요일 오후 7:32
  • This is why I think database vendors should disable DROP and DELETE by default if a WHERE clause is not specified. However, in the event that you need to use a DROP or DELETE without a WHERE, you should have to provide an overriding keyword something like:

     

    DROP COMMIT tablename/dbname

     

    DELETE COMMIT tablename/dbname

     

    This should save a few people their jobs.

    2008년 5월 5일 월요일 오후 4:53
  •  TDAVISJR wrote:

    This is why I think database vendors should disable DROP and DELETE by default if a WHERE clause is not specified. However, in the event that you need to use a DROP or DELETE without a WHERE, you should have to provide an overriding keyword something like:

     

    DROP COMMIT tablename/dbname

     

    DELETE COMMIT tablename/dbname

     

    This should save a few people their jobs.



    Or just DELETE tablename WHERE 1 = 1.
    2008년 5월 6일 화요일 오후 12:26
  • IMHO if I would work for a company that "forgets" to backup DB's I would either have the DBA fired or find another job. Also if you did this on a production database something is seriously long with the way you guys are working. Good luck.

    2008년 5월 7일 수요일 오전 6:21
  •  

    Actually, INFORMIX requires a where clause in this scenario.  I still catch myself with SQL Server using 'where 1=1'.
    2008년 10월 26일 일요일 오전 2:58
  • If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

    DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

    USE tempdb
    GO
    -- Create Test Table
    CREATE TABLE TruncateTest (ID INT)
    INSERT INTO TruncateTest (ID)
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    GO
    -- Check the data before truncate
    SELECT * FROM TruncateTest
    GO
    -- Begin Transaction
    BEGIN TRAN
    -- Truncate Table
    TRUNCATE TABLE TruncateTest
    GO
    -- Check the data after truncate
    SELECT * FROM TruncateTest
    GO
    -- Rollback Transaction
    ROLLBACK TRAN
    GO
    -- Check the data after Rollback
    SELECT * FROM TruncateTest
    GO
    -- Clean up
    DROP TABLE TruncateTest
    GO
    2012년 7월 11일 수요일 오후 1:00
  • no Help  sorry to say

    if you have any transaction backup running over your database,

    if yes you have a option to read the transaction log backups


    Ramesh Babu Vavilla MCTS,MSBI

    • 답변으로 제안됨 vr.babu 2012년 7월 11일 수요일 오후 2:02
    2012년 7월 11일 수요일 오후 2:02
  • Transactions only help before commiting the transaction. Once the transaction is committed, there is no "undo" feature.

    In the OP case:

    DELETE FROM table

    is an implicit transaction and was committed.  So there is no way to get it back without restoring the database.

    • 답변으로 제안됨 Shanky_621 2013년 9월 19일 목요일 오전 9:42
    2012년 7월 11일 수요일 오후 3:22
  • Unfortunately in such cases its not possible to roll-back the data .
    2012년 8월 23일 목요일 오전 9:44
  • Please Try this if you have Luck--If you remember those data values insert the same values once again.

    Incase if you set the primary key remove the primary key and Off the Auto increment(Identity Specification) then insert your data's manually then it may work I hope but it's not sure.

    Post the comment if it's working



    2013년 9월 18일 수요일 오후 6:40