Sql Server How to Read a Record in Visual Basic.net

Let's say someone wants to give you their SQL Server database. Maybe they desire y'all to manage it, troubleshoot it, or larn from it. So they hand you a .MDF file and  .LDF file and tell you that you lot demand SQL server 2008 R2 to open them. Then you install SQL Server 2008 R2 Express (which, to your relief, is free). You open SQL Server Direction Studio(SSMS), and yous try to open up the .MDF file. Y'all get the post-obit mistake:

There is no editor available for 'C:\Temp\MyDatabase.mdf'. Make sure the application for file type (.mdf) is installed.

The Solution:

.MDF files are SQL Server database files and .LDF files are the associated log files. But you can't really "open" them. Instead, you have to "attach" to them. Once yous attach, you'll encounter the database in the object explorer of SSMS.

If you're thinking, "Why wasn't the Backup and Restore process used instead of passing around these database files?" It's a valid question, just it doesn't help the reader staring at .MDF and .LDF files with no clue how to access them. However, if you need to movement a database around, you lot should do a search on "fill-in restore attach detach sql server" (without the quotes) in your favorite search engine. You'll get lots of opinions to help y'all decide the best arroyo.

How to Attach in a Perfect World:

Click here to watch a video on this...

There's a couple of dissimilar methods. I'll give you the "like shooting fish in a barrel" style through SQL Server Direction Studio (SSMS) and and then point you to other methods using T-SQL queries.

1. Launch SSMS.
2. Connect to your SQL Server Instance.
3. Correct-click on Databases in the Object Explorer.
4. Click Adhere.
5. In the Attach Databases window, click the Add together push button.
6. Navigate to the directory containing the .MDF and .LDF files.
7. Select the .MDF file, and printing OK.
8. Press OK again to attach the database.

You should come across the database appear in the Databases node (press F5 if you lot don't). You lot can now explore the data using SSMS. These same steps are documented in the MSDN here. I'one thousand just summarizing to make it easier for you.

There are two ways to achieve the aforementioned thing in a T-SQL script. First, you lot can apply the sp_attach_db stored procedure. This is shown here. Or y'all tin use the CREATE DATABASE command with the FOR Attach argument.

And then, the World isn't Perfect

If your database is successfully attached, so stop reading and become utilize it. But if you lot come across any errors or unexpected behaviors, I'll try to share some troubleshooting suggestions.

Beginning, you may get a generic error like the post-obit..

An fault occurred when attaching the database(s). Click the hyperlink in the Message column for details.

You might be asking, "Where's the hyperlink?" I'll show yous in the next screenshot (circled in red), and it will likewise be our first example.

one. Access Denied:

Adhere database failed for Server ###. (Microsoft.SqlServer.Smo) Unable to open the physical file "####". Operating system fault five: "5(Access is denied.)". (Microsoft SQL Server, Fault 5120)

This access denied error could take a few different causes. Basically, you don't have the required access to the .MDF or .LDF files. This tin happen if you go the file from someone else. When they disassemble the database file, the file permissions are changed to give simply that user total control. Also, fifty-fifty if you lot see that the file has full command for the Administrators grouping, it may non be enough. Remember in Windows 7/Vista, those permissions are oftentimes only realized when you're running an application equally an Administrator.

And then here are several choices to fix it:

1. The easiest solution is to close SSMS and and so run it every bit an Administrator. Perform the attach as an Administrator, and information technology's likely going to work.
2. Some other solution is to explicitly grant full control to the .MDF and .LDF files to your user business relationship. This can be done by correct-clicking the files, selecting Properties, and modifying the Security tab.
3. A final solution is to re-create the files to the default directory for your other database files. To find out what that is, you can use the sp_helpfile process in SSMS. On my machine it is: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Data. By copying the files to this directory, they automatically go permissions practical that will allow the attach to succeed.

2. Admission Denied Variation ii

There is another variation of the Admission Denied message that has a simple solution:

Failed to recall data for this asking. (Microsoft.SqlServer.Management.Sdk.Sfc) CREATE FILE encountered operating arrangement error 5(Access is denied.) while attempting to open or create the physical file '####'. (Microsoft SQL Server, Error: 5123)

This error means that the file is already opened exclusively by another awarding. The nigh likely cause is that this database file is already attached to an instance of SQL Server. Double-check your list of databases to see whether information technology is already in the list. It'southward also possible for applications to use .MDF files directly with a feature chosen User Instances. If an application is using a .MDF file in this fashion, then information technology would have to close before you could attach the to that database file.

3. Database is Read-simply

This is not and so much an error as an undesired issue. When yous load the database, it is in a read-just land. You tin tell by the greyness shading of the databasee icon (not to mention the "Read-Only" label next to it).

The most likely cause is that the .MDF file yous fastened to is read-only. Just right-click on the .MDF and .LDF files, select properties, and so uncheck the Read-just check box.

4. Unable to Downgrade

The next error happens when yous endeavor to adhere a database of a college version of SQL Server (similar SQL Server 2008 R2) to a SQL Server Example of a lower version (like SQL Server 2005).

Attach database failed for Server '####'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The database '####' cannot be opened because information technology is version 661. This server supports version 655 and before. A downgrade path is not supported. Could not open a new database '####'. CREATE DATABASE is aborted. (Microsoft SQL Server, Fault: 948)

This has never been supported. You tin can attach a database of an older version of SQL Server to a newer version, but you can't get the other management. If you want to verify the version of SQL Server you're running, encounter this mail service.

A Parting Give-and-take on Disassemble:

This post is but covering the scenarios of, "How practise I open this .MDF file" and "Oh, I have to adhere it, why am I getting errors?". Simply how were these files obtained in the first place? The reply is that at that place is a respective Detach feature in SQL Server. You lot can find information technology by right-clicking on the database in SSMS, selecting tasks, and looking for Disassemble. Before you practise this, though, run the sp_helpfile procedure in a query window in the context of your database. This will tell you *where* the detached .MDF file will be located. After you've detached the file, yous can attach it to any SQL Server instance. Although this post was most Attach, I remember it's worth mentioning this for completeness.

blankenshipcausubtlig.blogspot.com

Source: https://learningsqlserver.wordpress.com/2011/02/13/how-can-i-open-mdf-and-ldf-files-in-sql-server-attach-tutorial-troublshooting/