Attaching .MDF File Without .LDF file in SqlServer – An Error-Fix Approach

I was trying to attach a .MDF file without a .LDF which i 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
 GO

Problem “Access Denied”

Initially,I have 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)

0r

Give full permission on the .MDF file (Right Click->Security->Add everyone->Assign Full permission)

This 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 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->Select the instance on which you are running the attach query

  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.

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
RECONFIGURE

For 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

Advertisements

2 thoughts on “Attaching .MDF File Without .LDF file in SqlServer – An Error-Fix Approach

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s