August 30, 2017
Recovering Microsoft SQL Server Objects
Microsoft SQL Server OLR
Microsoft SQL Server is one of the most business-critical services. SQL Server data loss or undesired data modification can happen for many reasons, including the human factor, hardware failures, malicious actions or virus attacks. Loss of SQL objects due to any causes can have a significant impact on a business. Microsoft SQL Server recovery can be a complex and time-consuming task, which in particular, concerns big SQL databases.
To perform SQL server restore, you first need to perform an application-aware backup. This will ensure that data inside a database is consistent. Legacy backup requires restoring an entire VM with the SQL server, and only then restoring individual objects. This is a time-consuming process and puts an additional load on your environment. In contrast, NAKIVO Backup & Replication allows recovering SQL objects directly from compressed and deduplicated VM backups. Let’s take a closer look at how it’s done.
For example, you have the TestLabDB SQL database with Table_1 and Table_2 tables, which contain some important data:
At some point, someone has accidentally and permanently deleted the Table_1 table:
As a result, no one can get important data from Table_1, and the application using this database gives an error when accessing this table. To avoid unnecessary delays or long-term unavailability of the business-critical application, you must quickly react to the incident and perform fast recovery.
Instant SQL Server Object Recovery with NAKIVO Backup & Replication
First, you need to open the NAKIVO Backup & Replication web interface and find the necessary virtual machine (VM) backup in the Job dashboard. Under the Overview pane, select the specific backup job and check the VM name.
Click Recover and then click Microsoft SQL Server objects under Granular Recovery:
This action will open the Object Recovery Wizard for Microsoft SQL Server. Select a backup in the left pane and a recovery point in the right pane. Verify that Automatically locate application databases check box at the bottom left is checked.
At the second step, you should specify an SQL Server instance which will be used for recovery and fill out several related fields
- Recovery server: Select the SQL Server which will be used to perform recovery
- Username: Type your login and, if necessary, domain name of the user that has access rights to the particular SQL Server and database
- Password: Type the user password
When credentials are checked and accepted you will be able to choose an SQL instance, which is MSSQLSERVER in our case.
At the third step of the wizard, navigate to the table that you need to restore and select the check box next to it.
At the final step of the wizard, you can configure:
- Recovery type:
- Recover to original location
- Recover to custom location
- Overwrite behavior:
- Rename recovered item if such item exists
- Skip recovered item if such item exists
- Overwrite the original item if such item exists
In our case, choose Recover to original location and Rename recovered item if such item exists.
After clicking Recover, the message that Object Recovery has started will appear. If you click Activities, you will be able to see the status of this job.
Here we can see that our job has already Completed.
Now let’s go to SQL Server again to verify recovery. The Table_1 is in its rightful place as expected.
Microsoft SQL Server is a business-critical application that is used by many organizations. Recovery of the deleted or modified SQL objects is vital for any business. Timely recovery is one of the top priorities for such processes. NAKIVO Backup & Replication gives you the ability to instantly recover SQL objects such as databases and tables, directly from compressed and deduplicated VM backups. You can recover the objects back to the source SQL server, to a different SQL server, or export objects to a custom location. NAKIVO Backup & Replication provides an easy way to recover Microsoft SQL Server objects within the shortest time.