Share via

SQL Server Troubleshooting: Attaching .MDF File Without .LDF file

  • Article

Issue

Trying to attach a .MDF file without a .LDF, downloaded from codeplex using the query below

USE [master] CREATE DATABASE  Adventure ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data.mdf') FOR ATTACH_REBUILD_LOG
GGO 

Problem "Access Denied"

 Initially, placed the .MDF file in D:\It was throwing

Operating system error 5: "5(Access is denied.)"

Resolution

 

1) Close your running instance of SQL Server Management Studio (SSMS) 2)Run SSMS in administrator Mode (Right Click on SSMS ->Run As Administrator) (Recommended)/strong> 0r Give full permission on the .MDF file (Right Click->Security->Add everyone->Assign Full permission)

TThis error got resolved,but it was throwing a new error To give a try , I put the mdf in C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER\MSSQL\DATA\

Problem: Could not open DB

Could not open new database 'Adventure'. CREATE DATABASE is aborted. The database 'Adventure' cannot be opened because it is version 705. This server supports version 655 and earlier. A downgrade path is not supported.

RESOLUTION

* Option 1:*

 In order for this to work for me as soon as the file was finished downloading I right-clicked the file and selected "Properties". Then I selected the "Unblock" button within the properties window. Then and only then was I able to attach it to my SQL Server 2008 R2 instance.(Didn't work in my case)

* Option 2: *

Enable FILESTREAM 1)All Programs->SQL server 2008R2 or any other version 2)Expand Configuration Tools Folder->SQL Server Configuration Manager 3)Click on SQL Server Services->/strong>Select the instance on which you are running the attach query

http://onestopdotnet.files.wordpress.com/2012/01/sss.jpg

  1. Right-click the instance, and then click Properties.
  • In the SQL Server Properties dialog box, click the FILESTREAM tab.
  • Select the Enable FILESTREAM for Transact-SQL access check box.
  • If you want to read and write FILESTREAM data from Windows, click  Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
  • If remote clients must access the FILESTREAM data that is stored on this share, select  Allow remote clients to have streaming access to FILESTREAM data.
  • Click  Apply.

http://onestopdotnet.files.wordpress.com/2012/01/sss2.jpg

5 . In SQL Server Management Studio, click  New Query to display the Query Editor. 6. In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2
RRECONFIGURE

More Info

 Refer http://msdn.microsoft.com/en-us/library/cc645923.aspx This Worked Out pretty well for me.I was able to attach .MDF file by creating a new DataBase Adventure.  MDF-Master Data File LDF-Log Data File