THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

Attaching a Database

I had someone ask me yesterday how they could get to a database used by a product that they owned, but that was installed using SQL Server Express. They didn’t have access to the database, and they didn’t know the password for the service that started Express, so they wanted to know if they could look at the data.

There are a few ways to do this, but the quickest, safest and easiest for me is to “attach” the database on another Instance of SQL Server. To understand how this works, let me quickly explain how SQL Server uses databases from a physical perspective.

SQL Server uses two types of files, sometimes lots of each type. The two basic types are Data files and Log files. SQL Server basically writes data to the Log file first, and then on to the Data files. Of course it’s more complicated than that; but this is the basic flow.

When SQL Server first starts up, it reads (among other things) the master system database. And that’s where the crux of this post comes into play. In that database are two things that we care about right now: the logical name of the databases SQL Server knows about and the physical locations for the files that database uses.

With that information in mind, here’s a simple way to transfer a database from an Instance you don’t have access to into one that you do.

There are a LOT of disclaimers here.
First: don’t do this on a database you don’t own, and own all the data for. That’s illegal.
Second, don’t do this with a large database – the possibility of multiple files in the filegroups on a complex database is high, and you might not get them all.
Third: you have to be running the same (or higher) version of SQL Server on the destination server, and the same (or higher) edition of SQL Server on the destination server.
Fourth: you’ll have to stop the source Instance. If you have users on that system, you don’t want to stop it without consulting everyone first.

OK – with all of that stated, you first need to stop the “source” instance. Let it shut down completely and normally. This will (theoretically) commit all of the transactions from the logs to the databases.

Now copy the .MDF and .LDF files for the database you want to a location on your test Instance. If you don’t know what these are, you can try and copy what you think are those files, but don’t copy any of the system databases from the source system to your test Instance!

Start the source Instance back up and let the users back into the application.

On the test system, write-down the location where you copied both of those files (assuming there was one of each – it get’s trickier when there are multiples).

Open SQL Server Management Studio.

Right-click the “Databases” node in Object Explorer and select “Attach…” from the menu that appears.

Click “Add” to find the MDF and LDF files, and then “OK” to name the database.

You have two other options as well. You can use the sp_attachdb stored procedure (detail here - deprecated) or the newer CREATE DATABASE…FOR ATTACH statement (article on that here).

In any case, you now have the database, and you now “own” it in that test Instance. The logins will all be incorrect, since the names in the database security don’t match the new Instance’s master database. But you do have that data.

Published Tuesday, January 26, 2010 8:03 AM by BuckWoody
Filed under: , ,



Vikas Rajput said:

Good article Buck. Though its definitely a good idea, but at same time makes me think on the security aspect of it. Imagine someone getting access to data-log files somehow and its all up in open. Might sound very basic, but think its a risk. Any views on how it can be safeguarded. Is their some way that even if data-log files are shipped across, there is some level of security check before data gets actually usable. Am thinking on lines of security certificates, but not too sure on that. Would appreciate your view on same.

January 26, 2010 6:57 PM

Buck Woody said:

Exactly correct - which is why we created "Transparent Data Encryption". It would prevent exactly this scenario.

January 26, 2010 8:03 PM

Peter said:

Another method is to follow  and then create an additional sql logon with sysadmin rights.

January 27, 2010 5:36 AM
New Comments to this post are disabled

About BuckWoody

This Blog


Privacy Statement