Copy Databases with Backup and Restore

Artikel
02/28/2023

Applies to: SQL Server

In SQL Server, you can create a new database by restoring a backup of a user database created by using SQL Server 2005 (9.x) or a later version. However, backups of master, model and msdb that were created by using an earlier version of SQL Server cannot be restored by SQL Server. Also, SQL Server backups cannot be restored by any earlier version of SQL Server.

Important

SQL Server 2016 uses a different default path than earlier versions. Therefore, to restore backups of a database created in the default location of earlier versions you must use the MOVE option. For information about the new default path see File Locations for Default and Named Instances of SQL Server. For more information about moving database files, see "Moving the Database Files," later in this topic.

General steps for using Backup and Restore to copy a database

When you use backup and restore to copy a database to another instance of SQL Server, the source and destination computers can be any platform on which SQL Server runs.

The general steps are:

Back up the source database, which can reside on an instance of SQL Server 2005 (9.x) or later. The computer on which this instance of SQL Server is running is the source computer.
On the computer to which you want to copy the database (the destination computer), connect to the instance of SQL Server on which you plan to restore the database. If needed, on the destination server instance, create the same backup devices as used to the backup of the source databases.
Restore the backup of the source database on the destination computer. Restoring the database automatically creates all of the database files.

Some additional considerations that may affect this process:

Before You restore database files

Restoring a database automatically creates the database files needed by the restoring database. By default, the files created by SQL Server during the restoration process use the same names and paths as the backup files from the original database on the source computer.

Optionally, when restoring the database, you can specify the device mapping, file names, or path for the restoring database.

This might be necessary in the following situations:

The directory structure or drive mapping used by the database on the original computer not exist on the other computer. For example, perhaps the backup contains a file that would be restored to drive E by default, but the destination computer lacks a drive E.
The target location might have insufficient space.
You are reusing a database name that exists on the restore destination and any of its files is named the same as a database file in the backup set, one of the following occurs:
- If the existing database file can be overwritten, it will be overwritten (this would not affect a file that belongs to a different database name).
- If the existing file cannot be overwritten, a restore error would occur.

To avoid errors and unpleasant consequences, before the restore operation, you can use the backupfile history table to find out the database and log files in the backup you plan to restore.

Moving the database files

If the files within the database backup cannot be restored onto the destination computer, it is necessary to move the files to a new location while they are being restored. For example:

You want to restore a database from backups created in the default location of the earlier version.
It may be necessary to restore some of the database files in the backup to a different drive because of capacity considerations. This is a common occurrence because most computers within an organization do not have the same number and size of disk drives or identical software configurations.
It may be necessary to create a copy of an existing database on the same computer for testing purposes. In this case, the database files for the original database already exist, so different file names must be specified when the database copy is created during the restore operation.

For more information, see "To restore files and filegroups to a new location," later in this topic.

Changing the database name

The name of the database can be changed as it is restored to the destination computer, without having to restore the database first and then change the name manually. For example, it may be necessary to change the database name from Sales to SalesCopy to indicate that this is a copy of a database.

The database name explicitly supplied when you restore a database is used automatically as the new database name. Because the database name does not already exist, a new one is created by using the files in the backup.

When upgrading a database by using Restore

When restoring backups from an earlier version, it is helpful to know in advance whether the path (drive and directory) of each of the full-text catalogs in a backup exists on the destination computer. To list the logical names and physical names, path and file name) of every file in a backup, including the catalog files, use a RESTORE FILELISTONLY FROM <backup_device> statement. For more information, see RESTORE FILELISTONLY (Transact-SQL).

If the same path does not exist on the destination computer, you have two alternatives:

Create the equivalent drive/directory mapping on the destination computer.
Move the catalog files to a new location during the restore operation, by using the WITH MOVE clause in your RESTORE DATABASE statement. For more information, see RESTORE (Transact-SQL).

For information about alternative options for upgrading full-text indexes, see Upgrade Full-Text Search.

Database ownership

When a database is restored on another computer, the SQL Server login or Microsoft Windows user who initiates the restore operation becomes the owner of the new database automatically. When the database is restored, the system administrator or the new database owner can change database ownership. To prevent unauthorized restoration of a database, use media or backup set passwords.

Managing metadata when restoring to another server instance

When you restore a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the other server instance. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

View the data and log files in a backup set