RMP Resource - Restore MS SQL Database

 

SQL Server databases may be backed up on one server and restored to another server. If the target database does not exist on the second server, it may be created before the restore is initiated. However a new database can be created while restoring the backup.

If a new database is created while restoring the backup, all the restrictions mentioned in Restoring a Complete Backup to a New Database on the Same Server apply.

The steps in this section demonstrate one of the many ways that someone may use to restore the database onto a different server.

Follow this step to to restore complete backup to a new server by using SQL Server 2000 Enterprise Manager GUI .

STEP 1: Copy the database backup file to the target machine or share it on the network so that it is accessible from the server.

STEP 2: Open Enterprise Manager and connect to the server to which the backup is to be restored.

STEP 3: Right-click any database and select All Tasks, then select Restore Database

STEP 4 : Enter the name of the new database that the backup process will create and restore the backup to, in the Restore as database list box. Select the From device radio button.  Click the Select Devices... button.

STEP 5 :  In the Choose Restore Devices dialog box, select either the Disk button. Click the Add button to add backup file.

.

STEP 6: Select backup file you have copied and click OK.

STEP 7: Click the OK button. The Choose Restore Device dialog box, is now updated with the selected file.

STEP 8: Click the OK button to return to the Restore Database dialog box. The Devices list is now updated with the file selected in the previous step.

STEP 9: Click the Options tab and select appropriate options for the restore operation. You need to set the paths of data file and log file path if the paths of SQL Server installation is different from the original database.

STEP 10: Click OK in the Restore Database dialog box. This starts the restore process. Progress and confirmation dialog boxes are displayed as SQL Server processes and completes the restore of the database.

STEP 11: Click OK in the confirmation dialog box to finish the restore process..

 

Follow this step to to restore complete backup to a new server by using SQL Server 2005 Console Manager GUI .

STEP 1. Install SQL 2005.

STEP 2. Open SQL Server Management Studio

STEP 3. Right click Database Tree

STEP 4. Select Restore Database

STEP 5. Type <database name> in  to database box

STEP 6. Click form device

STEP 7. Click browse button, Click Add and select file and select the backup file.

STEP 8. Click OK

STEP 9. Select restore the database backup from grid.

STEP 10. Click OK to complete