Oops, I deleted that data

  • This was removed by the editor as SPAM

  • kingroon

    Valued Member

    Points: 73

    In my current role, Scripts are executed by the Release Team, so it's difficult to hand them something that requires input e.g. changing the @doCommit value to commit the update etc. However, getting an execution report back from them is easy. For this reason, I tend to use something similar to:


    DECLARE @SelectCount INT, @UpdateCount INT, @AccountCodeToUpdate NVARCHAR(2)

    SET @AccountCodeToUpdate = '4c'

    -- Begin Script Task
    SELECT @SelectCount = COUNT(1)
    FROM [dbo].[Account]
    WHERE AccountCode = @AccountCodeToUpdate

    SELECT @SelectCount AS [SelectCount]

    BEGIN TRAN [UpdateTran]

    SET A.Active = 0
    FROM [dbo].[Account] A
    WHERE A.AccountCode = @AccountCodeToUpdate

    SET @UpdateCount = @@ROWCOUNT
    SELECT @UpdateCount AS [UpdateCount]

    IF @SelectCount = @UpdateCount
    COMMIT TRAN [UpdateTran]
    ROLLBACK TRAN [UpdateTran]


    ROLLBACK TRAN [UpdateTran]

  • SQLian


    Points: 413

    Minor tweak, but if it's SQL Server you could possibly replace

    UPDATE account SET active_yn =0 WHERE account_code='4c'
    SELECT 'AFTER' 'AFTER', * FROM dbo.account WHERE account_code='4c'


    UPDATE account SET active_yn =0 WHERE account_code='4c'


  • MVDBA (Mike Vessey)


    Points: 21757

    kingroon wrote:

    In my current role, Scripts are executed by the Release Team, so it's difficult to hand them something that requires input e.g. changing the @doCommit value to commit the update etc. However, getting an execution report back from them is easy. For this reason, I tend to use something similar to:.....


    I like this - I might just add an "OUTPUT deleted.*" into the update statement



  • mmullane


    Points: 11

    Like Freddie, I have developers save the data to be modified to a utility database on the same server. This has a few benefits. The SELECT INTO query can be used to confirm the modification logic, rollbacks are relatively easy and there's a saved history of data modifications.

    Also, you can create a naming convention for the  new tables in the utility database, like TableName_TicketNumber, for high traffic tables that get more frequent modifications.

    I don't have them save into the same database because then you end up with a database with all sorts of cruft.


  • dragonharper

    SSC Rookie

    Points: 36

    I've used a similar method many times.  All it takes is trashing a database once to learn the value of writing the where clause in a select statement before updating or deleting.  If it is a big change you can also back up the table with an Insert into <tblname_backup>.  Just don't for get to drop the table once the data is verified.

  • erwin oosterhoorn


    Points: 2128

    Depending on the table size and the amount of data changed/deleted/updated I use output inserted, output deleted into a table in another database. When I have reviewed the changes or deletions I remove the table that I created with the backup data.

    Most of our changes are on the test system first, then BA and last on the production server, but I am aware that this is not always possible. Changes made have at least another pair of eyes while making the changes as well.

  • Jeff Moden

    SSC Guru

    Points: 996807

    Just my 2 cents...

    I've always done updates the way you've posted.  As some have said, it's saved my keester more than once.

    I also hammer (in a mentor/nice way) the method onto the Devs and they get it.  For those that worry about having both Rollback and Commit being commented out, I'll take the chance there for several reasons...

    1. We don't actually let ANYONE (including me, the DBA) do any kind of data updates in production "directly".  We enforce the rule that there MUST be a script to do the job and that it MUST be fully auditable, which means there must be a ticket that covers the action, it must have gone through the Dev, QA, UAT gauntlet (and that can be done very quickly for urgencies/emergencies), and the script MUST ALWAYS have a hard-coded expected row count (which can sometimes be a close-enough value for rapidly changing tables).
    2. The hard-coded expected rowcount is required for several reasons.  If first show due auditable due diligence on the part of the Developer and the whole process.  It also forces people to pay attention during deployment to production because another auditable process we rigidly follow is that NO ONE IS ALLOWED TO DEPLOY THEIR OWN CODE TO PRODUCTION, PERIOD!  Except for the addition of indexes, that includes me... the DBA.
    3. The biggest reason for all of this, especially the hard-coded expected row counts, it because MS and SQL Server have screwed us more than once.  The worst example I saw was back around 2004 when a really simple UPDATE had a WHERE clause that was supposed to limit the update to where a column had a NULL value.  It worked correctly in Dev and Staging.  In prod, SQL Server ignored the WHERE clause (it turned out that there was actually a CONNECT item on the problem).  Instead of the code updating a little over 1,000 customer's data, it updated the data for all 1.2 million customers.  The DBA never caught it because there was no indication of what the rowcount should be during the run and prior to the commit and the DBA simply doesn't have the time to review every piece of code during deployment (we also fixed that after).  So the DBA committed the code because no errors occurred.

      The error was discovered the next morning... after the reporting server (which has copies of all the production tables) was updated.  They went to do a restore because the damage was propagated by the nightly runs... yeah... that's when they also found out that backups had been silently failing for about 3 weeks.

      It took 40 people more than 10 days to get us back into business by reloading 3 weeks of files in the correct order, doing the processing on them, making a shedload of manual entries from source documents, etc, etc, and it was never 100% correct for about 6 months.

    A lot of things changed at that company because of that episode including them suddenly following the full development and deployment life cycle that I'd been trying to pound into their heads for a couple of years.  Most importantly, hard-code rowcounts for updates along with manual Rollback/Commits were always required because of the failure we discovered (and was documented in a CONNECT item) clearly demonstrated that we couldn't rely on SQL Server for such things.

    And, I'll gladly "take the hit" when a Developer sometimes forgets to do a Rollback/Commit on the Dev box.  That causes a whole lot less damage (none) than a UPDATE going awry, even in Dev.  I'll have some fun with a Developer that does sometimes forget but it's always done in a friendly manner and I NEVER "come down hard on" a Developer that has done such a thing.

    Shifting gears a bit, we're starting to join the "Big boy's club".  We have several databases teetering on the 1TB mark and two teetering on the 2 TB mark and several tables in the 100 to 300 GB range.  I've made it so that some of these larger tables live in the own databases and are referenced from the main database by synonyms.  Some of those tables have actually been split to multiple "sub-tables" using one form of partitioning or another and some of the tables have actually been split to separate databases using partitioned views.  Of course, the whole reason for that is to be able to very quickly do "get back in business" partial restores of one form or another.  Of course, I've also been able to make some of the legacy partitions (coming up to 120 monthly partitions on one table that we have to "keep the data forever" on) READ ONLY, which also has the benefit of greatly decreasing the time and space it takes to do nightly full backups.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • gary.mazzone

    SSCarpal Tunnel

    Points: 4254

    We backup the data we are going to change to a DB for that purpose only. We back up the key and any field we are going to modify, we name the backup table for the Ticket we are working from.  We can use that for rolling back the data.  We also have a SQL agent job that we run weekly that drops tables that are in that database that were created more that 6 months ago.

  • Miru Seshadri

    SSC Enthusiast

    Points: 198


    It is a good point to write out the updated/Deleted in XML format (or a common format) into an audit table to track the changes at a later date. This will work fine for smaller changes.

    Typically, when I run a script, I 'set implicit_transactions on' as the first thing in the script. After the DML, changes can be committed or rolled back depending on the number of records updated. This will work well where there is separation of duties, where developer cannot run updates in production.

    For a major change, I try to take a back up of the table, or backup a subset based on the predicate used for update.


  • heb1014

    Hall of Fame

    Points: 3790

    Before making any data change, I backup the table.  Always - no matter how "trivial" the change.  I keep these table backups for a period of time before dropping them.

    I also like Temporal Tables, but I'm not sure how well that solution would scale when needing to handle thousands of tables.

  • NJ-DBA


    Points: 13832

    Where I work, I encourage people to never write a block of code which has an OPEN TRAN without a ROLLBACK or COMMIT.  So instead of this:

    SELECT * FROM dbo.account WHERE account_code='4c'



    UPDATE account SET active_yn =0 WHERE account_code='4c'


    --rollback tran

    --commit tran

    I would have this:

    SELECT * FROM dbo.account WHERE account_code='4c'



    UPDATE account SET active_yn =0 WHERE account_code='4c'


    rollback tran  --change to commit if you really mean it

    --commit tran


    The reason is that on more than one occasion script executors have mistakenly forgotten to uncomment either option- the rollback or the commit- and leave a transaction hanging out there chewing up the version store, tlog and in the worst cases blocking.  Better to just rollback by default.

  • dbrinton 5101


    Points: 1

    I like to teach our younger DBA's and Developers is that UPDATE and DELETE statements are simply SELECT statements with an added feature. My favorite use of this idea is to change my SELECT statement into the UPDATE/DELETE instead of writing a whole new copy of that query below. For Example:

    --Start with this
    SELECT *
    FROM dbo.Account a WHERE account_code='4c';

    --Run the above statement and if it returns only the rows
    --you want to change then add the UPDATE/DELETE statement
    --along with the TRAN pieces for added security

    --Edit above statement to become this
    UPDATE a
    --SELECT *
    FROM dbo.Account a WHERE account_code='4c';

    --commit tran
    --rollback tran
  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 78 total)

You must be logged in to reply to this topic. Login to reply