Carbonite Support > Restoring a Microsoft SQL Serv...

Restoring a Microsoft SQL Server

  • This article is for Windows only

Microsoft SQL Server backups can be restored to the original computer or to a different computer.

Ensure that all Restore Requirements are met before starting the restore.

Restore Requirements

The following requirements must be met, whether restoring to the original computer or to an alternate computer:

  • The amandabackup / CarboniteUser user must have full access to the folder where the backups are stored.
    • For cloud backups, this is the chosen Download Folder.
    • For local backups, this is the folder where the backups are stored on the local disk.
  • Likewise, the amandabackup / CarboniteUser user must have full access to the folder(s) to which the backups are being restored.

Restore Requirements for all Database Restores

  • Microsoft SQL Server must be installed on the system.
  • Microsoft SQL Server must be started and healthy unless the rebuild system databases option is checked. Please see this Knowledge Base article for more information.
  • Do not run backup and restore operations of a Microsoft SQL backup simultaneously.
  • SQL 2005 restores of system databases, such as model, master, and msdb, will fail if other applications are actively connected to the MS-SQL server. Please disable any SQL query analyzers, the MS-SQL Management Studio, and other such programs before performing a restore of system databases.
  • The SQL Server VSS writer service must be running at the time of backup and recovery. Microsoft recommends that the SQL VSS Writer service be automatically started. MSDE writer is not sufficient for backup and recovery.
  • The amandabackup / CarboniteUser user must have access to the SQL server. To grant access:
    • Click the Allow Access button on the Backup page of any Microsoft SQL backup set, or
    • Add the user in SQL server Management Studio. Click on Security; Logins; Add New Login. Add the amandabackup / CarboniteUser user account and give it privileges.
    • You can just add it to sysadmin server role.
  • The log-on user for the SQL Server service must have full permissions to whatever folder is chosen for restore.

It is recommended that system databases, such as model, master, and msdb, be restored only to the original computer or an identical server. System databases contain configuration data. The Microsoft SQL Server may not start if system databases are restored to a different server. Migration of system databases to a non-identical server requires significant database administration knowledge. If system databases are restored, they must be restored separately from user databases. System databases should be restored first.

Additional Requirements for Restores to Another Computer

  • The SQL Server instance name must be exactly the same on the new computer as it was on the original computer. The restore will fail if the instance names do not match.
  • Restore of system databases requires that the SQL Server version exactly matches the new machine and the original machine. The restore will fail if there is a version mismatch.
    • This includes any restore that contains the "master", "model", and/or "msdb" databases, including restores in which the Rebuild System Databases option is enabled.
    • Example: The model database of an SQL Server 2005 database cannot be restored to anything other than an SQL 2005 instance.
  • Restore of user databases requires that the SQL Server version on the new computer be the same as or higher than the original computer.
    • Example: A user database from an SQL 2008 instance can be restored to another SQL 2008 instance or an SQL 2012 instance. It cannot be restored to an SQL 2005 instance.
  • Restore of user databases requires that the new computer be running the same or higher version of the Windows operating system as the original.
    • Example: An SQL 2008 database running on Windows Server 2003 can be restored to an SQL 2008 database running on Windows Server 2003, 2008, or 2012.
    • Example: An SQL 2008 database running on Windows Server 2008 cannot be restored to Windows Server 2003. It can be restored to Windows Server 2012.
  • The Restore a Copy of Database to Original or New Location and Restore to a New Location and Overwrite Original Database options may both be used to restore to an alternate computer.

Restore Locations

On the Restore page, you may choose where you wish to restore your database. Each option has special functionality designed to fill a specific purpose. Please select an option below for more information:

The sections below are collapsed. Please click the section title to open / close a particular section.

Restore Database to Original Location

This method will restore the selected databases to the location where it was originally located at the time of backup.

If the database to be restored is currently attached to the SQL instance, and the current location of the database files is different from the time of backup, then CSSB will treat the current database location as the Original location.
Restore a Copy of Database to Original or New Location

This method allows a user to restore the selected database with a new name to either its backup location or to a completely new location. This is similar to making a copy of the database with a new name to a new location.

Important Information

  • SQL Recovery is performed during the restore
  • The database file names (.ldf & .mdf) remain the same as before.

Requirements

  • All Restore Requirements must be met, as well as any SQL-specific requirements.
  • System databases such as master, model, and msdb, cannot be restored using this method.
  • To restore the database to the original location, ensure the path field is blank.
  • When restoring to the original location by this method, the original database must be deleted or detached from SQL before the restore begins.
    • If the original database still exists, the restore will fail.
  • Once a user makes a selection on the Restore page, they cannot make any changes to the selection list unless they toggle between restore methods.

Procedure to Restore a Copy of a Database to its Original Location

In the following example, we describe a database named Sales that needs to be renamed SalesTeam and restored to its original location.

  1. Navigate to the Restore page and select the Sales database in the File Path view.
    1. There is no need to select the database in all of the full, differential, and/or incremental backup runs. If one selects the Sales database in any one backup run, CSSB will automatically select the Sales database from all other backup runs that are required for restore.
  2. From the Restore Method drop-down menu, select the Restore a Copy of Database to Original or New Location option.
  3. The Edit Database Name & Restore Path dialog box will appear.
    1. Specify the new name SalesTeam in the New Name field.
    2. Keep the Path field blank since we want to restore the database to the same location.
    3. Save the changes.
  4. (Optional) Select the Run DBCC CHECKDB check box if you wish to verify the logical and physical integrity of all the objects in the specified database(s) after the restore completes.
  5. Click the Start My Restore button.
  6. After the restore completes, the SalesTeam database will appear in the SQL Management Studio.

Procedure to Restore a Copy of a Database to a New Location

In the following example, we describe how to make a copy of a database named Marketing that originally existed at 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA' with a database named MarketingTeam that will be created at 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA'.

  1. Navigate to the Restore page and select the Marketing database in the File Path view.
    1. There is no need to select the database in all of the full, differential, and/or incremental backup runs. If one selects the Marketing database in any one backup run, CSSB will automatically select the Marketing database from all other backup runs that are required for restore.
  2. From the Restore Method drop-down menu, select the Restore a Copy of Database to Original or New Location option.
  3. The Edit Database Name & Restore Path dialog box will appear.
    1. Specify the new name MarketingTeam in the New Name field.
    2. Click inside the Path field and browse to the target folder, which is E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.
    3. Save the changes.
  4. (Optional) Select the Run DBCC CHECKDB check box if you wish to verify the logical and physical integrity of all the objects in the specified database(s) after the restore completes.
  5. Click the Start My Restore button.
  6. After the restore completes, the Marketing and MarketingTeam databases will appear in the SQL Management Studio.
Restore to a New Location and Overwrite Original Database

This method allows a user to move the selected database to a completely new location. The original database will be overwritten as part of the restore.

Note: This option may still be chosen if the original database is not present. The Restore to a New Location and Overwrite Original Database option will behave exactly as Restore a Copy of Database to Original or New Location in such a situation.

Important Information

  • SQL recovery is performed during the restore.
  • The database file names (.ldf and .mdf) remain the same as before.
  • CSSB restores the database to the path specified by the user in the Edit Restore Path dialog box.
  • Details about the restore procedure are logged by the SQL Server in the Windows Event Viewer.

Requirements

  • All Restore Requirements must be met, as must as any SQL-specific requirements.
  • System databases such as master, model, and msdb cannot be restored using this method.
  • If the selected database exists on the SQL server before the restore procedure, the database files from the original location will be deleted and restored to the new location.
  • Once a user makes a selection on the Restore page, they cannot make any changes to the selection list unless they toggle between restore methods.

Procedure to Restore to a New Location and Overwrite Original Database

In the following example, we describe a database named Finance that originally existed at 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA'.

This database needs to be moved to a new location at 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA'.

  1. Navigate to the Restore page and select the Finance database in the File Path view.
    1. There is no need to select the database in all of the full, differential, and/or incremental backup runs. If one selects the Finance database in any one backup run, CSSB will automatically select the Finance database from all other backup runs that are required for restore.
  2. From the Restore Method drop-down menu, select the Restore to a New Location and Overwrite Original Database option.
  3. The Edit Restore Path dialog box will appear.
    Click the path field to select a path
    1. Click inside the Path field and browse to the target folder, which is E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA.
    2. Save the changes.
  4. (Optional) Select the Run DBCC CHECKDB check box if you wish to verify the logical and physical integrity of all the objects in the specified database(s) after the restore completes.
  5. Click the Start My Restore button.
Restore to an Alternate Location Without Recovery (FULL BACKUPS ONLY)

This method restores the SQL database files (.mdf and .ldf) to the specified location on disk. This option is available only for full backups performed with CSSB version 4.6 or earlier.

Important Information

  • No SQL recovery is performed during the restore.
  • This is a simple file level restore.

Requirements

  • Only Full backups can be restored using this method. Differential and/or Incremental backups cannot be restored.
  • Unlike the other SQL restore types above, you may use any computer name with any instance name.
Database Ownership After Restores
This section does not apply to restores to the original location, as database ownership will not change.

The amandabackup / CarboniteUser user is used to initiate the restore operations for Microsoft SQL Server. It will thus become the new owner of the restored databases during restores to a different computer or different instance.

This is necessary for several reasons:

  1. It cannot be assumed that the user who previously owned the database exists during the restore process.
  2. The user performing the restore must have the necessary permissions to apply transaction log backups to the newly restored database.
  3. Likewise, the user performing the restore must have full and total access to the database, including ownership changes.

Since we require that the amandabackup / CarboniteUser user be added as a Sysadmin, we know that the amandabackup / CarboniteUser user satisfies these requirements. After a restore, any system administrator can change the database ownership to any user. Please see this Microsoft Article for more information on how to change database ownership.

Feedback