August 28, 2008 at 11:09 am
My co-worker was trying to move a production database from one disk to another. He didn't do a backup and then he dropped a database. He doesn't have any experience/education in DB Administration. We need to help him to restore it.
I thought that db and logs are just files. When files are deleted they go to the Recycle Bin. Maybe it's a crazy idea, but would it be possible to get those files from recycle bin and restore a db from them ?
Are there any other ways to restore a database that was not backed up?
The guy may lose his job and I feel sorry for him.. though he did a dumb thing without telling anybody..
August 28, 2008 at 12:10 pm
Unfortunately there is nothing you can do once you have dropped the database. If you had detached it then you might be ok.
Are you sure there are no backups?
Very sorry to hear that this happened to you. Hope all will be well with your co-worker.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 12:24 pm
My question is why would anyone in your office let someone without any DB administration experience to handle moving a production database. Maybe the one who told that guy to move the database should be fired instead of that poor guy that had no clue what he was doing.
August 28, 2008 at 12:39 pm
My co-worker should have had guts to say "No", I don't know what I am doing and I don't want to poke around when I am in the middle of some other project and it's late at night..
Or at least keep other programmers in the loop and ask for advice. I cannot blame only our boss. It's maybe 70% boss' fault and still 30% of my co-worker. You've got to have guts to say NO.
August 28, 2008 at 12:48 pm
Yes, admitting that we don't know how to do something is critical in our work.
Ultimately there is no point in pointing fingers, just learning lessons and growing. Hopefully no one will be too hurt by this and the customer that you are working for will not be too adversly affected.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 2:08 pm
Vika (8/28/2008)
I thought that db and logs are just files. When files are deleted they go to the Recycle Bin. Maybe it's a crazy idea, but would it be possible to get those files from recycle bin and restore a db from them ?
I believe that the Recycle feature is off by default on Server Disks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 28, 2008 at 2:46 pm
rbarryyoung (8/28/2008)
Vika (8/28/2008)
I thought that db and logs are just files. When files are deleted they go to the Recycle Bin. Maybe it's a crazy idea, but would it be possible to get those files from recycle bin and restore a db from them ?I believe that the Recycle feature is off by default on Server Disks.
And I believe that if you drop the database, SQL Server will delete the files without using the recycle bin. I could be wrong, but also - if the database files were too large for the recycle bin they wouldn't end up there anyways.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 28, 2008 at 3:01 pm
Yes, it deletes the files automatically. From BOL
"Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files are offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. "
I tested this earlier hoping I was wrong because I felt so bad for the situation.....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 5:51 pm
Who made the decision not to have daily backups ? Can't just blame that on your co-worker too, can you ?
August 29, 2008 at 8:24 am
Guys,
all that you said is correct. I checked the recycle bin on the server. No db files there, in fact it's empty.
The database suppose to be setup on the disk where everything gets backed up automatically but following the written instructions (that had an error in the path!) he set it up on a different disk ( where nothing gets backed up). Database was in use for a while and then it was discovered that it doesn't get backed up.
The same evening it was discovered that programmer was told to move it to another drive asap. Yes, bosses told him/approved the procedure. The guy didn't do any manual backup before following the order, didn't tell the rest of us, never moved db in his live, and he is fearless!
I always have that fear in my guts that I'll do something wrong and I think it over and over and try it on the test db. He just goes ahead and does things and bosses tell him to do things and it's dangerous. Somehow he thought he needed to drop db before restoring it on a different disk. Maybe he mixed up drop and de-attach commands or maybe just didn't think but it's too late to know. We re-created the schema from other db but data was gone.
Yes, it should be a lesson but there is a pattern of being fearless for bosses and for him and this is no good. I hope we are safe and will keep our jobs LOL
August 29, 2008 at 8:36 am
Okay... we all get your co-worker is "fearless" and has not guts to say "no", the question here is... what did your coworker do before dropping the database? Please don't tell me the first task in his "migration plan" was "drop the target database".
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 29, 2008 at 8:54 am
Vika (8/29/2008)
Guys,The database suppose to be setup on the disk where everything gets backed up automatically but following the written instructions (that had an error in the path!) he set it up on a different disk ( where nothing gets backed up). Database was in use for a while and then it was discovered that it doesn't get backed up.
That's not how you backup a SQL database. Copying the files of an in-use SQL database to tape/other backup device is not going to work properly. You're very likely to get a suspect database if you try to 'recover' from that.
Use SQL's backup mechanism (backup database to..., or maintenance plan) to create database backup files, then back those files up to taps or whereever you put your backups.
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
August 29, 2008 at 9:18 am
You can run something like this to identify databases that have not been backed up in the last n days:
select * from master..sysdatabases db
where (not exists
(select * from msdb.dbo.backupset
where db.name = msdb.dbo.backupset.database_name
and backup_start_date > DATEADD(DAY, -2, (getdate()))))
order by name
August 29, 2008 at 9:18 am
I hope your co-worker and your boss learn a good lesson. Of course somebody never learns from mistake!!!!!! One of my former DBA kept making mistake and her boss kept backing her up. When I pointed out her mistakes, her boss complained to my boss that I was a difficult person to work with!!!!! Oh well!!!!
There is no backup of that database ever? Not even a few months ago?
August 29, 2008 at 9:34 am
It amazes me that the lack of backups is so common. I will be starting a new job soon, and after finding my desk, the first think I will do will be to find out their backup procedures & verify their backups. I'll run that little script above, find out if/when the backup files get written to tape etc etc ......
Accidents happen, servers crash, things break, corruption occurs, users want to restore data from odd-ball times ..... Sometimes a specific recovery is not possible, but someone in the organization needs to make sure that the basics are covered.
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply