CODEWITHSUNDEEP
sql-serverdatabase
Matthew
Matthew

Reputation: 4607

How to copy a database from one computer to another?

I have a database in SQL Server 2008, which I want to copy to another computer.

How do I make such a copy?

Once this is done, what should I do at the other computer to build the database once again from my copy?

Upvotes: 29

Views: 125061

Answers (5)

Tom Morgan
Tom Morgan

Reputation: 2365

Simple Answer: Back it up, then restore it on the other computer.

Have a look here: http://technet.microsoft.com/en-us/library/cc966495.aspx

There's a lot of stuff there, but essentially, right click on the database, Tasks > Backup. Fill in the options to perform a full backup to somewhere.

Once it has created the backup (one big file, by convention with a BAK extension), on the second computer, right click the Databases folder, Restore Database and follow the prompts.

You can do it as well in SQL if you wish:

There might be times when it's better to detach and move, but this approach always feels a bit safer!

This will copy both structure and the data in the database.

Upvotes: 23

Milena Petrovic
Milena Petrovic

Reputation: 2771

Using SQL Server Management Studio, here are the steps:

1.Right-click the database and select Tasks | Backup

enter image description here

2.Make sure that the Backup type is Full

3.Click Add and specify the location and backup name

enter image description here

4.Copy the created backup file to another computer

5.In SQL Server Management Studio on another computer, right-click the SQL Server instance and select Restore Database

enter image description here

6.Select Device and click the elipsis button to navigate to the copied backup file

enter image description here

Upvotes: 38

boto
boto

Reputation: 455

You can dump the content of your database and restore it on another machine. The same mechanism can be used also for backup purposes. Check the backup&restore functionality of MS SQL.

Upvotes: 0

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

One of the easiest ways to do this is back up and restore.

http://msdn.microsoft.com/en-us/library/ms187048.aspx

Another option you have is detaching your database from SQL Server, copying the MDF and LDF files to the target machine, and re-attaching there. Note, if you want to keep a copy of the database on the original server, you will also need to attach there too.

http://msdn.microsoft.com/en-us/library/ms190794.aspx

Upvotes: 1

Kell
Kell

Reputation: 3317

Use TSQL backup and restore. This should help http://www.sqlmag.com/article/tsql3/use-t-sql-to-back-up-and-restore-sql-server-user-databases

Upvotes: 1

Related Questions