transfer SQL database (table and data)

Question

hey all,

iv created a database with some tables and populated them using SQL server 2008, i want to move the database to another machine, what would be the best way copy the database and recreate it in another location ??

by the way im connecting to localhost\SQLEXPRESS if thats important !!

thanks

Answer 1

Do a backup of your database and then restore it on another machine

Answer 2

Right click on your database in SSMS. Choose Tasks->Backup. Then choose your location and click ok. Take the backup file you create out of the folder it was in and put it into the same folder on your other computer. Then open SSMS on your other machine. Right click on the database folder and click Restore Database. Enter what you ant to name it, choose From Device and then find your file. Click OK and you are done.

Answer 3

I would use SQL Server backup and restore. I believe you can backup in SQLExpress (you just can't schedule one).

Answer 4

Right click your database, and select Tasks -> Backup. Do the backup.

On your other sql server instance create a database with the same name. And do Tasks -> restore -> database

Answer 5

Backup and restore is usually the simplest way. The only complexity you may encounter is that your may have to recreate and associate users on your target system.

Answer 6

If you have permissions to restore on the target database server then that is by far the best way to copy the database.

If you don't have permissions, then you'll want to connect to the source and target databases with Management Studio and Import the data from the source. If you have simple primary key only indices on your tables you can import by right clicking on the target database name, then select Tasks, then Import Data. A wizard will walk you through it.

You can practice by importing the data into a new database on your local machine first.

If you have more complex indices or functions, sprocs, and views, you'll want to create the new database first, before you import the data. Same procedure, but select Generate Scripts. The wizard will walk you through the necessary steps. Under script options you'll want to make sure you select True for Script Indexes.

Again you can practice by recreating and importing locally. Though if you create locally you'll need to give the database a new name.