How to Copy SQL Server 2008 R2 Database from One MAChine to Another

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

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.

How can I clone an SQL Server database on the same server in SQL Server 2008 Express?

It turns out that I had attempted to restore from a backup incorrectly.

Initially I created a new database and then attempted to restore the backup here.
What I should have done, and what worked in the end, was to bring up the restore dialog and type the name of the new database in the destination field.

So, in short, restoring from a backup did the trick.

Thanks for all the feedback and suggestions guys

Trying to copy one table from another database to another in SQL Server 2008 R2

What error message says is simply "You cant add same value if an attribute has PK constraint". If you already have all the information in your backup table what you should do is TRUNCATE TABLE which removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain.

After that step you should follow this answer . Or alternatively i recommend a tool called Kettle which is open source and easy to use for these kinds of data movements. That will save you a lot of work.

How to copy Database from existing to new database sql script using sql server 2008 R2?

If you want to make a copy of the full database to another one you can make a full backup of the existing one and restore it as another database.

To perform a backup you can do doing something like this:

BACKUP DATABASE MyDatabase TO DISK='C:\Backup directory\MyDatabase.bak'
  WITH INIT, FORMAT, SKIP

Then you restore that backup in the destination database (either overwriting an existing database or creating a new one):

RESTORE DATABASE MyDatabase_Copy FROM DISK='C:\Backup directory\MyDatabase.bak'
  WITH REPLACE

If the destination database already exists you could need to use the MOVE option in the restore step (or perform a DROP DATABASE MyDatabase_Copy before restoring).

SQL Server - Copy database from Server 2008 to Server 2012

1. Copy your database file .mdf and log file .ldf and paste in your SQL Server
2. Install file in Programs Files->Microsoft SQL Server->MSSQL10.SQLEXPRESS->MSSQL->DATA.
3. Open SQL Server. Right-click on Databases then Select Attach... option.

copy tables with data to another database in SQL Server 2008

You can use the same way to copy the tables within one database, the SELECT INTO but use a fully qualified tables names database.schema.object_name instead like so:

USE TheOtherDB;

SELECT *
INTO NewTable
FROM TheFirstDB.Schemaname.OldTable

This will create a new table Newtable in the database TheOtherDB from the table OldTable whih belongs to the databaseTheFirstDB

Copying Database From One SQL Server to Another

Finally came across a solution that works.

In SQL Server 2008, there appears to be a bug when either exporting a database in which DEFAULT values are not carried with the table structures.

Here is my solution for circumventing this:

• Right-click on the database you wish to backup.
• If "Welcome to the Generate SQL Server Scripts wizard" dialog appears, click next. Otherwise continue to next step.
• Select the database you wish to transfer.
• There key things to ensure you select properly are as follows:
  
  • Set Script Defaults to True
  • Script USE DATABASE to False
  • Script Data to True
  • Script Indexes to True
  • Script Primary Keys to True
  • Script Triggers to True
  • Script Unique Keys to True
• Once you've finished setting other optional parameters, click Next >.
• Check Stored Procedures, Tables and View (do not check Users unless you want to/need to.) and click Next >.
• Click Select All to select all Stored Procedures and click Next >.
• Click Select All to select all Tables and click Next >.
• Click Select All to select all Views and click Next >.
• Under Script mode, select Script to file.
• Click the Browse... Button and select the folder and filename you wish to save the SQL script under. In this example we'll use my_script.sql.
• Click Finish.

Now that we have the entire database backed up including tables, views, stored procedures, indexes, data, etc. it's time to import this data into a new database.

On the machine you wish to restore this information to, perform the following steps:

• Open your command prompt by clicking Start -> Run... or Pressing Windows (Super) + R on your keyboard.
• Type "cmd" without the quotes in the Run dialog and click OK.
• Browse to the directory your SQL file is located at. In my case, cd "C:\Documents and Settings\Administrator\Desktop"
• Type "sqlcmd -s [server][instance] -i my_script.sql" ... [server] is whatever the name of your Windows machine and [instance] is whatever the name of your SQL instance is. For SQLExpress it is "SQLEXPRESS" without the quotes.
• Press Enter and you're on your way!

Hope this helps someone else who has encountered the maraud of issues!

---

Related Topics