Slight Problem :(

  • Hey,

    Due to a bit of a mistake, we've ended up with one of our databases in trouble.

    A developer has accidentally updated a table without a where clause, not in a transaction. The good news is that the database is in full recovery mode, the bad news is that we don't have any backups since the company I work for decided against spending any money on more disk space.

    Help!! 🙂


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Without even a transaction log file backup to go with, which I am surprised your company doesn't at the very least maintain these, you are out of luck.

    Unless the user copied the data table into a temp table.

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

  • Unfortunately not. Awesome. I'm hoping that we have a tape backup from last night, but with our Network Admin I'm hoping in vain.

    Maybe they'll listen to me from now on when I say we need more space for backups.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • It seems that the only thing you can do is get the backup from tape - if it exists. Buy your server admin a nice christmas lunch and maybe the process will go smoother.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • What? You're allowing a developer to touch production data? Yikes!

    Rob Schripsema
    Propack, Inc.

  • Rob Schripsema (12/15/2010)


    What? You're allowing a developer to touch production data? Yikes!

    Yes, well, I've spoken to the DBA (who is on sick leave) and we've decided that we'll be making them "readers" instead.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • it might take a bit of work, but this article teaches you how to read the transaction log that is in full recovery, but no backups to recover with:

    http://www.sqlservercentral.com/articles/Transaction+Log/71415/

    the author has some upcoming articles on reading updates and deletes; you could contact him directly for more info on the update portion of his example.

    there was a lot of poo-pooing on the discussions of that article on the usability of this, and i stated that this exact situation might occur....no backup or the backup had been failing, big changes, have to roll back a table. it happens much more often than people think.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • So you have a database in full recovery mode but no backups?

    What is the backup strategy you use? If you never performed any backup (including transaction log) you must have a huge log file by now... :unsure:



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (12/15/2010)


    So you have a database in full recovery mode but no backups?

    What is the backup strategy you use? If you never performed any backup (including transaction log) you must have a huge log file by now... :unsure:

    Exactly my thoughts... that might be the exact reason why they are running short on hd space.

    Our system here has 40 Gb of db files. 2 full backups + transactions only require 48 GB extra hd space... It would take 50-75% less if we were on 2008 R2 Standard and used compressions both on DB and backups.

    Event at 10$ / GB (SAN), it's pretty cheap to have that insurrance.

    For the same 500$, you could get a nice external drive on raid and never have to worry about this anymore.

  • If you do have a tape backup you can still take a log backup and do a point in time restore. Again, all hinging on the full backup existing.

    Honestly I can't imagine being in a situation like that. AND it would be wise on your part to tell management, "I told you so" at this point.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Lowell (12/15/2010)


    it might take a bit of work, but this article teaches you how to read the transaction log that is in full recovery, but no backups to recover with:

    http://www.sqlservercentral.com/articles/Transaction+Log/71415/

    the author has some upcoming articles on reading updates and deletes; you could contact him directly for more info on the update portion of his example.

    there was a lot of poo-pooing on the discussions of that article on the usability of this, and i stated that this exact situation might occur....no backup or the backup had been failing, big changes, have to roll back a table. it happens much more often than people think.

    I think this solution has some merit.

    Next step is to make sure you get some log backups.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • LutzM (12/15/2010)


    If you never performed any backup (including transaction log) you must have a huge log file by now... :unsure:

    If they're never run any backups (including full backup) the log will be small because the DB will still be behaving as though it's in simple recovery. There must be a backup before SQL starts retaining the log.

    Of course, that means there will be no log to recover this mistaken update from if that is the case.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If you have never made a full backup of the database since it was set to full recovery mode, you will not have anything in your transaction logs.

    Until you make at least one full backup, SQL Server continues to treat the transaction log the same as the log for a database in simple recovery mode.

  • Backups used to be made regularly, but due to disk space it was turned off (not by me).

    In the end, the tape disk was there, so I was able to recover everything except that day of transactions. I then wrote an SSIS package to pick up the post from that day and re-upload to a new table, then updated my original table with the correct parts of that. All in all, we lost nothing (except me, since I was at work from 8:30am to 10:30pm).

    I'm hoping that management agree that we could do with some more space to keep backups - also have been asking since I started here for a development environment but so far it has been a non-starter. Have a meeting later on today to discuss what happened and how we could have prevented it, so should be fun.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • skcadavre (12/16/2010)


    Backups used to be made regularly, but due to disk space it was turned off (not by me).

    In the end, the tape disk was there, so I was able to recover everything except that day of transactions. I then wrote an SSIS package to pick up the post from that day and re-upload to a new table, then updated my original table with the correct parts of that. All in all, we lost nothing (except me, since I was at work from 8:30am to 10:30pm).

    I'm hoping that management agree that we could do with some more space to keep backups - also have been asking since I started here for a development environment but so far it has been a non-starter. Have a meeting later on today to discuss what happened and how we could have prevented it, so should be fun.

    You can download sql express and install it on your pc. That's always a good start. If that's a no go because the DB is over 4 GB, then you can install a trial version at first while you ask them for 60$ to buy a dev version to install.

Viewing 15 posts - 1 through 15 (of 27 total)

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