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

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

Solution 1:

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.

Solution 2:

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.