December 15, 2010 at 10:24 am
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!! 🙂
December 15, 2010 at 10:26 am
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.
December 15, 2010 at 10:33 am
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.
December 15, 2010 at 10:40 am
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
December 15, 2010 at 10:58 am
What? You're allowing a developer to touch production data? Yikes!
Rob Schripsema
Propack, Inc.
December 15, 2010 at 11:08 am
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.
December 15, 2010 at 11:13 am
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
December 15, 2010 at 11:16 am
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:
December 15, 2010 at 11:25 am
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.
December 15, 2010 at 11:28 am
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
December 15, 2010 at 12:10 pm
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
December 15, 2010 at 2:13 pm
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
December 15, 2010 at 2:16 pm
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.
December 16, 2010 at 3:25 am
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.
December 16, 2010 at 5:32 am
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 26 total)
You must be logged in to reply to this topic. Login to reply