Database Recovery (1): Clone Database From One Instance To Another In SQL Server

I made these several articles as a series as Database Recovery:

• Database Recovery (1): Clone Database From One Instance To Another In SQL Server --- this article
  • Database Recovery (1-1): Restore a Table from a Backup Database through Restore/Export
  • Database Recovery (1-2): Recover Deleted Table Data "without Backup" in SQL Server
    • Database Recovery (1-2-1): Use Transaction Log to Recover the Deleted or Modified Data 

Introduction

It is sometimes useful to copy a database from one computer to another, whether for testing, checking consistency, developing software, running reports, creating a mirror database, or, possibly, to make the database available to remote-branch operations.

There are several ways to copy a database:

• Using the Copy Database Wizard

  You can use the Copy Database Wizard to copy or move databases between servers or to upgrade a SQL Server database to a later version. For more information, see Use the Copy Database Wizard.

• Restoring a database backup

  To copy an entire database, you can use the BACKUP and RESTORE Transact-SQL statements. Typically, restoring a full backup of a database is used to copy the database from one computer to another for a variety of reasons. For information on using backup and restore to copy a database, see Copy Databases with Backup and Restore.

   Note

  To set up a mirror database for database mirroring, you must restore the database onto the mirror server by using RESTORE DATABASE <database_name> WITH NORECOVERY. For more information, see Prepare a Mirror Database for Mirroring (SQL Server).

• Using the Generate Scripts Wizard to publish databases

  You can use the Generate Scripts Wizard to transfer a database from a local computer to a Web hosting provider. For more information, see Generate and Publish Scripts Wizard.

This article will discuss the ways to migrate database from one instance to another in the same machine or a different one, we will leave the topic of moving a table in a database to another database in a different article.

The following database migration methods will be included in this article,

• 1 - Attach/Deattach
• 2 - Backup/Restore
• 3 - Export/Import --- Export/Import Wizard
• 4 - Scripting --- Generate Scripts Wizard
• 5 - Copy Database --- Copy Database Wizard

1 - Attach/Detach

The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. [ref]

File access permissions are set during a number of database operations, including detaching or attaching a database.

Note:

Do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure.

When you want to copy a database file from one computer to another, you have to have access to at least the source database location, and you have to detach the database first before your copying.  However, the security setting might not allow you to either access the database file folder or detach the database if the SQL server is located in server.

Detach:

In the opened Detach Database Window: Click OK

The database is detached:

Attach

The Attach Databases Window is opened: Click Add button.

 

Choose a database file.

Click OK.

The database has been attached.

2 - Backup/Restore

We can use them in either the same database or create a new database with a backup file: Note, for the later way, we do not need to shut down the source database, say in server.  This is an advantage compared with attach/detach method.

Backup

in Backup Database Window: Click OK

Done, the backup file is in the directory indicated above

Restore

If restore to the same database, right click the database Name.

Choose the database you want to restore.

Click OK, we got an error.

Following the instruction, Click Option and Check Overwrite the existing database (WITH REPLACE)

Click OK.

Done.

If restore to another server without an existing one: Right Click Database.

To use data file, Check Device, then Click the ... buton on the right.

In a Select backup devices Window, Click Add button.

Choose the backup file (if we copied the file from server, and save into local)

Click OK.

Click OK, We got the input source file, and also auto-filed the same database name in the destination.

If we want a different database name, change it, say, as A_test1,

Click OK, 

Done, Got the restored database.

3 - Export/Import - Using the Export/Import Wizard

We make the export and import by the SQL Server Import and Export Wizard, the wizard will export a database from one server or SQL instance and directly import to another server or SQL instance.

Run Export/Import Wizard

Click Next.

In SQL Server Import and Export Wizard, choose Data Source as SQL Server Native Client 11.0

Click Next, we have

Click Next, Open SQL Server Import and Export Wizard again for Destination Database.

Type in the server name, we got a list of database names.

If we want to make a new database, Click New button.

In the Create Database window, type in the database name. say, A_test2. Click OK button.

Click Next.

Next.

Next

Next,

We got the Import/Export execution running.

The Exported database created in the new server.

4 - Scripting - Using the Generate Scripts Wizard [ref]

You can use the Generate and Publish Scripts Wizard to create scripts for transferring a database between instances of the SQL Server Database Engine or Azure SQL Database. You can generate scripts for a database on an instance of the Database Engine in your local network, or from SQL Database. The generated scripts can be run on another instance of the Database Engine or SQL Database. You can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services. You can create scripts for an entire database, or limit it to specific objects.

To generate a publish script

Click Next

Choose Script Entire Database Option - Select to generate scripts for all objects in the database and to include a script for the database itself.

Or Select specific database objects - Select to limit the wizard to generate scripts for only the specific objects in the database that you choose.

Set Scripting Options

Click Advanced button for Advanced Scripting Options

Summary page.

Run and Save Script.

The script saved.

5 - Copy Database - Using the Copy Database Wizard 

You can use the Copy Database Wizard to copy or move databases between servers or to upgrade a SQL Server database to a later version. The Copy Database Wizard moves or copies databases and certain server objects easily from one instance of SQL Server to another instance, with no server downtime. By using this wizard, you can do the following [ref]:

• Pick a source and destination server.

• Select database(s) to move or copy.

• Specify the file location for the database(s).

• Copy logins to the destination server.

• Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.

• Schedule when to move or copy the database(s).

Run Copy Wizard

Open Copy Database Wizard, Click next.

Choose Source Server, Next.

Try to link to Destination Server, got error message.

As indicated, SQL Server Agent does not start.

Start SQL Server Agent

SQL Server Agent started.

Rerun.

Next

Next, we got the multiple choices of database to be copied.

Reference

• Database Detach and Attach (SQL Server) - SQL Server | Microsoft Docs
• Import and Export Data with the SQL Server Import and Export Wizard - SQL Server Integration Services (SSIS) | Microsoft Learn
• Quickstart: Back up & restore database - SQL Server | Microsoft Learn
• Generate and Publish Scripts Wizard - SQL Server Management Studio (SSMS) | Microsoft Learn
• Use the Copy Database Wizard - SQL Server | Microsoft Learn
• Copy Databases to Other Servers - SQL Server | Microsoft Learn
• How to copy tables from one database to another in SQL Server (sqlshack.com) 
• --- this topic about table clone is not included in this article
  • SQL Server - synchronizing 2 tables on 2 different databases - Stack Overflow
  • How To Clone Tables in SQL.| Towards Data Science
  • SQL Cloning or Copying a Table - Tutorial Republic
  • SQL Server - Create a copy of a database table and place it in the same database? - Stack Overflow