Restore Microsoft SQL Server database

    • 9 min read
    On this page

    Introduction

    Although the SQL plug-in provides a basic ability to backup and restore SQL databases, there are important considerations when planning an effective backup/restore strategy. This document will focus on restoring SQL databases, a process which is typically more involved than backing them up. Where applicable, backup options and best practices will be called out when they have important ramifications on the restore process.

    Relocation

    There are two ways in which a SQL database can be restored:

    • To the SQL server from which it was backed up (in-place)

      This is the default behavior when you restore databases. If you do not change the restore to client (and you have not uninstalled and installed the SQL Server again on that client), you will be doing an in-place restore back to the original SQL Server.

    • To a SQL server other than the one from which it was backed up (relocated)

      If you change the default restore to client (or you have uninstalled and installed the SQL Server again on that client), you will be doing a relocated restore to an alternate SQL Server.

    Regardless of the method used, instance names must remain the same.

    The SQL plug-in requires that an instance on the target SQL Server be named exactly the same as the instance from which the backup was taken. If this is not the case, an error will occur during the restore attempt. There is currently no way to specify an alternate instance name at restore time.

    This is most likely to be an issue when doing a relocated restore since the alternate SQL Server may not have an instance of the same name. In this case, make sure you create (if one does not already exist) an appropriately named instance before attempting the restore.

    To ensure success in the widest range of conditions, the SQL plug-in applies relocation logic when restoring SQL databases. This means:

    • The SQL Server is queried to determine where databases should be stored on that particular server.

    • A unique folder will be created, at the location identified above, for each database restored.

      This folder will be named for the database whose files it will contain. This ensures that multiple databases with the same-named database files can be restored successfully.

    This relocation logic behavior can be disabled via the registry key on the client computer to which you will be restoring. If you disable relocation logic, the restore will only succeed if the exact original path to the database files is available on the destination SQL Server. This will usually only be true if you restore back to the original SQL Server. Therefore, relocated restores will likely fail if this logic is disabled.

    To disable relocation logic for all databases on all servers, set the following registry key/value pair:

    HKEY_LOCAL_MACHINE\SOFTWARE\Revinetix\RVX-Backup\Plugins\MsSQL
    DisableRelocate=true

    To disable relocation logic for all databases on a particular server, set the following registry key/value pair ([ServerID] is a 0-index-based value; the Name key can be used to identify the server):

    HKEY_LOCAL_MACHINE\SOFTWARE\Revinetix\RVX-Backup\Plugins\MsSQL\Servers\[ServerID]
    DisableRelocate=true

    To disable relocation logic for a particular database on a particular server, set the following registry key/value pair ([ServerID] and [DatabaseID] are 0-index-based values; the Name key can be used to identify the server/database):

    HKEY_LOCAL_MACHINE\SOFTWARE\Revinetix\RVX-Backup\Plugins\MsSQL\Servers\[ServerID]\Databases\[DatabaseID]
    DisableRelocate=true

    To remove disabling in all cases, set the key value to false, or delete the DisableRelocate key entirely.

    Restore order

    If you are restoring one or two, specific user (non-system) databases, you can simply restore them without concern for the order in which they are restored. But, if you also need to restore system databases, need to restore all databases, or are attempting to recreate a server from a backup, the order (and method) you use can be very important.

    The SQL plug-in does not enforce any particular order, as is it not built to be a SQL Server restoration tool. It will backup/restore databases, but you will need to be aware of certain SQL Server requirements when it comes to data protection via backup/restore of SQL databases.

    Consider the following when restoring SQL databases:

    • System databases should be restored first, individually, and in this order:

      • master

      • msdb

      • model

      • distribution

    • User databases should be restored after all system databases, grouping and order are not a factor.

    Lets say you need to restore all databases from a backup, this includes system and user databases. Perform the restore as follows:

    1. Restore the master system database:

      1. Browse the job (or set of jobs) and select ONLY the master entry.

      2. Restore the selected database.

      3. Verify that the job restored successfully.

      4. Verify that the SQL Server is running properly after the restore.

    2. Restore the msdb system database:

      1. Browse the job (or set of jobs) and select only the msdb entry.

      2. Restore the selected database.

      3. Verify that the job restored successfully.

      4. Verify that the SQL Server is running properly after the restore.

    3. Restore the model system database:

      1. Browse the job (or set of jobs) and select only the model entry.

      2. Restore the selected database.

      3. Verify that the job restored successfully.

      4. Verify that the SQL Server is running properly after the restore.

    4. Restore the distribution system database:

      1. Browse the job (or set of jobs) and select only the distribution entry.

      2. Restore the selected database.

      3. Verify that the job restored successfully.

      4. Verify that the SQL Server is running properly after the restore.

    5. Restore the user databases:

      1. Browse the job (or set of jobs) and select the remaining entries (do not select any of the system databases you have already restored).

      2. Restore the selected database(s).

      3. Verify that the job(s) restored successfully.

      4. Verify that the SQL Server is running properly after the restore.

    This procedure can be modified to exclude any databases that you either do not have in your backup, or do not care to restore. The most important issue is the order and grouping of the restores for the respective databases.

    It is not recommended that you simply select the entire SQL: folder and restore it all at once. This will likely result in conflicts between the system and user databases at restore time.

    The restore procedure discussed here assumes that you are restoring to a running SQL Server. This implies that the system databases are at a minimal state of functionality that is required for a SQL Server instance to start up. If this is not the case, you will have to re-install the instance, repair the system databases using the Microsoft prescribed methods, or restore and attach the databases manually. Refer to the link below for more information on these topics.

    The following Microsoft articles are available for more information on specific SQL Server backup/restore topics:

    Recovery models

    A databases designated recovery model has a big impact on its backup/restore strategy. Primarily, this has to do with how SQL logs are handled. Here is how SQL backup types are mapped to backup levels used by the appliance:

    SQL backup type Backup level
    Full Full
    Differential Differential
    Log Incremental

    The recovery models impact the backup/restore process in the following ways:

    Simple
    Databases using this recovery model can only be backed using the full or differential level. Log (incremental) backups are not supported using this model. As a result, incremental backups are promoted to differential for databases using this recovery model.

    When a full or differential backup of a simple recovery model database is taken, the transactions logs are automatically rolled into the database (committed) and truncated before the backup. Restoring these types of databases is a straightforward affair, no additional steps or steps considerations are needed.

    Full/Bulk-Logged
    Databases using this recovery model not only support log (incremental) backups, they require them. The transaction logs are truncated when a log (incremental) backup is taken, but not when a full or differential backup is taken. In fact, the logs can grow indefinitely until a log (incremental) backup occurs or some other event (called a checkpoint) is triggered.

    It is the implication on restores that is most important for this recovery model. In SQL 2005 and later, databases are required to have all log transactions backed up before the database can be overwritten by a restore operation. If this requirement is not fulfilled, the following error can be seen when attempting a restore:

    The tail of the log for the database [DatabaseName] has not been backed up.

    Use BACKUP LOG WITH NORECOVERY to back up the log if it contains work that you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

    The current version of the SQL plug-in does not support running a log (incremental) backup automatically at restore time. But, you have some options for dealing with this problem, they are described below.

    Handling tail log backups at restore time

    If a database using the Full/Bulk-Logged recovery model causes a tail log error during a restore attempt, you have a few choices:

    1. Run a manual, incremental backup of the database you are restoring on the destination server.

      Then, with as little delay as possible, restore the database. The reason for this is that you need to have an empty transaction log in order for the restored database to overwrite the existing one. Running the incremental backup first results in the log being truncated before the restore attempt, thus avoiding the tail log error.

    2. Force the restored database to overwrite the existing one.

      By setting a specific registry entry on the destination client computer, the database can be overwritten during a restore attempt. Keep in mind, but, that any outstanding transactions in the log of the destination database will be lost. Do this only if you do not need the latest transactions from the destination database to be backed up.

      To force a database to be overwritten at restore time, set the following registry key/value pair ([ServerID] and [DatabaseID] are 0-index-based values; the Name key can be used to identify the server/database):

      HKEY_LOCAL_MACHINE\SOFTWARE\Revinetix\RVX-Backup\Plugins\MsSQL\Servers\[ServerID]\Databases\[DatabaseID]
      ReplaceOnRestore = true

    3. Change the database to use the Simple recovery model.

      This will only work for backups/restores going forward since existing backups would still be affected by the existing recovery model.

      To fully understand the scope of this type of change, refer to the links below for more information on recovery models.

    To learn more about SQL database recovery models, refer to the Recovery Model Overview.