Microsoft sql server restore database in use

After you add the devices you want to the Backup media list box, click OK to return to the General page. In the Source: Device: Database list box, select the name of the database which should be restored.

Note This list is only available when Device is selected. Only databases that have backups on the selected device will be available. In the Destination section, the Database box is automatically populated with the name of the database to be restored. To change the name of the database, enter the new name in the Database box. Use either the Date and Time boxes or the slider bar to specify a specific date and time to where the restore should stop.

Click OK. Use the Timeline Interval box to change the amount of time displayed on the timeline. After you have specified a specific point in time, the Database Recovery Advisor ensures that only backups that are required for restoring to that point in time are selected in the Restore column of the Backup sets to restore grid.

These selected backups make up the recommended restore plan for your point-in-time restore. You should use only the selected backups for your point-in-time restore operation. For information about the columns in the Backup sets to restore grid, see Restore Database General Page.

On the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:. For more information about these options, see Restore Database Options Page.

Select an option for the Recovery state box. This box determines the state of the database after the restore operation. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now. Additional transaction logs can be restored. The database cannot be used until it is recovered. It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted. For descriptions of the options, see Restore Database Options Page.

Take tail-log backup before restore will be selected if it is necessary for the point in time that you have selected. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services.

Privacy policy. Applies to: SQL Server all supported versions. You can move a database to a new directory path or create a copy of a database on either the same server instance or a different server instance. Under the full or bulk-logged recovery model, before you can restore a database, you must back up the active transaction log. To restore an encrypted database, you must have access to the certificate or asymmetric key used to encrypt the database! Without that certificate or asymmetric key, you cannot restore the database.

You must retain that certificate used to encrypt the database encryption key for as long as you need the backup! For additional considerations for moving a database, see Copy Databases with Backup and Restore.

If you restore a SQL Server 9. Typically, the database becomes available immediately. However, if a SQL Server 9. 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. 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. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner dbo of the database.

Right-click Databases , and then click Restore Database. The Restore Database dialog box opens. On the General page, use the Source section to specify the source and location of the backup sets to restore. Select one of the following options:.

Select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the msdb backup history. NOTE: If the backup is taken from a different server, the destination server will not have the backup history information for the specified database.

In this case, select Device to manually specify the file or device to restore. You must consider a variety of factors. These include:. The goals of your organization regarding your production databases, especially the requirements for availability and protection of data from loss or damage. The nature of each database: its size, its usage patterns, the nature of its content, the requirements for its data, and so on. Constraints on resources, such as: hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.

Ensure that you place your database backups on a separate physical location or device from the database files. When your physical drive that stores your databases malfunctions or crashes, recoverability depends on the ability to access the separate drive or remote device that stored the backups in order to perform a restore.

Keep in mind that you could create several logical volumes or partitions from a same physical disk drive. Carefully study the disk partition and logical volume layouts before choosing a storage location for the backups.

Backup and restore operations occur within the context of a recovery model. A recovery model is a database property that controls how the transaction log is managed.

Thus, the recovery model of a database determines what types of backups and restore scenarios are supported for the database, and what the size of the transaction log backups would be.

Typically, a database uses either the simple recovery model or the full recovery model. The full recovery model can be augmented by switching to the bulk-logged recovery model before bulk operations. The best choice of recovery model for the database depends on your business requirements. To avoid transaction log management and simplify backup and restore, use the simple recovery model. To minimize work-loss exposure, at the cost of administrative overhead, use the full recovery model.

To minimize impact on log size during bulk-logged operations while at the same time allowing for recoverability of those operations, use bulk-logged recovery model. After you have selected a recovery model that meets your business requirements for a specific database, you have to plan and implement a corresponding backup strategy.

The optimal backup strategy depends on a variety of factors, of which the following are especially significant:. If there is a predictable off-peak period, we recommend that you schedule full database backups for that period. Under the simple recovery model, consider scheduling differential backups between full database backups. A differential backup captures only the changes since the last full database backup.

Under the full recovery model, you should schedule frequent log backups. Scheduling differential backups between full backups can reduce restore time by reducing the number of log backups you have to restore after restoring the data. Are changes likely to occur in only a small part of the database or in a large part of the database? For a large database in which changes are concentrated in a part of the files or filegroups, partial backups and or file backups can be useful.

Make sure you have a proper backup schedule established according to the needs of the application and business requirements. As the backups get old, the risk of data loss is higher unless you have a way to regenerate all the data till the point of failure.