Working with Microsoft SQL Server MDF LDF Files

This article is about .MDF and .LDF files. These files are created by Microsoft SQL Server, and if you have some of these, and you want to view their contents read on.

The files I had originated from a Microsoft SQL Desktop Edition 2000 (MSDE) server. I’m assuming these instructions will work for other versions as well.

Download MSDE2000

You can download the 42MB MSDE 2000 Release A file from here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en

Install MSDE2000

If you run the file you saved it will ask you to choose a folder to extract the setup files to. Please select a folder, or just click next. The files will unpack.

Open a command prompt from Start ->All Programs -> Accessories -> Command Prompt

It will show you something like this:

Microsoft Windows XP [Version 5.1]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\username>

Type the following in and press enter:

C:\Documents and Settings\username> cd C:\the\folder\you\extracted\your\files\to\goes\here
C:\the\folder\i\extracted\my\files\to> setup.exe SAPWD=YOUR_PASSWORD_GOES_HERE
This is the type of window that should show next
This is the type of window that should show next

Setup will commence, and you should have your server installed.

Graphical Administration Tool

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959

I tried this, but couldn’t get it to work. I wasn’t using IIS. Good luck.

Command Line Administration using OSQL

OK back to basics. osql.exe is in this folder:

%ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\

By default this is:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\

Open a command prompt and cd into that directory:

C:\SQL Server\80\Tools\Binn\>osql /?
usage: osql              [-U login id]          [-P password]
etc

Read the stuff that it prints out here.

The -E’ switch means trusted connection, so we’ll use that to login

C:\SQL Server\80\Tools\Binn\>osql -E

1> sp_help
1> go

A whole load of text will fly by. This is the output of the sp_help command. As you see, you use ‘go’ to execute the commands you type in at the prompt.

Attaching the MDF/LDF Files:

To view your MDF/LDF files  you need to attach the database files to the server. The commands are as follows:

1>EXEC sp_attach_db @dbname = N'CHOOSE_DATABASE_NAME_AND_TYPE_IT_HERE',
1>   @filename1 = N'C:\YOUR_FILE.mdf',
1>   @filename2 = N'C:\YOUR_FILE.ldf'
1>go

To view info on the databases currently attached, you can use these commands:

1>select * from sysdatabases
1>go
1>select * from INFORMATION_SCHEMA.TABLES
1>go

You can now use SQL statements as follows:

1>USE YOUR_DATABASE_NAME
1>select * from YOUR_TABLE_NAME
1>go

If you want to output the result of your SQL query in a file use:

C:\...\Binn>osql /E /d YOUR_DB_NAME /Q "select * from YOUR_TABLE_NAME" -o outputfile.txt

If you want to do it using a host/username/password to login:

C:\...\Binn>osql /U sa /P YOUR_PASSWORD /d YOUR_DB_NAME /S your.server.name.or.ip.address /Q "select * from YOUR_TABLE" -o outputfile.txt

Some more useful commands:

1>sp_helpdb mydb
1>go

1>EXEC sp_grantlogin ‘Test’
1>EXEC sp_grantdbaccess ‘Test’, ‘database_name’
1>EXEC sp_addlogin ‘test’,’hello’
1>EXEC sp_password ‘OLDPASS’, ‘NEWPASS’,’Test’

Have fun.

Advertisements

One thought on “Working with Microsoft SQL Server MDF LDF Files”

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