Monday, December 6, 2010

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'-

44 comments:

Muhammad Azeem said...

This is a nice article..
Its very easy to understand ..
And this article is using to learn something about it..

c#, dot.net, php tutorial

Thanks a lot..!

Tharindu Edirisinghe said...

Thanks for the comment. Your link seems to be having good learning material. I'll check it further.

Dharshana said...

Hello tharindu, in this manner can we attach a database created on Ms Sql Server Management Studio 2008 (express edition) with the Ms Sql Server Management 2005 (express edition) or vice versa.

Tharindu Edirisinghe said...

@Dharshana
I've attached a 2005 database to a 2008 and it worked. I'm not sure whether a 2008 database would support 2005.

Dharshana said...

Thanks for your info about this.

Anonymous said...

great article!

Dharshana said...

After attach a database from external source, is there any method to read source codes (sql queries) of that database?

Tharindu Edirisinghe said...

@Dharshana-
Of course yes. You can see the attached database in the 'Object Explorer' of MS SQL Server Management Studio. Expand the database you want and right click on the particular table.
(Databases->DatabaseName->Tables->TableName->rightClick) Select
Script Table as -> Create To -> New Query Editor Window
Now the schema of that table will appear. Like wise you can generate insert, select queries also.

Dharshana said...

Thank you, this will be helpful.

Anonymous said...

very very nice article... its really easy to understand and all steps are described very clearly.

Thanx a lot for this article.

Anonymous said...

thanks for the information...keep it up guy's....

Tolga said...

Thanks for information, but i lost primary-foreign key relationships between databases, how can i handle it ?

Tharindu Edirisinghe said...

This method works only for the tables in a single database. If your tables are in more than one database, you can copy all those databases to your new destination. Then it will work.

Samson Chikwendu Edeh said...

you are the sweetest.... it worked... am so happy ryt nw

Tharindu Edirisinghe said...

great.. I'm using this method all the time..

prashant said...

Thanks

Sagir Azam said...

Nice tutorial... It help me a lot.

One more question : How to backup my SQL databases from the my website's shared hosting server database.

Tharindu Edirisinghe said...

@Sagir Azam - Thanks for the comment. I haven't tried backing up mysql databases. I'll check it for you.

Anonymous said...

thnxa lot........ ur tutorial helps me a lot

Anonymous said...

thak u so much for this!!! :D

Anonymous said...

it is good but i want to access data base from pendrive only . pandrive to act as root directory for database

Adetula Tolulope said...

Tank u so much.it helped me

Anonymous said...

It's too easy to understand the way you explained...

msm said...

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'DELL-PC\SQLEXPRESS'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database 'ITA' cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database 'ITA'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=948&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

msm said...

how to resolve

Tharindu Edirisinghe said...

seems like you are using MS SQL Server Express Edition. If so I think you cannot attach a database to the server.

Anonymous said...

The best article..... easy to understand,,, thanx for the article

Anonymous said...

there is no option as detach on clicking tasks...

Tharindu Edirisinghe said...

Are you using the Express edition ???

Anonymous said...

Thank you very much for this tutorial.. Is doing this procedure will include the stored procedure as well?

Anonymous said...

best article......it is very easy to understand....thanx alott

Anonymous said...

Amazing man..... very easy

I thought that it is very hard but the way you explain all the steps make it very easy

balabarathy kanakalingam said...

Thank you this site is very help full for me
and thanks again

Nisha said...

nice article..very easy to understand.good luck man..

Vishal Mittal said...

Thank You!!! :)

Muthukumaran said...

Good article, thank you.
Uplinehost.com

Jejes de' pirates said...

Hello, I've already detach my database, but it's still can't copy to another folder.. Could you help me, please?

Tharindu Edirisinghe said...

Sure I'll help. You can backup the existing database and restore to the new location instead of copying the database files. Just google on "how to backup and restore mssql server database" and you can find the answer. If you still want to get data this way, put a comment and I'll look more into this.

muslim boy said...

thanks dear, this tutorial was very helpful for me.

Pritha said...

Wow. that's a great article, it's so much easy to understand.. keep it up..

Ozzy said...

Hello,

Can anybody here help plz.

can i use the procedure above to move database from sql server 2012 express to sql server 2014 enterprise?
maybe some advice how to achieve this?

many thanks in advance.

rajeesh m said...

nice boss

rajeesh m said...

. it worked... am so happy ryt nw Thank for this information is very help full for me
and thanks again

Darshil Desai said...

Thanks a lot...tutorial was very helpful and easy to understand...

Post a Comment