I made these several articles as a series as Database Recovery:
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,




































Join the conversation! Your thoughts help the community grow.