Ask Sawal

Discussion Forum
Notification Icon1
Write Answer Icon
Add Question Icon

How to duplicate db in sql server?

2 Answer(s) Available
Answer # 1 #

The tables that will be copied from the source database to the destination one are: Department, Employee, Employee Department History and EmployeePayHistory.

Table copying methods.

Using a query.

We can use the Select into a statement to copy our tables from the AdventureWorks database to the SQLShackDemo one. This statement will create the tables in the destination database first and then copy the data to the other tables.

You need to apply the script to the destination database if you want to copy the database objects such as the indexes and constraints.

The below script will be used to copy the Department, Employee, Employee History and EmployeePayHistory tables from the AdventureWorks database to the SQLShackDemo database.

The destination tables have columns created in the order specified in the select statement. The name, data type, nullability property, and column value of all these columns are the same as in the source table.

If the Identity column is present in any of the tables, the new column in the destination table will inherit the Identity property.

If the Select statement contains Join, you are using Union to join multiple Select statements, the Identity column is mentioned many times in your select statement or the source of this Identity column is a remote data source.

The column will be created with Not null property if any of the conditions are true.

The Identity column can be created with the Identity function in the select statement.

Using the wizard to export and import.

The export and import wizard is available in the management studio and can be used to copy tables from the source database to the destination. You can either export from the source database or import from the destination.

If you want to copy tables from the source database to the destination without concern about the tables' relationships and orders, this method is a good choice.

The tables' Indexes and keys won't be transferred using this method. If you want to copy it, you need to create a script for it.

If these are Foreign keys connecting these tables together, you need to export the data in the correct order, otherwise the export wizard will fail.

Using Generate Scripts

There is a way to generate a script for the database with its objects and data. This script can be used to copy the tables' data from the source database to the destination one.

The method of generating a single script for the tables is known as SQL scripting. If there are relations between the tables, this method doesn't generate the creation script in the correct order.

The combination of the data diff and sd is used.

The tool can be used to find the differences between the databases from the side of the database that is used.

The data diff tool can be used to find the differences between the databases from the data side and generate a script to insert the data in the destination database tables.

We can use the two tools together to create the required script to copy the data from the source tables to the destination tables by following the steps below.

The combination of the two tools can be used to create complete scripts for the tables' data and the Identity column insert.

Using a script.

Eijaz Gunaji
Answer # 2 #

Applies to: all supported versions of the SQL server.

The Copy Database Wizard can easily move databases and server objects from one database to another without downtime. You can do this using this wizard.

You must be a member of the fixed server role to work on the source and destination server.

The copy database wizard can be started from the source or destination of the database. Click on a database, select Tasks, and then copy it. If the splash page appears, select Next.

It's used to specify the location of the database to be moved or copied and to enter the login information. Next to connect to the origin server is when you choose the method of logging in. This connection is open throughout the session.

The server is used to specify the location of the database.

You can make a copy of the database if you set the source and destination server to the same server instance. The database name must be changed in the wizard. If there is no name conflict on the destination server, the source database name can be used for the copied or moved database. If there are name conflicts, they must be manually resolved on the destination server.

The detach and attach method is used.

The databases that you want to copy from the source server should be selected. See the restrictions.

If appropriate, change the database name and specify the location of the files. Each database that is moved or copied will have a single page.

The source and destination are on different server.

The source and destination are on different server. The source server database files should be specified in the file system share.

The database can be transferred with the help of the Copy Database Wizard.

You can specify when you want the operation to start.

If you are not a system administrator, you need to specify a proxy account that has access to theSIS package execution subsystem.

A summary of the options is displayed. To change an option, select Back. To create the package, select finish.

The Copy Database Wizard is being executed by the Performing Operation page.

The five steps listed below are the same regardless of whether you choose Move or Copy.

All examples will start at step 6 if the steps are listed here only once.