Agfdhyk

I have a database in SQL Server 2008 R2, and I want to copy this database onto another machine.

How do I make a copy?

And how do I restore it?

Thanks

There are probably more ways to do this but I usually right-click the database and choose "Tasks → Back up..." with Backup type "Full". After that you can copy the created file to your target machine, connect to its SQL Server in SQL Server Management Studio, right-click the "Databases" folder and choose "Restore Database". Select "Device" and choose the file, then restore.

Another approach would be to script the database in SQL Server Management Studio (right-click the database, then "Tasks → Generate scripts..."). During this process there'll be a step called "Set Scripting Options", where you'll have to click the "Advanced" button and carefully go through the options. You'll definitely want to choose "Data and schema" for the option "Types of data to script". I sometimes prefer this method if I really just want the data structures and the data itself to be transferred.

Update: Sorry, I forgot to mention how to restore the database for the scripting option. I always generate the scripts by selecting "Save to new query window" during the "Set Scripting Options" step. After the script is generated, just leave it there for a moment.

On the target server create a new database with the same name as the one you generated the scripts for. Or you can create a script for that on the source server, too (right-click the database, choose "Script Database as → CREATE TO... → Clipboard") and execute this script by right-clicking the server node in the SSMS Object Explorer, selecting "New query", pasting the script into the query window and executing it. This second option is the better choice if you really need a complete copy of the database and not just the data itself.

Once you've gone down one of these two roads you should have created a new database. Right-click this database in Object Explorer and select "New Query", then copy and paste the script containing the database structure and data into the new query window and execute the query. This should do the trick.

You can't copy Database to another machine.
Yes you can take back up to same machine and copy it to another machine and do restore.

To take backup follow procedure:

1. Right Click on the database you want to take backup.


2. Choose Task -> Back Up.


3. In Destination, Choose Add.


4. In File Name click on ... button and choose destination folder where you want to backup with backupname.bak . Click Ok, Ok and Ok. and wait until backup process is completed. Click Ok.

Now copy that backup file into pendrive or any media and paste it to another machine and Open SQL Server 2008 R2

To restore backup follow procedure:

1. Right Click on the Databases.


2. Choose Restore Database.


3. Write database name which you want to restore in To Database field


4. Select From device radio button in Source for restore. Click on ...
   


5. Click on Add button, Select database backup file you have pasted. Click Ok, Ok.


6. Check the checkbox of Restore in Select the beckup sets to restore.


7. Go on Options Check Overwrite the existing database & Preserve the replication settings (this fields needed to check only when you try to restore database which is already resided on that another device)


8. Click Ok. wait until restore complete and click ok.

Tell me if you face any problem.

By Code

To Backup:

USE DATABASE_NAME;

GO

BACKUP DATABASE DATABASE_NAME

TO DISK = 'D:DATABASE_NAME.Bak'

   WITH FORMAT, MEDIANAME = 'D_SQLServerBackups',

   NAME = 'Full Backup of DATABASE_NAME';

GO

(If you want to put backup in any folder, the folder must be exist before you take the backup.)

To Restore:

Step 1: Retrive the Logical file name of the database from backup.

RESTORE FILELISTONLY

FROM DISK = 'D:BackUpYourBaackUpFile.bak'

GO

Step 2: Use the values in the LogicalName Column in following Step.
----Make Database to single user Mode

ALTER DATABASE YourDB

SET SINGLE_USER WITH

ROLLBACK IMMEDIATE

----Restore Database

RESTORE DATABASE YourDB

FROM DISK = 'D:BackUpYourBaackUpFile.bak'

WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',

MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'

/If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user./

ALTER DATABASE YourDB SET MULTI_USER

GO

Copying a database using a full database backup will not copy the transactions in the online transaction log.

If this is important, use the following steps to take the database offline, copy the MDF and LDF files, and attach them:

1. Select the database in SQL Server Management Studio , right-click the database and select Properties. Copy the location of the MDF and LDF files

2.Click OK

3.Right-click the database again, select Tasks, Take offline

4.In Windows Explorer, copy the MDF and LDF files using the location found in step #1

5.Paste them to another location
6.In SQL Server Management Studio right-click the SQL Server instance and select Attach

7.In the next dialog, click Add, find the copied files, select them and click OK
8.Change the default name offered in the Attach AS field. Specify the new name you want for your database here.

9.Click OK

To bring back online the original database, right-click it and select Tasks, Bring online

if you are copying it to a SQL 2008 R2 then all u have to do is

1. open SQL server management studio


2. choose your database


3. right click, go to Tasks --> BackUp


4. in the database backup window, click add and then give your backup path and file name
   click ok

copy the backup file to any drive in the machine to which you want to restore then,

1. Open SQL server management studio


2. create a new database


3. right click on the newly created database, go to Tasks --> Restore --> Database


4. on the restore window select from device option


5. add the backup file path


6. click ok

Justin's answer above was almost correct, it is simply right click on database in SSMS and select "Tasks" > "Export Data". This wizard allows you to duplicate the entire database to another location.

In addition for copy or move database to another server in same network level you can use SQL Server Copy Database Wizard

for use this method you should

1. Right click on database


2. Select task and


3. Select copy database


4. Fill source database server login data


5. Fill destination database server login data


6. Select transfer method

7. Select databases to transfer(except system databases)

Extra description about this method