6

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

4
  • is this a production database? restore from your backup. stage? well, couldn't you download a new copy from production?
    – David Hedlund
    Jul 13, 2010 at 6:59
  • 4
    do you have backup?
    – Serkan Hekimoglu
    Jul 13, 2010 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, 2010 at 6:59
  • 2
    No backups? Oops.
    – Will A
    Jul 13, 2010 at 7:00

6 Answers 6

4

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.

2
  • 2
    Restore database without backup? How misleading is that... you can't...
    – gbn
    Jul 13, 2010 at 7:05
  • Hehehe... the author of the post just tried to catch attention.
    – Leniel Macaferi
    Jul 13, 2010 at 7:06
4

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.

2

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)

3
  • 3
    You may be able to undelete the files if you're lucky, tho'. Very lucky.
    – Will A
    Jul 13, 2010 at 7:03
  • @Will A: And the chances are...? :-) No backup = no restore is the simple answer
    – gbn
    Jul 13, 2010 at 7:06
  • 1
    When it comes to recovering vital data, the simple answer is never sufficient.
    – Dan
    Aug 4, 2015 at 18:27
1

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.

1

As long as nothing has been written to that drive since the deletion, they are still there. Just the allocation in the file system has gone. Try https://www.ccleaner.com/recuva/download - Worked for me :-)

0

if the transaction that you have deleted accidentally were performed within a Begin transaction and did not committed, can be recovered by using fn_dblog() function. you can apply the below command for viewing your accidentally deleted data.

SELECT
    [Current LSN],
    [Transaction ID],
    [Operation],
    [Context],
    [AllocUnitName]
FROM fn_dbLog(NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS'

However, the process is too long and time consuming. this can be done easily by using a free SQL ldf viewer, which will provide you more appropriate result as well as it will recover the deleted database records in few clicks.

You must log in to answer this question.