ansible module/cvpysdk - SQL Server restore question

Last post 20 hours, 51 minutes ago by markweiji. 3 replies.
Sort Posts: Previous Next
  • ansible module/cvpysdk - SQL Server restore question
    Posted: 02-06-2020, 4:37 AM

    Hi All, 

    I am having some issues with restore()/restore_to_destination_server() using SQL Server agents.

    It looks like despite the fact that I am providing restore_path argument it seems to be ingored and restore job insists on restoring the SQL db in its original location which does not exist on destination instance.

    Ansible 2.9.3
    Python 3.6.9
    Commvault 11 SP14 (yes, yes, I know)

    I am passing the restore_path argument like this:

    restore_path: '{{restore_data_path}}'

    where
    restore_data_path: "E:\\Test_Dir_that_Exists\\Data\\"

    I can see the restore job started but it's unable to complete with following error:
    "Query Result: Directory lookup for the file "X:\Original_dir_from_source_instance\db_name.mdf" failed with the operating system error 3 (the system cannot find the path specified). File 'db_name' cannot be restored to "X:\Original_dir_from_source_instance\db_name.mdf". Use WITH MOVE to identify a valid location for the file.  Directory lookup for the file "X:\Original_dir_from_source_instance\db_name_log.ldf" failed with the operating system error 3 (the system cannot find the path specified). File 'db_name_log' cannot be restored to "X:\Original_dir_from_source_instance\db_name_log.ldf". Use WITH MOVE to identify a valid location for the file. Problems were identified while planning for the RESTORE staement. RESTORE DATABASE is terminating abnormally.

    Any advice or tips will be greatly appriciated.

    Regards,
    Tomasz

  • Re: ansible module/cvpysdk - SQL Server restore question
    Posted: 02-25-2020, 2:59 PM

    Hi Tomasz,

    Took a quick look at the source code of how CommVault implement SQL instance https://github.com/CommvaultEngg/cvpysdk/blob/master/cvpysdk/instances/sqlinstance.py there is a note hidden in the comment that saying the restore_path is not a string but a list:

    restore_path (list, optional): list of dicts for restore paths of database files

    Futher digging to the way Qscript initiate the SQL agent out of place restore, this appears to give a little bit direction:

    https://documentation.commvault.com/commvault/v11/article?p=18366.htm

    The xml template file to specify the renaming has a totally different syntax to follow and more importantly it is the name of the element is "device" which match the PyCVSDK source code pretty well.

    if restore_path is not None:
    restore_path_dict = {
    "device":
    restore_path
    }
    request_json['taskInfo']['subTasks'][0]['options']['restoreOptions'][
    'sqlServerRstOption'].update(restore_path_dict)

    XML SOAP request syntax as per template: https://documentation.commvault.com/commvault/v11/others/products/sql/command_line_xml/out_of_place_restore_template.xml

    <device>|DB1|#12!DB1_rename|#12!DB1|#12! E:\RestoreLocation\DB1.mdf|#12!C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB1.mdf </device>

    <device>|DB1|#12!DB1_rename|#12!DB1_log|#12! E:\RestoreLocation\DB1_log.ldf|#12!C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB1_log.ldf </device>

     

    In sum, instead of specify a simple string as ansible parameter I would suggest use list as parameter to give a try: 

    restore_path: '{{restore_data_path}}'

    Try 

    restore_path:

      - "|DB1|#12!DB1_rename|#12!DB1|#12! E:\RestoreLocation\DB1.mdf|#12!C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB1.mdf"

      -"|DB1|#12!DB1_rename|#12!DB1_log|#12! E:\RestoreLocation\DB1_log.ldf|#12!C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB1_log.ldf"

    If this did not work out, maybe we just need add a bit more debugging code to sqlinstance.py to discover the Python dictionary structure before sending to figure that out.

    Hope this helps.

    Mark

     

  • Re: ansible module/cvpysdk - SQL Server restore question
    Posted: 02-26-2020, 4:29 AM

    Hi Mark, 

    thanks for getting back to me. 

    Yes, we have managed to figure this out but the problem is now how to get the original location of the files database files - this seems to be a mandatory requirement to kick off the restore (+plus the logical name of the files)..

    Browse method (for SQL Instance) does not provide those details :/ and I could not find anything else that can be used instead.. 

    Any ideas on this? 

    Regards,

    Tomasz

  • Re: ansible module/cvpysdk - SQL Server restore question
    Posted: 20 hours, 51 minutes ago

    Unfortunately after a lot of try and errors I just realised that the browse of the individual files may be blocked for some reason for the cvpysdk, there are 2 official REST APIs listed in the online documentation for browse in the Restore Operations category

    GET Subclient Browse

    http://documentation.commvault.com/commvault/v11/article?p=48638.htm

    POST Browse

    http://documentation.commvault.com/commvault/v11/article?p=48660.htm

    POST Browse is the extesive method with variation of parameters which is used in the Commcell class in the SDK as part of the servcie mapping dictionary in self._services:

    "BROWSE": "http://cvcs1-bu-nvan.datacenter.globalrelay.net/webconsole/api/DoBrowse"

     

    Check services.py for detailed definitions https://github.com/CommvaultEngg/cvpysdk/blob/master/cvpysdk/services.py

    GET Subclient Browse on the other hand is not even part of the Python API with simpler interface allowing the URL encoded parameters. Both of them I attempted to use to retrieve the detailed file list but with the same error of "No Backups found for given time range".

    The closest match that works and may allow you to browe the SQL file on a live system is the Client Browse API

    /Client/{clientId}/browseFS

    However this will not properly work in your case as the source system may change overtime, there might be some other hidden parameters/APIs that allows go to granular level of SQL instance in the backupset as in the Java Console we have that info populated. Another way to think is to intercept the communications and decode the API somehow through packet trace.

    Hope this can shed some light at least.

    Mark

The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2020 Commvault | All Rights Reserved. | Legal | Privacy Policy