Home Print  
StoreGrid Vembu Technologies

Restore SQL Server Backup Data

Overview

You can restore your SQL Server databases using your most recent full backup. This section provides information on restoring SQL Server databases using StoreGrid.

How to get here in the StoreGrid Web Console?
 In the top menu, select: "Restore". It will display the "List Of Backup For Restore" page. In the Action Column of the (SQL) backup schedule you want to restore, click on the Restore icon.

Requirements

SQL Server 2000 / 2005 / 2008.

Restore SQL Server Backup Data

In StoreGrid, restoring SQL Server database is quite simple. The following are the steps to be followed while restoring SQL Server data:

  • Step 0: Decide how to restore

    Decide on whether the SQL Server database data has to be restored from the backup server or if you wish to use data which had been previously restored from the backup server to a local folder. This step has the following options:

    - Restore data from backup server
    - I have previously restored data from my backup server to a local folder. I now want to use this data to restore the SQL Server database.

    1. If the second option is selected to restore SQL Server database from the previously restored data, then ensure that :

      1. SQL Server database was restored from backup server locally through the menu "Customer Management->Customer Name->Client Name->List of Backups->Actions->Proceed to Restore Backup" and transferred to client machine.
        or

      2. You had chosen the option "Restore data from backup server only (I will separately run StoreGrid through the 'locally restored files' to restore the SQL Server later)" in "Confirm SQL Server Restore Settings" of the Restore SQL Server Database page in the previous restore.

    2. Enter the local restore location and click "Submit Location" button to restore SQL Server data from the local folder.

  • Step 1: Choose the backup server (in case of multiple servers)

    This step allows you to choose the backup server if the backup schedule was configured to multiple servers. This step has the following options:

    • Backup Name - The backup name is a non editable field here.

    • Backup Server - The backup server is non-editable if the backup schedule is backing up to only one server. But if the replication feature is enabled in the backup server for the client and replication server name is added/discovered in client machine, then choose the server from the choice box.

  • Step 2: Select backups for database restore

    This step allows you to choose the database name and full backup version based on timestamp. This step has the following options:

    • SQL Server Instance Name - The SQL Server Instance name is a non editable field here and this will display the instance name from which SQL Server database was configured.

    • Database Name - The database name will also be a non-editable if the backup schedule is configured with single database. If the schedule is configured with multiple database, then you need to choose the database name here from the choice box. Select the database you wish to restore.

    • Show backups after - From the drop-down list, select the most recent full backup version based on timestamp to restore from. Once recent full backup version is selected and the "Load" button is clicked, StoreGrid will list all the backups for the selected database, starting with the one selected in this field.

    • Load - Based on the values selected in the fields"Database Name" and "Show backups after", clicking this button will list the files backed up to the backup server (available for restore).

    • Select Files for database restore - This section will list the files based on what timestamp/version and database name you choose in the "Database Name" and "Show backups after" fields. Here is where you have to select the files to be restored from the server. StoreGrid displays a list of the backups available for the database selected in "Database Name", starting from the first backup selected in "Show backups after" fields.

      This section has the following fields:

      • Type - The type of backup, value can be either FULL/DIFFERENTIAL/TRANSACTION-LOG backups.
      • Backup Date - The time at which the backup was transferred to backup server.
      • File Name - The name of the file backed up.

  • Step 3: Confirm Restore Settings

    • Password - If you had password protected the backup schedule, you will need to provide the password here.

    • Restore Location - You can specify the location to which the backup data has to be restored. The restored files will be saved in this location, but with their full original path – that way you know exactly where these files were in the first place !

  • Step 4: Configure SQL Server Database Restore

    This step helps you to restore backed up data right into the SQL Server database. By default, the option "After restoring data from my backup server, continue to restore the data into the SQL Server database" is selected. Alternatively you can also choose the option "Restore data from backup server only (I will separately run StoreGrid through the 'locally restored files' to restore the SQL Server database later)".

    This step has the following option:

    • Restore as database - This drop-down list allows you to select an existing database, or, if you want to restore to a new database, type in a new database name. For a new database, the database will be created and then the contents of the backup will be restored. For an existing database, the contents of the backup will be restored over the existing database.

    • Overwrite the existing database - If you are restoring backups and you want to overwrite an existing database with a different name, you must use choose this option. If you are restoring backups to the same database, this option is not required.

    • Restore the database file as - This displays the name and location of the physical files used when you restore your database. The location and names of the files are stored in the backup file, so if you are restoring to a different server or to a different database name, you must change these options for both the data file and the transaction log file.

    • Recovery State - The Recovery state panel determines the state of the database after the restore operation. The default behavior is:

      • Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)

      Note: Choose this option only if you are restoring all of the necessary backups now.

      Alternatively, you can choose either of the following options:

      • Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

      • Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

        StandBy file – A file required by SQL Server to track incomplete transactions if the backup is restored in standby state. The Standby file is used to undo uncommitted transactions when the database is brought fully online. If the Standby file does not exist, it will be automatically created.

      Restore and Cancel Button - Once all the options are set and the files and folders to be restored are selected, click on the Restore button to restore the files and folders. The status of the restore operation will be displayed on a pop-up browser window.

Troubleshooting Tips

SQL Server restore fails with one of the following errors.
  1. "SQL Server restore aborted because of error in getting the virtual device backup configuration. Check your database properties. Check your database properties."
  2. "Exclusive access could not be obtained because the database is in use."
 Show/Hide

Cause : If more than one connection was opened to the database which was configured for restore.

Solution 1: Make sure that no other user is connected to that database. If possible close the connections open to that database.

Solution 2: Alternatively, change the database property to single user mode before restore operation and change the database property again to multiple user mode after restore.

Restore SQL Server Database page is not listing database available to restore. Show/Hide

Cause : This would occur, if StoreGrid client configurations are recovered from backup server through Client Disaster Recovery option.

Solution : Please re-enter the SQL Server Instance login details in "SQL Server Connection Properties" page through the menu "Backup->Create SQL Server Backup Schedule" once and then try to restore the SQL Server database.

Restore SQL Server Database page is not listing databases available to restore. Show/Hide

Cause 1: If backed up SQL Server databases are Unavailable(OFFLINE/DETACHED/DELETED) to perform restore. Hence logical file name is not available to perform restore.

Cause 2: Check if backed up SQL Server databases contain any special characters like forward slash(/), backward slash(\), question(?), colon(:), asterisk(*), lesser than(<), greater than(>), pipe(|) and plus(+).

Solution : Download the backed up SQL Server database dump file to client machine using the following steps:

  1. Restore SQL Server database locally in backup server through the menu "Customer Management->Customer Name->Client Name->List of Backups->Actions->Proceed to Restore Backup" and transfer the restored data to client machine.
    or

  2. Choose the option "Restore data from backup server only (I will separately run StoreGrid through the 'locally restored files' to restore the SQL Server later)" in "Confirm SQL Server Restore Settings" of the Restore SQL Server Database page to restore the SQL Server database locally in client machine.

  3. After downloading the files from server, use second option (I have previously restored data from my backup server to a local folder. I now want to use this data to restore the SQL Server database) in Step 0 of the "Restore SQL Server Database" page to restore the SQL Server database.

I am backing up SQL-Server using the Advanced plugin. How do I restore the SQL Server database in a different machine. For Example - If MACHINE A is configured to backup SQL Server database to SERVER A, how do I restore the backup from SERVER A to MACHINE B. Show/Hide

Solution : To restore SQL Server database in MACHINE B, please follow the steps given below:

  1. SQL-Server data could be restored from backup server A to MACHINE B in one of the following ways:-

    1. Restore SQL Server database backup schedule data locally in backup server A through the menu "Customer Management->Customer Name->Client Name->List of Backups->Actions->Proceed to Restore Backup" and then manually transfer the data to MACHINE B.

      OR

    2. From MACHINE A itself, do a regular restore by choosing the option "Restore data from backup server only (I will separately run StoreGrid through the 'locally restored files' to restore the SQL Server later)" in Step 4. And then manually transfer the data to MACHINE B.

  2. Once the data has been transferred to MACHINE B, you need to edit the restore.conf located in "<RESTORED_LOCATION>/plugin/" folder.

    1. Open the restore.conf located in "<RESTORED_LOCATION>/plugin/" folder.

    2. Change the SqlInstanceName attribute value to MACHINE B SQL Server Instance name. And before restoring the SQL-Server, go to the "SQL Server connection properties" page and then login to MACHINE B SQL Server Instance at least once.

    3. Change the ClientIdentity attribute value to MACHINE B StoreGrid Identity.

    4. Save and close the file.

  3. Database to be restored should not exists in the Mapped Drive of the MACHINE B. Please check that database files to be restored exists in the Local Drive of the MACHINE B, if not please transfer the restored data to the Local Drive of the MACHINE B .

  4. In the top menu of the StoreGrid WebConsole, select: "Restore". It will display the "List Of Backup For Restore" page. In the Action Column of the any SQL backup schedule, click on the Restore icon.

  5. Choose the second option in "Step 0: Decide how to restore" settings to restore SQL Server database from the previously restored data. And then enter the local restore location and click "Submit Location" button to restore SQL Server data from the local folder.

  6. And Proceed with the remaining steps in Restore SQL Server Database [Advanced Plugin] page to restore the SQL Server database.

Note : The above solution is applicable when the sql-server backup is configured using the Advanced Plugin. In the case of SQL Server Basic Plugin, check the Restore SQL Server Database [Basic Plugin] page.

Limitations

  1. If a backup schedule is configured with multiple database(s) (in client-side) only a single database is allowed to restore to the SQL Server at a time.

  2. It is recommended that you provide an empty directory for the local restore location. There must be sufficient space in the directory to store the files; also ensure that the local restore location does not exceeds the 256 character limit.

  3. Previous schedules cannot be restored in the client side after changing the dump location.

  4. While restoring the deleted MS SQL Server database(s) using Advanced Plugin, the same cannot be restored directly to the MS SQL Server. First the deleted database(s) should be restored locally in the client machine and then should proceed with the 'Plugin local restore' feature to restore the deleted MS SQL Server database(s).
Print  
© Vembu Technologies Technical support-