CODEWITHSUNDEEP
sqlsql-server-2008-r2
DK007
DK007

Reputation: 285

How to copy SQL Server 2008 R2 database from one machine to another

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

Upvotes: 22

Views: 100137

Answers (6)

Hamed Naeemaei
Hamed Naeemaei

Reputation: 9746

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

Copy Database Wizard

  1. Select databases to transfer(except system databases)

select databases

Extra description about this method

Upvotes: 2

Derek
Derek

Reputation: 19

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.

Upvotes: 1

Milena Petrovic
Milena Petrovic

Reputation: 2771

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

enter image description here

2.Click OK

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

enter image description here

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

enter image description here

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.

enter image description here

9.Click OK

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

Upvotes: 10

Kiran M R
Kiran M R

Reputation: 91

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

Upvotes: 2

Onkel Toob
Onkel Toob

Reputation: 2212

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.

Upvotes: 21

Dhwani
Dhwani

Reputation: 7626

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

Upvotes: 26

Related Questions