March 29, 2010 at 8:43 am
One of friends customer has deleted mdf file when server is down, is it possible to recover with the help of LDF and las available back of february 27(last month).
if more details needed kindly post i can ask and reply immediately.
Regards
Durai Nagarajan
March 29, 2010 at 8:52 am
Only if the database is in full recovery model, there have been no log backups since the February backup (or if there have, you have them), the log has not been truncated since the February backup (switch to simple recovery/explicit log truncation), and the DB is still attached to SQL (you can still see the database if you query sys.databases)
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
March 29, 2010 at 9:24 am
Hi,
Kindly explain me individual steps so that i can help them.
sorry for not undetanding it from your post.
They are taking backup by right click in the database default i hope full backup so is it possible with feb 27 full backup and 2 days back LDF and restore with all data 2 days before.
Thanks in advance.
Regards
Durai Nagarajan
March 29, 2010 at 9:39 am
First and most important question.
Was the database in full recovery model?
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
March 29, 2010 at 10:11 am
Hi,
yes it is a full backup
Regards
Durai Nagarajan
March 29, 2010 at 10:32 am
Full backup is not the same thing as Full Recovery Mode.
Right click the database, go to Properties. Check the Options page in the popup window. Does the second line (Recovery Model) say Simple, Bulk-Logged, or Full?
March 29, 2010 at 1:38 pm
durai nagarajan (3/29/2010)
yes it is a full backup
I'm not asking what the backup was.
What recovery model was the database in? Simple, bulk logged or full recovery?
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
March 29, 2010 at 1:44 pm
Brandie Tarvin (3/29/2010)
Right click the database, go to Properties. Check the Options page in the popup window. Does the second line (Recovery Model) say Simple, Bulk-Logged, or Full?
That may well fail since the mdf is missing. A query of sys.databases (for recovery_model_desc) should work, as would restoring the backup onto another server and checking the database properties there.
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
March 30, 2010 at 2:06 am
Hi,
They have restored and found the recovery model as Full recovery.
sorry for not understanding it properly.
Thanks
Regards
Durai Nagarajan
March 30, 2010 at 2:40 am
Ok, great.
Were there any log backups taken between the time of the full backup and the time that the mdf was deleted?
Were there any explicit log truncations? (switch to simple recovery or explicit log truncation)
You can check the SQL error log for both, it will record log backups, it will record switches of recovery model and the use of backup log... truncate only.
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
March 30, 2010 at 3:32 am
No log backup taken. No truncation also done.
Friends customer is non technical guy so hope nothing is done other than deletion of MDF file when server is down.
They have copied the LDF file on mar mid around 15 and kepping it like a backup. Current LDF also available now.
Regards
Durai Nagarajan
March 30, 2010 at 3:38 am
durai nagarajan (3/30/2010)
No log backup taken. No truncation also done.
Are you absolutely sure? Did you check the SQL error log?
Full recovery and no log backups will result in a transaction log that's growing out of control. It's not a common, long-term state because of the ever-growing log file.
Is SQL still running, is the database (the original, with the deleted mdf and current ldf) still visible within SQL? (if you query sys.databases, does that DB name come back?)
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
March 31, 2010 at 8:13 am
ApexSQL has two products, Log and Recover, that can completely REDO/UNDO stuff from a full backup and a tlog. This is as others state dependent on the database being in FULL recovery mode, which I think I did see.
Disclaimer: I have a close relationship with Apex, use their products and recommend them to my clients. Also, if you care to you can mention TheSQLGuru sent you you will get a discount and my daughter will get a few coins for her college fund.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2010 at 8:31 am
Is it possible the server team could go back and restore the .mdf from their backup procedures?
I would check/ask if they have "volume shadow copy" enabled on the server. If so they can point to an older version of the folder containing the .mdf.
Also someone could try a simple freeware recovery tool, like "Undelete Plus" which can scan for items that have been cleared from the recycle bin. http://download.cnet.com/Undelete-Plus/3000-2242_4-143754.html?tag=mncol
I would try these two things first.
April 1, 2010 at 12:16 pm
chrisph (3/31/2010)
Is it possible the server team could go back and restore the .mdf from their backup procedures?I would check/ask if they have "volume shadow copy" enabled on the server. If so they can point to an older version of the folder containing the .mdf.
Yikes! Bad idea. Unless the database was offline, detached, or the services were off at the time, you can't recover a database from a server backup tape. The .mdf is exclusively "owned" by SQL and will be corrupted if you even try.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply