Today I will tell you about two Commvault features for MS SQL backup, which are undeservedly bypassed: granular recovery and the Commvault plugin for SQL Management Studio. I will not consider the basic settings. The post is more likely for those who already know how to install an agent, set up a schedule, policies, etc. I talked about how Commvault works and what it can do in this .
Granular recovery
Option table level restore appeared in the Subclient properties relatively recently. It allows you to enable the ability to restore tables from a database without restoring the entire database from a backup. This is convenient when you know exactly where the error or data loss is. At the same time, the base itself is large and it will take a lot of time to restore it all.
This option has limitations:
- Tables cannot be restored to the original database, only to a different one.
- All tables are restored to the dbo schema. The table cannot be restored to a custom schema.
- Only a local SQL server account with system administrator rights is supported.
- The target server where we restore the table must run on Windows.
- On the target server, in addition to the SQL Agent, Media Agent and Java Runtime Environment must be installed.
- The database must use the Recovery model in Full mode.
- If the granular database restore option is enabled, the ability to run differential backup jobs is no longer available.
The table-level-restore option is disabled.
The table-level-restore option is disabled.
In my practice, there was a case when the client had the following schedule configured for the SQL server: one full backup once a week and 6 differential backups on weekdays. He enabled the table-level-restore feature, and differential backup jobs were failing.
Let's see what the restoration itself will look like.
1. Start recovery on the required agent.
2. In the window that appears, go to the tab Advanced Options. We choose SQL Granular Browse - View Content.
3. In the list that opens, select the database from which we will restore the table, and click Restore Granular.
4. In the dialog box, set up a database mount point from backup files (something like Instant Recovery technology).
Specify:
- name for the temporary database;
- how long to keep this restore point in days;
- server where we will mount the database. Only servers that fulfill all the necessary conditions mentioned above will be available in the list: with Windows OS, Media Agent and Java Runtime Environment installed, etc.
Click OK.
5. In the new window, click on List Recovery Points.
6. A list of mounted restore points will open. If the database is large, then you will have to wait. Then press Browse. A window for viewing tables from the selected database will appear.
While the list is being formed, the Recovery Points dialog is often closed, and then they cannot return there again. It's simple: right-click on the SQL server instance where the recovery point mount process was started. Go to All Tasks and select List Recovery Points.
7. If there are many tables, it may take some time to display them. For example, for a 40 GB database, the list is formed in about ten minutes. Select the desired table, click Recover All Selected.
8. In the new window, select the database where we will restore the table(s). In our case, this is the GPI TEST base.
9. After the restoration is completed, the selected tables will appear in the GPI TEST database.
Once a table has been restored to a temporary database, it can be transferred to the original database using Management Studio.
Plug-in from Commvault for SQL Management Studio
DBAs do not always have access to the backup system (BMS). Sometimes you need to do something urgently, but the SRK administrator is not there. Using the Commvault plugin for SQL Management Studio, the database administrator can perform basic data backup and restore operations.
QL Management Studio Version
Command
SQL 2008 R2
CvSQLAddInConfig.exe /i 10 /r
SQL 2012
CvSQLAddInConfig.exe /i 11 /r
SQL 2014
CvSQLAddInConfig.exe /i 12 /r
SQL 2016
CvSQLAddInConfig.exe /i 13 /r
SQL 2017
CvSQLAddInConfig.exe /i 14 /r
Versions of SQL servers that support the Commvault Plug-in and commands that activate the plugin. The plugin is only supported on 64-bit version of Windows OS.
1. Run the command that matches our version of SQL server:
2. Backup and restore options are now available in Management Studio. To do this, right-click on the desired database.
The administrator thus has the opportunity to directly interact with the backup copies of this database without the Commvault console and calls to the SRK administrator.
3. When you start any of the available functions of this menu, a window will appear asking for a login and password. To connect to CommServe, use SSO or any other account from the Security section in Commserve (Commcell login).
4. If the credentials were entered correctly and there are enough access rights, the database administrator can:
- start an extraordinary backup (Backup);
- restore the database from backup (Restore);
- view the history of completed jobs (View History) and progress on jobs in the process of execution (Job monitor).
This is how the history of completed backup jobs for the selected database looks like in Management Studio.
Menu for restoring the database. It doesn't even differ from the console menu.
That's all about these two SQL Agent features from Commvault. I will add that Commvault backup is more suitable for those who have dozens of servers in service, with several instances and databases, all this may be at different sites and requires setting up different schedules, depths, etc. If you have a couple of servers, then for the backup will suffice also regular means of MS SQL.
Source:
Source: habr.com