Solved

Veeam Remote SQL Database Restore as SQL Agent job


Userlevel 3

I have several SQL jobs that currently perform nightly (or weekly) restores of a production database on other servers.  Since we have now migrated to the Veeam Backup and Replication solution, I am trying to accomplish these same tasks within the Veeam environment.  (BTW, we are on Veeam version 11.)  I set up the Veeam PowerShell environment as described here https://helpcenter.veeam.com/docs/backup/powershell/getting_started.html?ver=110#remote and wrote a script to perform a database restore (see attached).

I ran the script interactively within a PowerShell session and it completed successfully.  I ran it again as a Windows Scheduled Task and it worked.  However, I can’t get it to run as a SQL Server Agent job. Here’s the error message that I’m receiving:

The term 'Start-VESQLRestoreSession' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Since the original SQL job contains steps subsequent to the restore (granting user permissions, etc.), I can’t use the option to run is as a Scheduled Task.  The requirements are that it runs as a SQL job.  I’m sure that the solution is very simple.  I just can’t seem to see it.  Can anyone help me you here?

 

Thanks!

 

Paul

 

icon

Best answer by GLFHCDBA 21 January 2022, 17:06

View original

17 comments

Userlevel 7
Badge +17

Hello @GLFHCDBA ,
does the job run under the same user context both times?

Userlevel 7
Badge +20

I am thinking that you cannot run it as a SQL Agent job as SQL does not understand the Veeam PS commands.

Try adding this to the PS script before the connection to VBR line to see if it helps in the SQL Agent context -

asnp VeeamPSSnapin

This loads the Veeam PS modules.  The other option is you import the modules in the PS script also just need to find those commands.

Userlevel 3

Thanks Chris.  I thought that same thing - that SQL doesn’t understand the Veeam PS commands.  Wouldn’t modifying the global (PowerShell) profile fix that?  Then, regardless of what user is logged in, the Veeam modules would be loaded.  Why does it work as a Windows Scheduled Task and not a SQL job.  I’d like to fix this problem so that it can be demonstrated that Veeam can interact with SQL.  Otherwise, other DBAs, like myself, are going to have issue using this product… :-(

Userlevel 7
Badge +17

You said that you are using VBR 11, correct? Then there is no Powershell SnapIn anymore.

You have to import the Veeam v11 Powershell module and in some cases you have to give the exact path.

Something like this - change the path to the correct path on your system:

Import-module -name "C:\Program Files\Veeam\Backup and Replication\Console\Veeam.Backup.PowerShell\Veeam.Backup.PowerShell.psd1"

 

Userlevel 7
Badge +20

Thanks Chris.  I thought that same thing - that SQL doesn’t understand the Veeam PS commands.  Wouldn’t modifying the global (PowerShell) profile fix that?  Then, regardless of what user is logged in, the Veeam modules would be loaded.  Why does it work as a Windows Scheduled Task and not a SQL job.  I’d like to fix this problem so that it can be demonstrated that Veeam can interact with SQL.  Otherwise, other DBAs, like myself, are going to have issue using this product… :-(

I would think modifying the PS profile may fix that so that the Veeam modules are loaded always when PS runs.  Never really tried it with SQL so cannot confirm for sure.  It works as a Windows task I assume because the OS is aware of the modules but SQL is not (my assumption).  I would try the profile to see if that works otherwise just add the line to the script and test SQL Agent.

Userlevel 7
Badge +20

You said that you are using VBR 11, correct? Then there is no Powershell SnapIn anymore.

You have to import the Veeam v11 Powershell module and in some cases you have to give the exact path.

Something like this - change the path to the correct path on your system:

Import-module -name "C:\Program Files\Veeam\Backup and Replication\Console\Veeam.Backup.PowerShell\Veeam.Backup.PowerShell.psd1"

 

Ah yes that is right as per @JMeixner - the snapin was old school now it is import.  Thanks for the correction Joe.

Userlevel 3

Hey Chris,

 

Let me check that out...

Userlevel 3

Bummer…  I’m still getting this error message when I run the script in a SQL job:

The term 'Start-VESQLRestoreSession' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
 

:slight_frown:

Userlevel 7
Badge +17

Mhh, there is sql powershell module.

Perhaps this will do the trick….

import-module -name "C:\Program Files\Veeam\Backup and Replication\Explorers\SQL\Veeam.SQL.PowerShell\Veeam.SQL.PowerShell.psd1"

Userlevel 7
Badge +17

Yes, in this module is the cmdlet Start-VESQLRestoreSession included

PS C:\Program Files\Veeam\Backup and Replication\Explorers\SQL\Veeam.SQL.PowerShell> $test = Get-Module Veeam.SQL.PowerShell

PS C:\Program Files\Veeam\Backup and Replication\Explorers\SQL\Veeam.SQL.PowerShell> $test.ExportedCmdlets.Values | sort

CommandType Name                      Version Source  

Cmdlet      Start-VESQLRestoreSession 1.0     Veeam.SQL.PowerShell  

Userlevel 3

OK….  When I run that command interactively now, using the import-module command, I get this message:

 

WARNING: The names of some imported commands from the module
'Veeam.Backup.PowerShell' include unapproved verbs that might make
them less discoverable. To find the commands with unapproved verbs,
run the Import-Module command again with the Verbose parameter. For a
 list of approved verbs, type Get-Verb.

 

Any thoughts?

Userlevel 7
Badge +17

Honestly… I have not a clue what’s wrong at the moment…  :frowning2:

The module loads fine on my server…

 

Seems to be a bigger problem with your environment.

Userlevel 3

Thanks Joe.  I appreciate everyone’s comments here.  I’m sure that it’s something simple that I’m overlooking.  I guess it’s time to roll up the sleeves and debug this code…

If, and when, I discover the solution, I’ll share it with the group.

Userlevel 7
Badge +13

Check this if it’s the solution for your issue:

https://stackoverflow.com/questions/24156451/powershell-the-term-is-not-recognized-as-cmdlet-function-script-file-or-operable

 

PS.

Is this the same case? Or a similar?

https://forums.veeam.com/powershell-f26/case-04983558-start-vesqlrestoresession-not-working-t76262.html

Userlevel 3

Update:

 

Since I originally posted this question, I have been successfully restoring the SQL database on a daily basis using a (Windows) Scheduled Task.  However, until today, the SQL job created to restore the database has been unsuccessful.  For mysterious reasons that I can’t explain, it’s now working correctly as a SQL job.  I’m going to test it some more but it’s working!

Userlevel 7
Badge +20

Update:

 

Since I originally posted this question, I have been successfully restoring the SQL database on a daily basis using a (Windows) Scheduled Task.  However, until today, the SQL job created to restore the database has been unsuccessful.  For mysterious reasons that I can’t explain, it’s now working correctly as a SQL job.  I’m going to test it some more but it’s working!

Glad to hear that.  Keep us updated.

Userlevel 3

My next step is to replicate this environment on the backup (DR) server and get it working there.  Once that’s successful, I will move the final product to production...

Comment