Share via

  • Article

Copying Databases with Backup and Restore

In SQL Server 2008 R2, you can create a new database by restoring a database backup created by using SQL Server 2000, SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. However, backups of master, model and msdb that were created by using SQL Server 2000 or SQL Server 2005 cannot be restored by SQL Server 2008 R2. Also, SQL Server 2008 R2 backups cannot be restored by any earlier version of SQL Server.

Database backups that were created by using SQL Server 7.0 or earlier are in an incompatible format and cannot be restored in SQL Server 2008 R2. For information on how to migrate a database that was created by using SQL Server 6.5 or earlier to SQL Server 2005, see Copying Databases from SQL Server 7.0 or Earlier.

Important

SQL Server 2008 and SQL Server 2008 R2 use a different default path than earlier versions. Therefore, to restore a database created in the default location of either SQL Server 2000 or SQL Server 2005 from backups, 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:

  1. Back up the source database, which can reside on an instance of SQL Server 2000, SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2. 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.

Warning

If you reuse an existing database name and destination and if its files can be overwritten, any existing files with the same names as those in the backup are overwritten.

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 2000 or 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.

When Upgrading a Database by Using Restore

When restoring backups from SQL Server 2000 or SQL Server 2005, 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 Full-Text Search Upgrade.

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

To restore files and filegroups to a new location

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

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