Copying Databases

The general steps required to copy a database to another computer are:

1. Ensure that both computers running Microsoft® SQL Server™ are using the same code page, sort order, Unicode collation, and Unicode locale.
2. Back up the database from the source computer running SQL Server.
3. Create backup devices, if desired, at the destination computer running SQL Server.
4. Restore the database backup to the destination computer. It is not necessary to create the files or the database before restoring the backup.

Re-creating Database Files

Restoring a database automatically creates the files needed by the database backup to restore the backup into. The database files (hence the database) do not need to be created before restoring a backup. By default, the files created by SQL Server during the restoration process use the same name and path as the backup files from the original database on the source computer. Therefore, it is useful to know in advance the files that are created automatically by the restore operation, because:

• The file names may already exist on the computer, causing an error.
• 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 are allowed to 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.

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 as they are being restored. For example:

• 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.
• Creating a copy of an existing database on the same computer for testing purposes may be necessary. 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.

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 this is a copy of a database.

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

Database Ownership

When a database is restored onto another computer, the SQL Server login or Microsoft Windows NT® 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.

Restoring Full-Text Index Data

If the database being copied contains tables that have been defined for full-text indexing, then the destination computer must also have Full-Text Search installed and the MSSearch Service started before the full-text catalogs can be re-created and repopulated.

Because the metadata for the full-text index definitions are stored in the system tables of a database, it is useful to know in advance whether any of the full-text catalogs on the source computer resided on drives and directories other than the default (\MSSQL7\FtData). These directories or drive mappings may not exist on the destination computer and must be created first. To view the locations of the full-text catalog(s) on the source computer, execute the sp_help_fulltext_catalogs system stored procedure. The PATH column value is the location where the full-text catalog will be re-created on the destination computer. If the PATH column value of the result set is NULL, then this denotes the default full-text catalog location.

To view the data and log files in a backup set

To restore files and filegroups over existing files

To restore files and filegroups to a new location

To restore a database with a new name

To restart an interrupted restore operation

To change the owner of a database

See Also