Revcover Data base from .mdf file only

Question

Can we recover data from mdf file only? Secondly, for attaching the file is it necessary to keep it in the defulat SQL data folder ?

---

Regards, Syed Faizan ur Rehman, CBPM®,PRINCE2®, MCTS

Answer 1

1) You can attach database with a single data file (log file will be created)

2) No, just navigate to the needed path

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

Hi Syed,

We can use the below query(sample one) extracted from BOL.

USE master;  
GO  
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2012',
@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2012_Data.mdf'

---

Ramesh. M

Answer 3

Hi Ramesh,

  I have done the mentioned step earlier but I ended up with error, DBName can not be upgraded it is in read only.

I am trying to restore sharepoint data file. 

---

Regards, Syed Faizan ur Rehman, CBPM®,PRINCE2®, MCTS

Answer 4

It sounds like you are attaching the mdf on a different version of the server. You will need to reattach it on the same version of the server. You may have mixed results attaching a single file mdf as in some cases the log file will be necessary. You may need to use database recovery software to extract the data.

Answer 5

Hi Syed,

Are you able to detach the database? before you are trying to restore it. Are you able to move that specific .mdf (cut and move) file from one drive to other drive

Thanks

M.Ramesh

---

Ramesh. M

Answer 6

My situation was that the SharePoint server and Database server crashed... only mdf file are available. I need to attach the mdf file to get versions and then proceed with the rebuild.

---

Regards, Syed Faizan ur Rehman, CBPM®,PRINCE2®, MCTS

Answer 7

Do what Hilary suggested. Try to attach it on the same version, as a starter. Now you at least have a chance for it to work. If you are lucky, there is no recovery work required and the attach will succeed. If you are unlucky, then there *is* recovery work that need to be performed and the attach will fail - meaning that you will revert to your most recent clean backup.

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 8

check if the .mdf file is also corrupted or not. you can detach and reattach the .mdf file on same version or on different version and if the file is got corrupted then you can try manual methods for its repairing or use sharepoint recovery software to repair corrupt sharepoint database. After recovering the data from .mdf file you can attach it by giving full permission to .mdf file.

 

Answer 9

Hi Syed,

Adding to other posts, in your scenario, please ensure that SQL Server Service account has full access on the mdf file that you placed in other folder, you can check SQL Server Service account in SQL Server Configuration Manager. And ensure that the mdf file is not read-only by checking its properties.

In addition, right-click SQL Server Management Studio and choose “Run as Administrator”, then execute the attach script.

Thanks,
Lydia Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 10

the SQL Server user is local Administrator and sysadmin on the server, it gives read only issue. 

The file is recovered from one node of the Cluster SQL server. 

---

Regards, Syed Faizan ur Rehman, CBPM®,PRINCE2®, MCTS

Answer 11

Make sure that the file isn't read-only. Also make sure that that the service account for sQL Server has full permissions on the file (not the user who is running SSMS). And also make sure that SSMS is started as Administrator (bypass UAC).

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 12

CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO

or

CREATE DATABASE TestDb ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO