Restore Data - Microsoft SQL Server - Full System Restore

Topics | How To | Full System Restore | Related Topics

Overview

Perform SQL Server Recovery

Perform a Full System Restore

Perform a Full SQL Server Instance Restore

• Method 1 (Recommended)
• Method 2

Overview

In the case of disaster recovery, where a full system restore is required, you must rebuild the system to exactly the state as it existed before the problem. In some cases, where the SQL Server is corrupted, the SQL Server software must be reloaded and the server rebuilt.

The difference between a normal restore and a full system restore is the severity of the problem. Normally, if data is lost or removed, it is recovered from the archives using the normal restore procedures. However, when a normal restore operation cannot correct a software and/or hardware corruption problem, some additional changes may be required.

Keep in mind that for this or any SQL restore to take place, you must first have a SQL server instance up and running.

Perform SQL Server Recovery

To perform SQL Server Recovery:

1. If the SQL Server instance starts, go to Step 2.

2. If there is software currently running on the server that may attempt to access any database within the instance (e.g., Virus Scan), disable it at this time.
3. Perform a restore of the SQL Server instance or databases using the most current backup that is valid. See Perform a Full SQL Server Instance Restore.
4. If desired, enable any software that was disabled in a previous step.
5. Perform a full backup of the SQL Server to ensure the existence of a current archive.

Perform a Full System Restore

A full system restore is the process of restoring all files (including the operating system), and SQL Server databases on a client computer after a catastrophic event.

Before You Begin

• If necessary, rebuild the hardware as it existed before.
• Install the same Windows operating system with the same patches that were previously installed.
• Configure the system with the same networking parameters and passwords that were previously set.
• If you are not using DNS, set up peer-to-peer communication with the CommServe. Edit the host file to include the CommServe name.

To perform a full system restore:

1. Install the File System iDataAgent software. In a clustered environment, you must reinstall to the same virtual node as was being used previously.
2. Using the File System iDataAgent, perform a full iDataAgent restore of the system as it existed before. Use the instructions in Disaster Recovery for the appropriate file system.

   If you are rebuilding a failed cluster node and the SQL Server database is not attached, refer to the Microsoft SQL High Availability documentation, Restoring a Node after a Catastrophic Failure, in order to install SQL software on a restored node.

3. If the SQL Server instance starts, go to Step 4.

   If the SQL Server instance does not start, there are two options:

   • Option 1: Rebuild Master Database
   • Option 2: Uninstall and reinstall each instance of the Microsoft SQL Server

   OPTION 1: Rebuild Master Database

   This option is the faster of the two options, but depending on your environment, it is not guaranteed to always work.

   SQL Server 2000:

   This method is referenced in Microsoft MSDN article aa197950 (SQL.80).

   Run rebuildm.exe as follows:

   • Locate your SQL Server installation disc.
   • Copy the data directory x86\data to a shared or local disk.
   • Reset the read-only attribute on all files in this directory.
   • Run the rebuildm.exe program located in (for SQL 2000) program files\microsoft sql server\80\tools\binn.
     • Select the SQL Server instance.
     • Select the source directory for data files by browsing to the location where you copied the data files.
     • Select the collation setting for this instance.
     • Click Rebuild.
   • Make sure the SQL Server instance is now running or start it manually.
   • Ensure that all system databases are up and running.

   SQL Server 2005 and later:

   This method is referenced in Microsoft MSDN article MS144259.

   • Insert the SQL Server installation media into the disc drive or copy the data directory x86\data to a shared or local disk.
   • Open the command prompt window and go to the \Servers folder.
   • Enter the following command:
     • start /wait setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>
       
       where:
       /qn = suppresses all setup dialog boxes and error messages.
       /qb = allows display of basic setup dialog boxes and error messages.
   • Make sure the SQL Server instance is now running or start it manually.
   • Ensure that all system databases are up and running.

   SQL Server 2005 and later (Express version):

   OPTION 1: If master exists

   • Download the Express kit, and extract the contents to a local directory.
   • Extract the contents to a local directory:
     • D:\Expressdir\SQLEXPRESS.EXE –x
       where:
       -x = extract the content
   • Open the command prompt window and go to the extracted folder.
   • Enter the following command:
     •  start /wait setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword> REINSTALLMODE=vomus
       
       where:
       /qn = suppresses all setup dialog boxes and error messages.
       /qb = allows display of basic setup dialog boxes and error messages.
       REINSTALLMODE=vomus = instructs MSI to use these files and not the remembered location when rebuilding the databases.
   • Make sure the SQL Server instance is now running or start it manually.
   • Ensure that all system databases are up and running.
     
     OPTION 2: If master does not exist
   • Go to the directory where the SQL Server is installed, usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.
   • Open the Template Data folder and verify that there is a copy of the system databases created automatically during the original install.
   • Copy the missing files and paste them into the Data directory (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).
   • Make sure the SQL Server instance can be started manually.
   • Ensure that all system databases are up and running.

   OPTION 2: Uninstall and reinstall each instance of the Microsoft SQL Server (valid for all SQL versions).

   • Use the same installation paths and configuration settings as the previous installation. Paths and instance name information of the original installation can be viewed by browsing the backup data from the CommCell Browser.
   • Make sure the SQL Server instance is now running or start it manually.
   • Ensure that all system databases are up and running.
4. Install the SQL Server iDataAgent software.
5. If there is software currently running on the server that may attempt to access any database within the instance (e.g., Virus Scan), disable it at this time.
    

   Ensure that the SQL Server service for instances being protected are not using the Network Service account as restores will fail.

6. Perform a restore of the SQL Server instance or databases using the most current backup that is considered valid. See Perform a Full SQL Server Instance Restore.
7. Perform a full backup of the SQL Server to ensure the existence of the current archive.

Perform a Full SQL Server Instance Restore

A full SQL Server instance restore is the process of fully restoring all databases of a SQL Server instance to the same computer. Full SQL Server instance restores can be used to re-establish a SQL Server’s databases after a catastrophic server failure.

There are two methods you can use to perform a SQL Server instance restore:

• Method 1 (Recommended)
• Method 2

Method 1 (Recommended)

Method 1 allows you to restore the system databases (master, model, and msdb) as one restore job, and then restore user defined database one-by-one. By restoring your most important database(s) before less vital databases, you can get users up and running on the server while you then restore the remaining database(s).

This method also allows you to determine the order in which databases are restored. You can even restore user databases in parallel as long as the databases have been configured to backup to different storage policies.

If there are any applications or services that access SQL databases, they should be stopped prior to the restore.

1. Perform either a single or multiple database restore of the system databases (master, model, msdb). If choosing to restore these individually, restore them in this order: master, model, msdb.
2. On the SQL Server Restore Options dialog box, select Unconditionally overwrite existing databases or files, and any other applicable restore options.
3. Restore the user databases using Restore Multiple SQL Server Databases to a Consistent Time in the order you want (i.e., select one or more databases*, run the restore, and allow it to complete. Then select the next group of databases, run the restore, and allow it to complete, etc.).

   * Keep in mind that when restoring non-system databases, each database being restored runs as a separate job and a separate SQL process. Therefore, avoid starting more restores (jobs) than your SQL server and available memory can handle.

This task is now complete.

Method 2

Method 2 allows you to restore the whole instance, both system and user-defined databases, as one job. When restoring through Method 2, databases will be restored in the following order: system databases (master, model, msdb), then user databases. User databases are restored in the order in which they were added to the SQL Server iDataAgent.

This method is useful if:

• all databases on the SQL instance are of equal importance; or
• all databases have been backed up using the same storage policy with each database requiring the same media for the restore to complete.

If there are any applications or services that access SQL databases, they should be stopped prior to the restore.

1. From the CommCell Browser, right-click the SQL Server instance you want to restore, click All Tasks, and then click Restore SQL Server.
2. On the SQL Server Restore Options dialog box, select Unconditionally overwrite existing databases or files, and any other applicable restore options.
3. Select OK.

This task is now complete.

Back To Top