Copying Databases with Backup and Restore

In Microsoft SQL Server 2005, you can create a new database by restoring a database backup created using Microsoft SQL Server 7.0, Microsoft SQL Server 2000, or SQL Server 2005. However, backups of master, model and msdb that were created by using SQL Server 7.0 or SQL Server 2000 cannot be restored by SQL Server 2005. Also, SQL Server 7.0 log backups that contain create-index operations cannot be restored to SQL Server 2000 or SQL Server 2005.

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:

1. Back up the source database, which can reside on an instance of SQL Server 7.0, SQL Server 2000, or SQL Server 2005. The computer on which this instance of SQL Server is running is the source computer.
2. 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.
3. Restore the backup of the source database on the destination computer. Restoring the database automatically creates all of the database files.

The following topics address additional considerations that may affect this process.

Before You Restore Database Files

Restoring a database automatically creates the files that are needed by the restoring database. By default, the files that are 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. To avoid errors and unintended consequences, before the restore operation find out which files are created automatically by the restore operation, because:

• The file names may already exist on the computer, causing an error.
• The target location might have insufficient space.
• The directory structure or drive mapping may not exist on the computer.
  For example, the backup contains a file that it needs to restore to drive E, but the destination computer does not have a drive E.
• If the database files can be replaced, any existing database and files with the same names as those in the backup are overwritten, unless those files belong to a different database.

When restoring the database, if necessary, you can specify the device mapping, file names, or path for the restoring database.

Moving the Database Files

If the files within the database backup cannot be restored onto the destination computer because of the reasons mentioned earlier, 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 either SQL Server 7.0 or SQL Server 2000.

  Note

  For information about the default path in SQL Server 2005, see File Locations for Default and Named Instances of SQL Server 2005.

• It may be necessary to restore some of the database files in the backup to a different drive because of capacity considerations. This is likely to be 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 need to 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 that is 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.

Restoring Full-Text Index Data

Backup and restore treat full-text catalogs the same as database files. If the database that is being copied contains tables that have been defined for full-text indexing, then the destination computer must also have Full-Text Search installed before the full-text catalogs can be re-created and repopulated. If the Microsoft Full-Text Engine for SQL Server (MSFTESQL) service is not installed, the full-text catalog will be offline after restore.

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).

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. For more information, see Security Considerations for Backup and Restore.

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 Managing Metadata When Making a Database Available on Another Server Instance.

To view the data and log files in a backup set

• RESTORE FILELISTONLY (Transact-SQL)

To restore files and filegroups to a new location

• How to: Restore Files to a New Location (Transact-SQL)
• How to: Restore a Database Backup (SQL Server Management Studio)

To restore files and filegroups over existing files

• How to: Restore Files and Filegroups over Existing Files (Transact-SQL)

To restore files and filegroups to a new location

• How to: Restore Files to a New Location (Transact-SQL)
• How to: Restore a Database Backup (SQL Server Management Studio)

To restore a database with a new name

• How to: Restore a Database to a New Location and Name (Transact-SQL)
• How to: Restore a Database Backup (SQL Server Management Studio)

To restart an interrupted restore operation

• How to: Restart an Interrupted Restore Operation (Transact-SQL)

To change the owner of a database

• sp_changedbowner (Transact-SQL)

To copy a database by using SQL Server Management Objects (SMO)

• ReadFileList
• RelocateFiles
• ReplaceDatabase
• Restore

See Also

Concepts

Copying Databases from SQL Server 6.5 or Earlier
Copying Databases from SQL Server 6.5 or Earlier
Copying Databases to Other Servers
Working with Transaction Log Backups

Other Resources

File Locations for Default and Named Instances of SQL Server 2005
RESTORE FILELISTONLY (Transact-SQL)
RESTORE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History