How to Recover Database without Backup

Question

How to Recover Database without Backup

August 20, 2015 at 1:28 am

This was removed by the editor as SPAM

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

praveen_manyam (7/21/2009)
Well you got the link,,, i did'nt, i got tht from my inbox from one of my friends

You should still acknowledge that what you're posting is not your own work. Even if it's just "A friend sent me this, don't know where it came from, think it may help" Without that it will be assumed that the script is your original work.

btw, it took me maybe 30 sec to find that link with google.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 2

We have a 3rd party product called Apex SQL Recover. You can recover dropped tables w/o any backups. However, (As Jeff Modem stated) it reads the disks and the longer you go, the possibility exists that some data got overlaid.

Tim White

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

crickwilli (8/20/2015)
Get more effective safe and secure software, you can download third-party software, Kernel for SQL Database Recovery Tool. This software easy to recover complete database objects available in .MDF files such as tables, views, stored procedures, triggers, indexes, primary keys, unique keys, foreign keys and etc. That software supported all update version MS SQL Server 2014/2012/2008 R2/2008/2005.

The problem is that the OP dropped the database which deletes the .MDF file, so there's no .MDF file to do this with. The .MDF file would first have to be undeleted at the OS level and the chances of that happening go to near zero very quickly on an active system because the disk space where the deleted file lives can be quickly overwritten.

I would also imagine that the same holds true for individual objects that have been dropped. The parts of the MDF file that contain dropped objects and will be overwritten, sometimes in an instant.

My point is that if you don't have backups, don't plan on being saved by such tools.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

I guess the real questions at this point is not "how to I recover a dropped database?" but rather "how do I recover a deleted file?".

How Volume Shadow Copy Service Works

https://technet.microsoft.com/en-us/library/cc785914(v=ws.10).aspx

ShadowExplorer

http://www.shadowexplorer.com/

19 Free Data Recovery Software Tools

http://pcsupport.about.com/od/filerecovery/tp/free-file-recovery-programs.htm

Hopefully there hasn't been much action on the server, since the database has been offline. However, the more time that passes, the less chance you have for recovery of the .mdf file. Each write to disk can potentially overwrite portions of your database files. So, install and run the file restore software from an external drive, and restore the files to an external drive.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

I guess the real questions at this point is not "how to I recover a dropped database?" but rather "how do I recover a deleted file?".

How Volume Shadow Copy Service Works

https://technet.microsoft.com/en-us/library/cc785914(v=ws.10).aspx

ShadowExplorer

http://www.shadowexplorer.com/

19 Free Data Recovery Software Tools

http://pcsupport.about.com/od/filerecovery/tp/free-file-recovery-programs.htm

Hopefully there hasn't been much action on the server, since the database has been offline. However, the more time that passes, the less chance you have for recovery of the .mdf file. Each write to disk can potentially overwrite portions of your database files. So, install and run the file restore software from an external drive, and restore the files to an external drive.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Ken McKelvey SSCoach Points: 18842 More actions · Answer 6

This will not help the OP but he may want to consider it in future.

I have a non-production server where the users have a habit of dropping DBs!

I have saved mayself a lot of time by having hard links to the database files in a hidden directory.

eg

C:\>MkLink /h C:\Hidden\MyDB.mdf C:\SQLData\MyDB.mdf

C:\>MkLink /h C:\Hidden\MyDB_log.ldf C:\SQLData\MyDB_log.ldf

If MyDB gets dropped, then all I need to do is:

C:\>MkLink /h C:\SQLData\MyDB.mdf C:\Hidden\MyDB.mdf

C:\>MkLink /h C:\SQLData\MyDB_log.ldf C:\Hidden\MyDB_log.ldf

CREATE DATABASE MyDB

ON (FILENAME = 'C:\SQLData\MyDB.mdf')

,(FILENAME = 'C:\SQLData\MyDB_Log.ldf')

FOR ATTACH;

Eric M Russell SSC Guru Points: 125561 More actions · Answer 7

For users who have a habbit of dropping databases, I've found the following works too.

ALTER SERVER ROLE [sysadmin] DROP MEMBER [username];

And, if they know the password for 'SA' account, then I'll do this:

use MASTER

go

alter login [sa] DISABLE;

go

alter login [sa] with name = [sa_bak];

go

create login [sa] with PASSWORD = '...', DEFAULT_DATABASE = master;

go

use Accounting;

go

drop user [sa];

go

create USER [sa] for login [sa];

go

exec sp_addrolemember 'db_datareader', 'sa';

exec sp_addrolemember 'db_datawriter', 'sa';

go

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Eric M Russell (8/20/2015)
Hopefully there hasn't been much action on the server, since the database has been offline.

Well, considering that it has been 6 years now since the "accidental drop", I guess there has been a lot of action on the server. 😀 :hehe:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125561 More actions · Answer 9

Luis Cazares (8/20/2015)
Eric M Russell (8/20/2015)
Hopefully there hasn't been much action on the server, since the database has been offline.
Well, considering that it has been 6 years now since the "accidental drop", I guess there has been a lot of action on the server. 😀 :hehe:

Hey, we're just practising Case Law here. 🙂

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

mpowell33 Newbie Points: 1 More actions · Answer 10

Can anyone restore a WP DB to a point before it was overwritten?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 11

You'll get a lot better response posting that question to the main forums. The only people who will see are those who responded to this six year old thread.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning