How to copy a database from one computer to another?

Question

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?

Answer 1

Using SQL Server Management Studio, here are the steps:

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

2.Make sure that the Backup type is Full

3.Click Add and specify the location and backup name

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

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

Answer 2

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:

• Backup: http://msdn.microsoft.com/en-us/library/ms186865.aspx
• Restore: http://msdn.microsoft.com/en-us/library/ms186858.aspx

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.