How to copy a Microsoft SQL Server Database to another computer ?

From this post, I'll show you how to copy a MS SQL Database in one computer to another with an example.

When you go to the Microsoft SQL Server Management Studio, it shows all the databases.

As you see in the above image, I have a database called ITA. I'll show you how to copy that.

All the databases are stored in the following location by default in your computer. 

Local Disk (C:) -> Program Files -> Microsoft SQL Server -> MSSQL10.MSSQLSERVER -> MSSQL -> DATA

(If you have changed the location when you installed MS SQL Server, go to that location.)

You need to have administrator privileges to access that location.

When you go there, as in the above image, it shows all the databases.

For each database, there are two files as in the image below.

Now I'm going to copy the database named 'ITA' . Right Click on the two files and select 'Copy'.

Go to the location where you want to paste them (eg: your Pen Drive), right click on the window and click 'Paste'.

Then the following Dialog Box appears. 

It says that I need to provide administrator permission to copy the files. Since I have, I'll click 'Continue'. 

OOPS !!! What happened ? It says that the database is opened in SQL Server so that I cannot copy it. So before copying the database, I have to detach the database from the MS SQL Server.

As in the above image, go to MS SQL Server Management Studio. In the Object Explorer, in the Databases folder, it shows all the databases. Right Click on the database you want to Detach, select 'Tasks' and select 'Detach'. Then the following window appears.

Press 'OK'.

Then your database will not appear under the databases list in MS SQL Server Management Studio. In the below image, my 'ITA' database is not appearing now.

 Then go to the location where databases are stores. 

Now you see a small padlock symbol near the database files I detached. Right Click on them, select 'Copy', and 'Paste' them in the location you want to copy them. Using a Pen Drive, you can copy them to another computer. 

After copying the files, you need to Attach the Detached files. Go to MS SQL Server Management Studio. Right Click on the Databases folder and select 'Attach'.

Then the following window appears.

Click on the 'Add' button. Then it'll show all the databases.

Select the particular database and press 'OK'.Then the following window appears.

Press 'OK'.

Now the Databases folder shows my database. 

Now you have copied the database files to your pen drive. In the other computer, paste them in the location where databases are stored. Then open MS SQL Server Management Studio and 'Attach' the pasted database as I described earlier.

-Tharindu Edirisinghe-
-SLIIT 10'-