SQL Server restore

BackupAssist File Protection, File Archiving and System Protection use the Volume Shadow Copy service (VSS) to back up your SQL Server while it is running. BackupAssist can use these backups to restore your SQL Server or individual SQL databases.

If you have a transactional backup created using the SQL Continuous add-on you can also perform SQL Point-in-Time restore restores.

SQL Server restores

Restoring SQL databases back into their SQL Server is a straight forward process. However, complications can arise when you restore SQL databases into a new SQL Server or try to restore the entire SQL Server application. This is because once SQL Server is installed, it may have dependencies with the operating system. Specific SQL Server installation options can also create compatibility problems for full SQL Server restores or database restores into new SQL servers.

For this reason, it is often easier to perform a recovery of the full SQL Server rather than just restoring the SQL Server application. If you want to be able to perform a recovery if your SQL Server, your backup rotation will need to use or include System Protection bare-metal backups. To learn more, see System Recovery

Note: Restoring an SQL Server may require preparations and considerations behind the scope of the restoration of the data. Microsoft’s SQL Server restore resources should be consulted before planning and performing an SQL restore.

SQL database restores

Most SQL restores will be for SQL databases used by your business, but an SQL restore can also be used to restore SQL Server databases. It is important to understand the difference.

User databases

These are the databases that are used to manage your information.

System databases

These databases are used by the SQL Server to run. The main database is the master database, which is composed of important system tables and catalogs that are required to rebuild your SQL Server. Only a backup that includes all of the system databases can be used to perform a Master database restore. You must select these system databases from the database list when your backup is made.

If you are restoring after a disaster or performing a new installation or a migration, you should restore the master database before restoring any other databases.

Host files

The SQL Server restore option allows you to restore an SQL Server or individual databases from the Hyper-V host to their original location or a new location.

Full guest

The restoration of a Hyper-V guest is a recovery. To lean more, see Full VM Recovery

System Protection backups can use Data containers as backup destinations. BackupAssist automatically mounts Data containers when restores are performed. However, there may be times when you want to do this manually. For example, if you want to check what is inside a Data container or have it available for another task.

To manually mount a Data container:

  1. Open Disk Management.

    2. For Windows Server 2008 R2, SBS 2011 and Windows 7

    1. Click the Start button.
    2. Type diskmgmt.msc into the Search Programs and Files text box.
    3. Click OK.

    For Windows Server 2012 and Windows 8 and later

    1. Open Run from the Apps screen.
    2. Type diskmgmt.msc into the Search Programs and Files text box.
    3. Click OK.

    The Disk Management console will open.

    Disk Management Console

  2. Select Action from the Disk Management menu.
  3. Select the menu option Attach VHD.
  4. Enter the path of the Data container that you want to mount and click OK.

    5. You will now have a drive named BA_VHD_Container in your list of available drives.

  5. Right click BA_VHD_Container, select Change Drive Letter and Paths, and select Add.
  6. Select a drive letter for the Data container, using the drop down box, and select OK.

    7. Mount Data Container

You can now browse the Data container using Windows Explorer.

To learn more see, Data Container guide

The following instructions use a System Protection backup as the example.

To restore an SQL Server or individual databases, follow these steps:

  1. Select the File/Apps tab

    2. The Files\Apps tab has a Home page and a Tools page. The Home page is the default page and the starting point for performing a restore. The Tools page should only be used by experienced users or with the assistance of BackupAssist Technical Support.

  2. Select SQL Server

    3. Files/Apps

    This step starts the guided restore process and will take you to the SQL Server restore screen. There is a Learn more about Restore link at the bottom of the screen.

  3. Select the backup

    4. The SQL Server restore screen displays the SQL Server backups created by this installation of BackupAssist. To select the backup that you want to restore from, click on a backup’s name.

    SQL backup selection

    There are tabs above each volume’s backup list that can be used to filter the list of backups shown. Using these filters will help in locating the required backup.

    The display tabs available are:

    • All - shows all backups available.
    • The Last 7 days and Last 30 days tabs - shows the backups within those ranges.
    • Custom - allows you to select a specific date range and display backups for that period.

    Discover Backups

    This option allows you to add and restore from backups created by other installations of BackupAssist and backups made by local jobs that have been deleted.

    Discover backups

    When you select the Discover Backups button, the Discover Backups screen will appear.

    • If you select Show manually added backups, backups from other BackupAssist installations that were added using the + option will be available when you go back to the main restore screen.
    • If you select Show backups made by deleted jobs, backups made by deleted backup jobs will be displayed and available when you go back to the main restore screen.
    • If you select Show backups made by existing jobs, only backups made by the current list of local backup jobs will be shown. This is the default list you saw before using the Discover Backups option.
    • Selecting the plus symbol will open a browse dialog that can be used to locate backups made by other BackupAssist installations and add them to the Discover Backups screen.
    • Selecting the cloud symbol will open a dialog from where you can select and configure the cloud destination that you want to restore from. This option allows you to restore from a backup created on another machine or a deleted job.
    • Selecting Delete, removes any selected backups that were added using the + option.

    Selecting only one of these options means only those backups will be shown when you go back to the main restore screen. Combinations of these 3 options can be selected to get the set of backups that you want.

  4. Select the data

    5. When you select the backup that you want to restore from, the Integrated Restore Console will open. Use the console to select the SQL Server or the SQL databases that you want to restore.

    Integrated Restore Console

    What to restore

    • Use the left pane to locate and select the data that you want to restore.
    • The right pane will display the contents of the folder selected in the left pane.
  5. Select the destination

    6. Use the Integrated Restore Console to select the location that you want to restore the data to. You can also use this step to select other restore options like the data overwrite rules.

    Where to restore

    Follow these steps to select the restore destination and restore options:

    1. Under Where to restore select To original location or To Specific location.
    2. Use the Browse button to locate and select the restore destination.
    3. Use the drop down box to set the overwrite rules. The overwrite rules will apply if the files being restored encounter files with the same name in the restore destination.
      • Overwrite existing files: the restored files will overwrite files in the restore destination.
      • Do not overwrite existing files: the restored files will not overwrite files in the restore destination. This means the files will not be restored.
      • Only overwrite older files: if a source file has changed since the backup was made it will not be overwritten.
    4. Review the Restore NTFS security attributes option

      5. If you select this option, the NTFS security attributes the file had when it was backed up will be retained when the file is restored. The NTFS security attributes can be viewed in the Security tab on the file’s Properties

    5. Review the Take ownership of restored files option

      6. Selecting the Take ownership of restored files tick box will give the current user ownership of the restored files. The user is shown to the right of the text box description.

  6. Start the restore

    7. When you select the Restore button, the restore process will begin. The Integrated Restore Console will display information about the restore job and provide status updates as the job runs.

    Completed Restore

    List all processed files and folders

    Selecting this link will open notepad and display a list of the files restored, including their full path.

    Encrypted backups

    If your backup is encrypted, you will be prompted for the encryption password when the restore job accesses the backup.

    This is the password entered in the Set up destination step when the job was created.

    If you used BitLocker, the password is required. A BitLocker key can only unlock a drive to create a backup.

    Note: It is important that you keep a copy of your password in a safe place, as we cannot assist you with opening password-encrypted files if your password is lost or forgotten.

  7. Select Done

    8. Once the restore has finished, selecting Done will return you to the main UI.

Your SQL Server restore has now been completed.