How to: Create a New Database From an Existing Database Backup (SQL Server Management Studio)

This topic explains how to create a new database from an existing full database backup.

Security Note:

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.

Important

Under the full or bulk-logged recovery model, before you can restore a database in SQL Server Management Studio, you must back up the active transaction log (known as the tail of the log). For more information, see How to: Back Up a Transaction Log (SQL Server Management Studio).

To create a new database from an existing full backup

1. After you connect to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

2. Right-click Databases, point to Restore, and then click Database. Clicking Database opens the Restore Database dialog box.

3. On the General page, the name of the source database appears in the To database list by default. To create the new database, enter its name in the list box.

4. In the To a point in time text box, either use the default (Most recent possible) or click the browse button to select a specific date and time.

5. To specify the source and location of the backup sets to restore, click one of the following options:

   • From database
     Enter a database name in the list box.
   • From device
     Click the browse button. This opens the Specify Backup dialog box. In the Backup media list, select one of the listed device types. To select one or more devices for the Backup location list, click Add.
     After you add the device or devices you want to the Backup location list, click OK to return to the General page.

6. In the Select the backup sets to restore grid, select the backups to restore. This grid displays the backups available for the specified location. By default, a recovery plan is suggested. To override the suggested recovery plan, you can change the selections in the grid. Backups that depend on the restoration of an earlier backup are automatically deselected when the earlier backup is deselected.

   For information about the columns in the Select the backup sets to restore grid, see Restore Database (General Page).

7. To view or select the advanced options, click Options in the Select a pagepane.

8. In the Restore options panel, optionally select any of the options:

   • Overwrite the existing database
   • Preserve the replication settings
   • Prompt before restoring each backup
   • Restrict access to the restored database

   For complete descriptions of theses options of the Restore options panel, see Restore Database (Options Page).

9. You can move the database that you are restoring by specifying new restore destinations for the files in the Restore the database files as grid. The grid lists the original full path for each data or log file of the database and the restore destination for each file.

   The following table describes the columns of the Restore the database files as grid.

   Header

10. The Recovery state panel determines the state of the database after the restore operation. The default behavior is to:

    • Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)

      Note

      Choose this option only if you are restoring all the necessary backups now.

    Alternatively, you can select to either:

    • Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
    • Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

    For descriptions of the options of the Restore options panel, see Restore Database (Options Page).

See Also

Tasks

How to: Back Up a Transaction Log (SQL Server Management Studio)
How to: Restore a Database to a New Location and Name (Transact-SQL)
How to: Restore a Transaction Log Backup (SQL Server Management Studio)

Other Resources

SQL Server Management Studio Tutorial

Help and Information

Getting SQL Server 2005 Assistance