Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How to recover the database which is accidently deleted using sql server 2008?

share|improve this question
    
is this a production database? restore from your backup. stage? well, couldn't you download a new copy from production? –  David Hedlund Jul 13 '10 at 6:59
4  
do you have backup? –  Serkan Hekimoglu Jul 13 '10 at 6:59
    
Unless I'm missing something, this isn't about programming, it's a data recovery question, so I'm going to say it belongs on superuser...? –  lc. Jul 13 '10 at 6:59
2  
No backups? Oops. –  Will A Jul 13 '10 at 7:00
add comment

migrated from stackoverflow.com Jul 13 '10 at 8:47

This question came from our site for professional and enthusiast programmers.

4 Answers

One possible solution (This will only work if done immediately):

  • Get some data recover software (e.g. DataRecovery, PandoraRecovery, DataRecovery, etc.).
  • Run the software to see if you can find the deleted file with hard drive scan.
  • If you can see the deleted files, restore them into a separate drive (USB or external drive).

If that does not work, then your only option is to contact some professional data recovery company.

share|improve this answer
add comment

This is going to be a little long, in hopes of fleshing the subject out a little more than the other answers.

When you drop a database, the files that underlie it are not moved to the Recycle Bin, they are actually deleted. 'Deleted' means that the directory entries that the NTFS file system uses to describe the location and length are removed and, as far as the OS is concerned, the space on the disk where those files are becomes free to use for any purpose. Other files may be written there, which would overwrite the data that you wish to "get back".

It may be possible to 'recover' the data and log files with a third-party undelete utility. The problem with that is that these utilities often will not get the file back 100% intact since data may already have been written into the area on the disk where the file was previously located. This is especially true if the system has continued to run for a while after the files were deleted when the database was dropped. (Your best, but not great, bet would have been to shutdown the system immediately after dropping the database. I'm guessing that did not happen.)

If the files aren't 100% properly restored by an undelete utility, you data and/or the internal structures of the databases files will be corrupted. SQL Server might notice this when the files are brought back online (with an attach command) and then flag the database as suspect. You'd be right back where you were, without a working database.

Even if SQL Server brings the database online successfully, this is not a guarantee that your data has not been corrupted. The first thing you should do after bringing a possibly dodgy database online with an attach command is to run DBCC CHECKDB() against it. If that command runs without finding problems, you are probably OK (and you should immediately make a backup). It is more likely that it will find corruption that can't be fixed.

If you absolutely must have the data back, there are articles around the internet talking about various kinds of heroics to try to extract as much non-corrupt data as possible out of a corrupt database. You could give those a shot, or you might be better off calling a specialist or even Microsoft (who will probably just point you at a partner of theirs).

While 'heroics' are an interesting thing to read from a technical perspective, the best thing to do is to avoid situations where you have to go through unusual means to get your data back. (This is doubly true (triply, actually) true for people who don't administer databases full time.) The simplest thing to do is to back up your data regularly, by whatever means necessary, and to test your restoration process. Disk space only seems expensive until you realize how expensive getting your data back via third-party specialists is.

share|improve this answer
add comment

Do you have a backup? If so, restore from this.

If not, you have no further options. The MDF and LDF files are deleted (see DROP DATABASE)

share|improve this answer
2  
You may be able to undelete the files if you're lucky, tho'. Very lucky. –  Will A Jul 13 '10 at 7:03
    
@Will A: And the chances are...? :-) No backup = no restore is the simple answer –  gbn Jul 13 '10 at 7:06
add comment

This link answers some common questions just related to this:

SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

The tagline is:

You need complete backup to rollback your changes. If you do not have complete backup you can not revert back. Sorry.

share|improve this answer
2  
Restore database without backup? How misleading is that... you can't... –  gbn Jul 13 '10 at 7:05
    
Hehehe... the author of the post just tried to catch attention. –  Leniel Macaferi Jul 13 '10 at 7:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.