Dela via

  • Artikel

How to: Restore a Database to a New Location and Name (Transact-SQL)

This topic explains how to restore a full database backup to a new location and, optionally, with a new name. This procedure enables you to move a database or create a copy of a database on either the same server instance or a different server instance. For information about considerations for moving a database, see Copying Databases with Backup and Restore.

Prerequisites and Recommendations

  • To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. Without the certificate or asymmetric key, the database cannot be restored. As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. For more information, see SQL Server Certificates and Asymmetric Keys.

  • For security purposes, we recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

Database Compatibility Level After Upgrade

The compatibility levels of the tempdb, model, msdb and Resource databases are set to 100 after upgrade. The master system database retains the compatibility level it had before upgrade, unless that level was less than 80. If the compatibility level of master was less than 80 before upgrade, it is set to 80 after upgrade.

If the compatibility level of a user database was 80 or 90 before upgrade, it remains the same after upgrade. If the compatibility level was 70 or less before upgrade, in the upgraded database, the compatibility level is set to 80, which is the lowest supported compatibility level in SQL Server 2008.

Note

New user databases will inherit the compatibility level of the model database.

Procedures

To restore a database to a new location and name

  1. Optionally, determine the logical and physical names of the files in the backup set that contains the full database backup that you want to restore. This statement returns a list of the database and log files contained in the backup set. The basic syntax is as follows:

    RESTORE FILELISTONLY FROM <backup_device> WITH FILE = backup_set_file_number

    Note

    You can obtain the backup_set_file_number of a backup set by using the RESTORE HEADERONLY statement.

    This statement also supports a number of WITH options. For more information, see RESTORE FILELISTONLY (Transact-SQL).

  2. Use the RESTORE DATABASE statement to restore the full database backup. By default, data and log files are restored to their original locations. To relocate a database, use the MOVE option to relocate each of the database files and to avoid collisions with existing files.

    The basic Transact-SQL syntax for restoring the database to a new location and a new name is:

    RESTORE DATABASE new_database_name

    FROM backup_device [ ,...n ]

    [ WITH

       {

            [ RECOVERY | NORECOVERY ]

       [ , ] [ FILE ={ backup_set_file_number | @backup\_set\_file\_number } ]

       [ , ] MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ]

       }

    ;

    Note

    When preparing to relocate a database on a different disk, you should verify that sufficient space is available and identify any potential collisions with existing files. This involves using a RESTORE VERIFYONLY statement that specifies the same MOVE parameters that you plan to use in your RESTORE DATABASE statement.

    The following table describes arguments of this RESTORE statement in terms of restoring a database to a new location. For more information about these arguments, see RESTORE (Transact-SQL).

    • new_database_name
      The new name for the database.

      Note

      If you are restoring the database to a different server instance, you can use the original database name instead of a new name.

    • backup_device [ ,...n ]
      Specifies a comma-separated list of from 1 to 64 backup devices from which the database backup is to be restored. You can specify a physical backup device, or you can specify a corresponding logical backup device, if defined. To specify a physical backup device, use the DISK or TAPE option:

      { DISK | TAPE } **=**physical_backup_device_name

      For more information, see Backup Devices.

    • { RECOVERY | NORECOVERY }
      If the database uses the full recovery model, you might need to apply transaction log backups after you restore the database. In this case, specify the NORECOVERY option.

      Otherwise, use the RECOVERY option, which is the default.

    • FILE = { backup_set_file_number | @backup\_set\_file\_number }
      Identifies the backup set to be restored. For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium and a backup_set_file_number of 2 indicates the second backup set. You can obtain the backup_set_file_number of a backup set by using the RESTORE HEADERONLY statement.

      When this option is not specified, the default is to use the first backup set on the backup device.

      For more information, see "Specifying a Backup Set," in RESTORE Arguments (Transact-SQL).

    • MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ]
      Specifies that the data or log file specified by logical_file_name_in_backup is to be restored to the location specified by operating_system_file_name. Specify a MOVE statement for every logical file you want to restore from the backup set to a new location.

      Option

      Description

      logical_file_name_in_backup

      Specifies the logical name of a data or log file in the backup set. The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created.

      NoteNote
      To obtain a list of the logical files from the backup set, use RESTORE FILELISTONLY.

      operating_system_file_name

      Specifies a new location for the file specified by logical_file_name_in_backup. The file will be restored to this location.

      Optionally, operating_system_file_name specifies a new file name for the restored file. This is necessary if you are creating a copy of an existing database on the same server instance.

      n

      Is a placeholder indicating that you can specify additional MOVE statements.

Note

After you restore a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008, the database becomes available immediately and is then automatically upgraded. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. If the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes will be unavailable during the upgrade. Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog is not available. To change the setting of the upgrade_option server property, use sp_fulltext_service.

Example

Description

This example creates a new database named MyAdvWorks. MyAdvWorks is a copy of the existing AdventureWorks database that includes two files: AdventureWorks_Data and AdventureWorks_Log. This database uses the simple recovery model. The AdventureWorks database already exists on the server instance, so the files in the backup must be restored to a new location. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The database backup is the first backup set on the backup device.

Note

For an example of how to create a full database backup of the AdventureWorks database, see How to: Create a Full Database Backup (Transact-SQL).

Note

The examples of backing up and restoring the transaction log, including point-in-time restores, use the MyAdvWorks_FullRM database that is created from AdventureWorks just like the following MyAdvWorks example. However, the resulting MyAdvWorks_FullRM database must be changed to use the full recovery model: ALTER DATABASE MyAdvWorks_FullRM SET RECOVERY FULL.

Code

USE master
GO
-- First determine the number and names of the files in the backup.
-- AdventureWorks_Backup is the name of the backup device.
RESTORE FILELISTONLY
   FROM AdventureWorks_Backup
-- Restore the files for MyAdvWorks.
RESTORE DATABASE MyAdvWorks
   FROM AdventureWorks_Backup
   WITH RECOVERY,
   MOVE 'AdventureWorks_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf', 
   MOVE 'AdventureWorks_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf'
GO