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.
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
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
Setup will commence, and you should have your server installed.
Graphical Administration Toolhttp://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.
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:\...\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:\...\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. Type ‘go’ and hit return to execute the commands you have typed 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 authenticate 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>EXEC sp_grantlogin ‘Test’
1>EXEC sp_grantdbaccess ‘Test’, ‘database_name’
1>EXEC sp_addlogin ‘test’,’hello’
1>EXEC sp_password ‘OLDPASS’, ‘NEWPASS’,’Test’