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.

This page covers:

• SQL restore considerations — Why a full recovery is often easier; user and system databases.
• Hyper-V considerations — Restoring SQL Server from the Hyper-V host; restoring a full guest is a recovery.
• Data container considerations — Restores mount Data containers automatically; how to mount one yourself.
• How to restore an SQL Server — The steps, from the Files & Apps tab to starting the restore.

SQL restore considerations

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.

Hyper-V considerations

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

Data container considerations

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 mount one yourself, see Manually mounting a Data container.

To learn more see, Data Container guide

How to restore an SQL Server

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 Files & Apps tab

   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

   

   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

   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.

   

   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.

   To restore from a backup made by another installation of BackupAssist, or by a local job that has been deleted, select Discover Backups. See Discover backups from another machine.

4. Select the data

   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.

   

   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

   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

      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

      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

   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.

   

   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

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

Your SQL Server restore has now been completed.

Download a free 30-day trial