The DBA Whoops

• Adam Machanic

  SSCoach

  Points: 15370

  More actions

  June 17, 2006 at 9:31 am

  #644467

  There are a couple of ways:

  A) Restore a backup plus tran logs to a different database and copy the data from there

  B) If you're running SS2005 and have a database snapshot around, get the data from there

  --
  Adam Machanic
  whoisactive

• Jim P.

  SSCrazy Eights

  Points: 8725

  More actions

  June 19, 2006 at 9:10 am

  #644634

  I just did a whoops the other day. We have a DB that we load data monthly and then have a "As_Of_" tables to keep the historical data.

  I forgot about them when I wanted clear the current tables quickly to load this months data. I ran the command sp_MSforeachtable "TRUNCATE TABLE ?"

  Not a good idea. Luckily no one was looking at the historical data and I had a good backup. So it was just a matter of running insert queries from the restored DB back into the production DB.

  
  
  ----------------
  Jim P.

  A little bit of this and a little byte of that can cause bloatware.

• Andrew Murphy-408015

  Newbie

  Points: 7

  More actions

  May 9, 2007 at 8:29 am

  #705241

  chopeen, Newbie,

  I developed a similar template which has saved me repeatedly, with one important extra:

  begin tran

      if (@@trancount > 0) begin

          -- do something here

      end

      

      if (@@trancount > 0) begin

          -- do more work here

      end

     

      <etc>

  rollback

  -- commit 

  When testing I can run the whole script safely; when I am ready to finalise the changes I run everything up-to-but-excluding the rollback, then run the commit manually.  What makes it so much safer is that if I accidentally run the whole thing then the rollback undoes all the damage, and if there's an error anywhere along during the "final" phase then the if () statements stop anything being run outside a transaction by accident.

  Hope this saves you lots of grief,

  Andrew