Today, I will talk about two features of Commvault for backing up MS SQL that are often overlooked: granular recovery and the Commvault plugin for SQL Management Studio. I won't cover the basic setup details. This post is more for those who already know how to install the agent, configure schedules, policies, etc. I discussed how Commvault works and its capabilities in this .
Granular Recovery
Option table level restore was recently added to the Subclient properties. It allows enabling table recovery from the database without restoring the entire database from backup. This is convenient when you know exactly where the error or data loss occurred. Meanwhile, the database itself is large, and restoring it fully would take a lot of time.
This option has limitations:
— Tables cannot be restored to the original database, only to another.
— All tables are restored to the dbo schema. A table cannot be restored to a user-defined schema.
— Only a local SQL Server account with system administrator rights is supported.
— The target server where the table is being restored must be running on Windows OS.
— The target server must have the Media Agent and Java Runtime Environment installed in addition to the SQL agent.
— The database must use the Full Recovery model.
— If the granular recovery option is enabled for the database, it will not be possible to run differential backup jobs.
The table-level-restore option is disabled.
The table-level-restore option is disabled.
In my experience, there was a case where a client had the following schedule set for the SQL server: one full backup weekly and 6 differential backups on weekdays. He enabled the table-level-restore function, and the differential backup jobs failed.
Let's see how the recovery process will look.
1. Start the recovery on the needed agent.
2. In the window that appears, go to the tab Advanced Options. We select SQL Granular Browse — View Content.
3. In the list that opens, select the database from which we will recover the table and click Restore Granular.
4. In the dialog box, configure the database mounting point from the backup files (something like Instant Recovery technology).
Specify:
- a name for the temporary database;
- how long to keep this recovery point in days;
- the server where we will mount the database. Only servers that meet all the necessary conditions mentioned above will be available in the list: with Windows OS, installed Media Agent and Java Runtime Environment, etc.
Click OK.
5. In the new window, click on List Recovery Points.
6. A list of mounted recovery points will open. If the database is large, you may need to wait. Then click Browse. A window will appear to view tables from the selected database.
While the list is being formed, users often close the Recovery Points dialog, and then cannot return there again. It's simple: right-click on the SQL server instance where the recovery point mounting 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 takes about ten minutes to form. Select the desired table and 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 database GPI TEST.
9. After the recovery is complete, the selected tables will appear in the GPI TEST database.
After restoring the tables to the temporary database, they can be transferred to the original database using Management Studio.
Commvault Plug-in for SQL Management Studio
Database administrators do not always have access to the backup system (BS). Sometimes urgent action is needed, and the backup system administrator is not available. With the Commvault plug-in for SQL Management Studio, the DB administrator can perform basic backup and data recovery tasks.
SQL 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 plug-in's functionality. The plug-in is supported only on the 64-bit version of Windows OS.
1. Execute the command that corresponds to our version of SQL server:
2. Now the options for backup and recovery are available in Management Studio. To do this, right-click on the desired database.
Thus, the administrator has the ability to interact directly with the backups of this database without using the Commvault console or contacting the SRK administrator.
3. When launching any of the available functions from this menu, a window will appear prompting for a username and password. SSO or any other account from the Security section in Commserve (Commcell login) is used to connect to CommServe.
4. If the credentials are entered correctly and permissions are sufficient, the database administrator can:
— initiate an on-demand backup (Backup);
— restore the database from a backup (Restore);
— view the history of completed jobs (View History) and monitor the progress of jobs in process (Job monitor).
This is how the history of completed backup jobs for the selected database appears in Management Studio.
The menu for restoring the database. It is not even different from the console menu.
That’s all about these two features of the SQL agent from Commvault. I would add that Commvault's backup solution is more suitable for those managing dozens of servers, with several instances and databases, possibly across different sites and requiring various scheduling, depth, etc. If you have just a couple of servers, the built-in MS SQL tools will suffice for backups.
Source:
Source: habr.com
