Professional Web Applications Themes

Ooops, I did an UPDATE and made a mistake on 1300 records - Microsoft SQL / MS SQL Server

Whoops. You can possibly undo the transaction using Log Explorer ([url]www.lumigent.com[/url]). Don't think you can restore just a table from a backup file, unless its in its own filegroup. One workaround would be to restore as a different database name and then you could DTS that table (obviously with a different name also) back to the original DB. "Scott A. Keen" <scottscottkeen.com> wrote in message news:eG1ww$9RDHA.2892TK2MSFTNGP10.phx.gbl... > I did an UPDATE from the SQL Query yzer and overwrote about 1300 records > with the wrong data. > > How do I restore a table from a backup file, but restore ...

  1. #1

    Default Re: Ooops, I did an UPDATE and made a mistake on 1300 records

    Whoops.

    You can possibly undo the transaction using Log Explorer ([url]www.lumigent.com[/url]).
    Don't think you can restore just a table from a backup file, unless its in
    its own filegroup. One workaround would be to restore as a different
    database name and then you could DTS that table (obviously with a different
    name also) back to the original DB.





    "Scott A. Keen" <scottscottkeen.com> wrote in message
    news:eG1ww$9RDHA.2892TK2MSFTNGP10.phx.gbl...
    > I did an UPDATE from the SQL Query yzer and overwrote about 1300
    records
    > with the wrong data.
    >
    > How do I restore a table from a backup file, but restore it with a
    different
    > name and not overwrite the current table?
    >
    > After I restore it with a different name, I want to UPDATE the fields in
    the
    > current table with the data from a field in the backup table.
    >
    > Thanks,
    >
    > Scott
    >
    >

    Aaron Bertrand - MVP Guest

  2. #2

    Default Re: Ooops, I did an UPDATE and made a mistake on 1300 records

    OK, Thanks.

    That's what I'm going to do -- restore as a different database name, with
    different physical filename, to a different server, then use DTS to copy the
    table with a different name to the live database. Then I'll run my UPDATE
    query.

    I'm backing up the live database before I do anything. Man, this s.

    Scott



    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:%23BkLOH%23RDHA.1720TK2MSFTNGP10.phx.gbl...
    > Whoops.
    >
    > You can possibly undo the transaction using Log Explorer
    ([url]www.lumigent.com[/url]).
    > Don't think you can restore just a table from a backup file, unless its in
    > its own filegroup. One workaround would be to restore as a different
    > database name and then you could DTS that table (obviously with a
    different
    > name also) back to the original DB.
    >
    >
    >
    >
    >
    > "Scott A. Keen" <scottscottkeen.com> wrote in message
    > news:eG1ww$9RDHA.2892TK2MSFTNGP10.phx.gbl...
    > > I did an UPDATE from the SQL Query yzer and overwrote about 1300
    > records
    > > with the wrong data.
    > >
    > > How do I restore a table from a backup file, but restore it with a
    > different
    > > name and not overwrite the current table?
    > >
    > > After I restore it with a different name, I want to UPDATE the fields in
    > the
    > > current table with the data from a field in the backup table.
    > >
    > > Thanks,
    > >
    > > Scott
    > >
    > >
    >
    >

    Scott A. Keen Guest

  3. #3

    Default Re: Ooops, I did an UPDATE and made a mistake on 1300 records

    In query yzer, I tend to do this:

    BEGIN TRAN
    -- query that could be fat-fingered.

    I can press F5, and then if the row(s) affected message indicates to me that
    something went wrong, I can always issue ROLLBACK TRAN.



    "Scott A. Keen" <scottscottkeen.com> wrote in message
    news:u4$JsU#RDHA.2148TK2MSFTNGP12.phx.gbl...
    > OK, Thanks.
    >
    > That's what I'm going to do -- restore as a different database name, with
    > different physical filename, to a different server, then use DTS to copy
    the
    > table with a different name to the live database. Then I'll run my UPDATE
    > query.
    >
    > I'm backing up the live database before I do anything. Man, this s.
    >
    > Scott
    >
    >
    >
    > "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    > news:%23BkLOH%23RDHA.1720TK2MSFTNGP10.phx.gbl...
    > > Whoops.
    > >
    > > You can possibly undo the transaction using Log Explorer
    > ([url]www.lumigent.com[/url]).
    > > Don't think you can restore just a table from a backup file, unless its
    in
    > > its own filegroup. One workaround would be to restore as a different
    > > database name and then you could DTS that table (obviously with a
    > different
    > > name also) back to the original DB.
    > >
    > >
    > >
    > >
    > >
    > > "Scott A. Keen" <scottscottkeen.com> wrote in message
    > > news:eG1ww$9RDHA.2892TK2MSFTNGP10.phx.gbl...
    > > > I did an UPDATE from the SQL Query yzer and overwrote about 1300
    > > records
    > > > with the wrong data.
    > > >
    > > > How do I restore a table from a backup file, but restore it with a
    > > different
    > > > name and not overwrite the current table?
    > > >
    > > > After I restore it with a different name, I want to UPDATE the fields
    in
    > > the
    > > > current table with the data from a field in the backup table.
    > > >
    > > > Thanks,
    > > >
    > > > Scott
    > > >
    > > >
    > >
    > >
    >
    >

    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: Ooops, I did an UPDATE and made a mistake on 1300 records

    Thanks. I will do that.

    Live and learn.

    oooooffaaaa.

    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:%23f93YX%23RDHA.2240TK2MSFTNGP11.phx.gbl...
    > In query yzer, I tend to do this:
    >
    > BEGIN TRAN
    > -- query that could be fat-fingered.
    >
    > I can press F5, and then if the row(s) affected message indicates to me
    that
    > something went wrong, I can always issue ROLLBACK TRAN.
    >
    >
    >
    > "Scott A. Keen" <scottscottkeen.com> wrote in message
    > news:u4$JsU#RDHA.2148TK2MSFTNGP12.phx.gbl...
    > > OK, Thanks.
    > >
    > > That's what I'm going to do -- restore as a different database name,
    with
    > > different physical filename, to a different server, then use DTS to copy
    > the
    > > table with a different name to the live database. Then I'll run my
    UPDATE
    > > query.
    > >
    > > I'm backing up the live database before I do anything. Man, this s.
    > >
    > > Scott
    > >
    > >
    > >
    > > "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    > > news:%23BkLOH%23RDHA.1720TK2MSFTNGP10.phx.gbl...
    > > > Whoops.
    > > >
    > > > You can possibly undo the transaction using Log Explorer
    > > ([url]www.lumigent.com[/url]).
    > > > Don't think you can restore just a table from a backup file, unless
    its
    > in
    > > > its own filegroup. One workaround would be to restore as a different
    > > > database name and then you could DTS that table (obviously with a
    > > different
    > > > name also) back to the original DB.
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > "Scott A. Keen" <scottscottkeen.com> wrote in message
    > > > news:eG1ww$9RDHA.2892TK2MSFTNGP10.phx.gbl...
    > > > > I did an UPDATE from the SQL Query yzer and overwrote about 1300
    > > > records
    > > > > with the wrong data.
    > > > >
    > > > > How do I restore a table from a backup file, but restore it with a
    > > > different
    > > > > name and not overwrite the current table?
    > > > >
    > > > > After I restore it with a different name, I want to UPDATE the
    fields
    > in
    > > > the
    > > > > current table with the data from a field in the backup table.
    > > > >
    > > > > Thanks,
    > > > >
    > > > > Scott
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Scott A. Keen Guest

  5. #5

    Default Re: Ooops, I did an UPDATE and made a mistake on 1300 records

    No need to copy the table if you restore the database to a different
    name on the same server. You can update the table directly with 3-part
    names:

    UPDATE MyDatabase..MyTable
    SET MyData = MyRestoredDatabase..MyTable.MyData
    FROM MyRestoredDatabase..MyTable
    WHERE MyRestoredDatabase.MyTable.MyPK = MyDatabase.MyTable.MyPK

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    [url]http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800[/url]
    [url]http://www.sqlserverfaq.com[/url]
    [url]http://www.mssqlserver.com/faq[/url]
    -----------------------

    "Scott A. Keen" <scottscottkeen.com> wrote in message
    news:u4$JsU%23RDHA.2148TK2MSFTNGP12.phx.gbl...
    > OK, Thanks.
    >
    > That's what I'm going to do -- restore as a different database name,
    with
    > different physical filename, to a different server, then use DTS to
    copy the
    > table with a different name to the live database. Then I'll run my
    UPDATE
    > query.
    >
    > I'm backing up the live database before I do anything. Man, this
    s.
    >
    > Scott
    >
    >
    >

    Dan Guzman Guest

Similar Threads

  1. I made a terrible mistake...
    By peppermint_patty in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: July 18th, 05:06 AM
  2. SQL Insert - no errors but no update made
    By swissmiss954 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: February 18th, 01:15 AM
  3. Update records ASP
    By Raincheck in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 17th, 12:51 PM
  4. SORRY FOR OFFTOPIC! I've made a mistake, choosing group in OE
    By Vsevolod Ukrainsky in forum ASP Components
    Replies: 0
    Last Post: August 14th, 12:39 PM
  5. Update / Add New Records
    By PBrown in forum Microsoft Access
    Replies: 2
    Last Post: July 14th, 10:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139