Microsoft SQL Server Restore

Use the following procedure as a guideline for restoring Microsoft SQL Server after a data corruption event.

Depending on the type of corruption event you had, you can either restore the most current copy of the backed up data, or a historical version of the data. The recommended procedure is to restore the most recent copy of the data first. This will provide the least amount of data loss.

However, in a data corruption event, the most current backed up version may be corrupted. This is dependent on when the corruption occurred and whether the corrupted data has already been backed up. You may need to perform several restores, current then historic, until you restore a version from before the corruption occurred.

Important: LiveVault Corporation developed and tested these procedures. These procedures should only be implemented by personnel who are trained and experienced in Windows and SQL Server.

To restore SQL Server

1. Stop the SQL service.

2. If you are planning to restore the SQL Server over the Internet, suspend backup for the computer that is running SQL Server.

   Important: The purpose of suspending backup is to make the process of restoring the SQL Server easier in the event you need to perform iterative restores due to corruption. However, suspending backup means that all backup is suspended until you resume it. If this is an issue for you, please contact Customer Service for assistance. 

   This step is only recommended if you are restoring over the Internet. If you select Automatic delivery and we decide to restore your data over the Internet, then we will suspend your backup at that time if it is deemed appropriate.

3. Follow the steps in Restoring Files and Directories, or Databases to restore SQL Server using MyLiveVault. When requesting the restore it is important to do the following:

   • When specifying the files and directories to restore, ensure that you select all the files comprising the SQL Server.

   • Only select the Network delivery option if you are sure that you have sufficient bandwidth and the connection stability to restore your data. Otherwise an additional restore using media may be required, delaying your restore.

   • If you choose Network delivery, set the following restore options:

     • Choose the Auto-rename the existing file option.

       Important: When restoring files/directories, we strongly recommend using the option Auto-rename the existing file, as this provides you the greatest flexibility. However, this option requires that you have sufficient disk space available for both copies (restored and existing copies) of each file. If you do not have sufficient disk space, the restore will fail. If sufficient disk space is not available, you may choose to use one of the overwrite options (for example, Always overwrite existing file with restored file). However, once a file is overwritten, you cannot retrieve it except by restoring another version. We do not usually recommend using the overwrite options.

     • Choose the Restore the original NTFS security descriptors option.

4. If your data is restored on media, after you receive the media with your restored data, refer to the media restore steps to restore the data from the media to your computer. During the media restore, set the following restore options:

   • Choose the Auto-rename the existing file option.

     Important: When restoring files/directories, we strongly recommend using the option Auto-rename the existing file, as this provides you the greatest flexibility. However, this option requires that you have sufficient disk space available for both copies (restored and existing copies) of each file. If you do not have sufficient disk space, the restore will fail. If sufficient disk space is not available, you may choose to use one of the overwrite options (for example, Always overwrite existing file with restored file). However, once a file is overwritten, you cannot retrieve it except by restoring another version. We do not usually recommend using the overwrite options.

   • Choose the Restore the original NTFS security descriptors option.

5. After the restore is complete, restart the SQL service. It should recover itself from the data provided in the restore.

   Important: If the SQL service will not restart, Windows may have assigned the restored Microsoft SQL Server directory a different short name than its original short name. For more on short names and restores, see Short names can be inconsistent between the source files and restored files in the Help topic Accessing Restored Files. To address this issue, compare the ImagePath value of the MSSQLSERVER service in the Windows registry with the restored SQL directory short name. If the directory names are different, do one of the following:

   • In the Windows registry, edit the ImagePath value to match the restored SQL directory short name.

   • Rename the restored directory short name to match the MSSQLSERVER ImagePath value. This may require renaming other restored directories also.

6. Verify that the restore was successful, for example, your database is no longer corrupted. If the restore was successful, go to the next step. If you need to restore a different version, perform this procedure again choosing an older version.

7. Resume backup if it was suspended for this restore procedure.

Related Information

Microsoft SQL Server Backup Configuration