Thursday, November 29, 2007

Recover your DB from Log Files in MS SQL DBMS

Hi Folks:

I got a call from one of my friends "Fady" regarding one of the guys dropped his tables without taking a backup of his DB.

This is my Support and answer to him.

"
Dear My Firend Fady,

Regarding your question yesterday, for that person who Dropped his tables, anyways this email has good news for him, as I told you he can recover the DB from its log file, simply pass these Urls to him, to recover your DB from Log File.

Urls:

http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html

http://www.apexsql.com/sql_tools_log.asp

"

Hope this help you guys.......


Regards,
Moustafa arafa

4 comments:

.NET King said...

I love this scenario becasue I have faced it for a couple of times. Here are some questions that tell you exactly if you can recover.
1. Do they have any full backup of the database before the table deletion? No matter how old the full back up is. Even if the backup is done just after the database creation without the deleted table, it will change the result dramatically.
2. What is the database recovery mode. If it is Simple recovery the chance of recovery is 0%. Otherwise we may find a way to recover it.

The recovery is pretty simple.
Step 1: Make a transaction log back up of the same database with "NO TRUNCATE" option
Step 2: Restore the very old full back up that you may have with "NO RECOVERY" option
Step 3: Restore the new Transation Log backup that you have made after table deletion. RESTORE LOG has an option of "STOP AT". This gives you the chance to set the date and time to the moment before the table is deleted. In this way you can move back in time.
If you have any other concern just send me a message to http://www.dotnetking.com/ContactUs.aspx and we may resolve it.
Alireza (Toronto)
http://www.dotnetking.com

Alex said...

For work with sql files advise use-recover mssql,also tool is free,it can help with this problem and retrieve the data, that was considered to be lost,mwill extract housekeeping data from the source database and preview the data, that can be recovered,this tool is a good solution to recover data from corrupted databases in MS SQL Server format,restore databases represent files, like any other documents, they can be easily corrupted by viruses, all sorts of malware, hard drive failures, file system errors, incorrect user actions, etc,supports both data extraction to your hard drive as scripts in SQL format and data export directly to a database in MS SQL Server format.

Moustafa El-Zoughby said...

Thanks Alex for your valuable post.

Alexis said...

Friend said me about one nice tool-recover mdf,which recover sql files.And after some time I used it and tool recovered all my data very quickly.Besides that it executed this operation for free and showed repair .mdf files of Microsoft SQL Server 2005.