Page tree
Skip to end of metadata
Go to start of metadata

Restore a Microsoft SQL Server

Microsoft SQL Server backups can be restored to the original machine or to an alternate machine.

Additional Requirements

Microsoft SQL Server restores have many additional requirements, including some requirements unique to specific Restore Locations.

Requirements for all database restores

  • Do not run backup and restore operations of a MS-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 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 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 user account, and add it to the sysadmin server role.
  • The log-on user for the SQL Server service must have full permissions to the folder that was chosen for restore.
    • Locate this information in Services.msc. The user will be listed as the log-on user for the SQL Server service that matches the instance(s) that are to be restored.
  • System databases, such as model, master, and msdb, should be restored separately from user databases.
    • Restore system databases first.

Requirements for restore to an alternate machine

The Restore a Copy of Database to Original or New Location and Restore to a New Location and Overwrite Original Database options, discussed below, may both be used to restore to an alternate machine.

  • The SQL Server instance name must be exactly the same on the new machine as it was on the original machine. The restore will fail if the instance names do not match.
  • Restore of system databases such as master, model, and msdb require that the SQL Server version exactly match between the new machine and the original machine. The restore will fail if there is a version mismatch.
    • Example: The model database of a SQL Server 2005 database cannot be restored to anything other than a SQL 2005 instance.
  • Restore of user databases require that the SQL Server version on the new machine be the same as or higher than the original machine. 
    • Example: A user database from a SQL 2008 instance can be restored to another SQL 2008 instance or a SQL 2012 instance. It cannot be restored to a SQL 2005 instance.
  • Restore of user databases require that the new machine be running the same or higher version of Windows operating system as the original.
    • Example: A SQL 2008 instance running on Windows Server 2003 can be restored to a SQL 2008 instance running on Windows Server 2003, 2008, or 2012.
    • Example: A SQL 2008 instance running on Windows Server 2008 cannot be restored to Windows Server 2003. It can be restored to Windows Server 2012.

 

It is recommended that system databases, such as model, master, and msdb, be restored only to the original machine 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, as shown here.

If system databases are restored, they must be restored separately from user databases. System databases should be restored first.

Special Options

Run DBCC CHECKDB after restore

When selected, this option verifies the integrity of the database(s) after restore. It is off by default. 

Restore To

The Restore To options have been replaced for Microsoft SQL Server restores with the following

  • Original Location
  • Restore a Copy of Database to Original or New Location

  • Restore to a New Location and Overwrite Original Database

Each Restore Location may have additional requirements beyond those found above. Each Restore Location option is designed to fill a specific purpose.

Restore To: Original Location

This option will restore the selected databases to the location where it was originally located at the time of backup. There are no additional requirements.

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 ZCB will treat the current database location as the "Original" location.

Restore To: 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.

Information

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

Additional Requirements

  • System databases, such as master, model, and msdb, cannot be restored using this method.
  • To restore the database to the Original location, the Path field must be blank.
  • When restoring to the Original location using 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, he cannot make any changes to the selection list unless he toggles 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 as 'SalesTeam' and restored to its original location.

  1. Go to the Restore page and select 'Sales' database in the File Path view. 
    1. It is not necessary 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, ZCB will automatically select 'Sales' database from all other backup runs that are required for restore.
  2. From the 'Restore To' drop down, select '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 'New Name' field.
    2. Keep the 'Path' field blank since we want to restore it to the same old 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 on the Restore button.
  6. A confirmation box will appear. Click OK to restore or Cancel to cancel.
  7. After 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 exists at 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA' with a name 'MarketingTeam' that will be created at 'E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA'.

  1. Go to the Restore page and select the 'Marketing' database in the File Path view. 
    1. It is not necessary 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, ZCB will automatically select 'Marketing' database from all other backup runs that are required for restore.
  2. From the 'Restore To' drop down, select '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 choose a restore folder
    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 on the Restore button.
  6. A confirmation box will appear. Click OK to restore or Cancel to cancel.
  7. After restore completes, both the 'Marketing' & 'MarketingTeam' databases will appear in the SQL Management Studio.

Restore To: 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 restore.

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 like Restore a Copy of Database to Original or New Location in such a situation.

Information

  • SQL recovery is performed during the restore.
  • The database file names (.ldf & .mdf) remain the same as before.
  • ZCB 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.

Additional Requirements

  • System databases like 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, he cannot make any changes to the selection list unless he toggles between Restore Methods.

Procedure to Restore to a New Location and Overwrite Original Database

In the following example, we describe a database '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. Go to the Restore page and select the 'Finance' database in the File Path view.
    1. It is not necessary 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, ZCB will automatically select 'Finance' database from all other backup runs that are required for the restore.
  2. From the 'Restore To' drop down, select the 'Restore to a New Location and Overwrite Original Database' option.
  3. The 'Edit Restore Path' dialog box will appear.
    1. Click inside the 'Path' field and choose a restore folder.
    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 on the Restore button.
  6. A confirmation box will appear. Click OK to restore or Cancel to cancel.

Database ownership after restore

This section does not apply to restores to the Original Location, as database ownership will not change.

The amandabackup 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:

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

Because we require that the amandabackup user be added as a Sysadmin, we know that the amandabackup user satisfies these requirements.

After 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: http://technet.microsoft.com/en-us/library/ms190909%28v=sql.105%29.aspx

  • No labels