Copying Databases

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

Back up the database from the source computer running an instance of Microsoft® SQL Server™.
Create backup devices, if desired, at the destination computer running an instance of SQL Server.
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.
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.

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 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 Windows NT® 4.0 or Windows® 2000 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 restores of a database, use media or backup set passwords. For more information, see Password Protection.

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 meta data for the full-text index definitions is 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. 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

Transact-SQL