Skip to main content
  • EN
Solved

Veeam Remote SQL Database Restore as SQL Agent job

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

 

17 comments

JMeixner
Userlevel 7
Badge +17

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

Jochen (Joe) Meixner | Veeam Vanguard 2024 | Veeam Legend 2021 - 2023 | Veeam Certified Architect (VMCA) | Twitter: @JoMeix | BlueSky: @jmeixner.bsky.social
Chris.Childerhose
Userlevel 7
Badge +21

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.

Chris Childerhose - VMCA2024 | VMCE2023 | Veeam Vanguard 7* | Veeam Legend 4* | vExpert 5* | VCAP-DCV/VCP-DCV | Object First Ace | Cisco Champion | Twitter: @cchilderhose | Blog Site – https://just-virtualization.tech
G
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… :-(

JMeixner
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"

 

Jochen (Joe) Meixner | Veeam Vanguard 2024 | Veeam Legend 2021 - 2023 | Veeam Certified Architect (VMCA) | Twitter: @JoMeix | BlueSky: @jmeixner.bsky.social
Chris.Childerhose
Userlevel 7
Badge +21

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.

Chris Childerhose - VMCA2024 | VMCE2023 | Veeam Vanguard 7* | Veeam Legend 4* | vExpert 5* | VCAP-DCV/VCP-DCV | Object First Ace | Cisco Champion | Twitter: @cchilderhose | Blog Site – https://just-virtualization.tech
Chris.Childerhose
Userlevel 7
Badge +21

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.

Chris Childerhose - VMCA2024 | VMCE2023 | Veeam Vanguard 7* | Veeam Legend 4* | vExpert 5* | VCAP-DCV/VCP-DCV | Object First Ace | Cisco Champion | Twitter: @cchilderhose | Blog Site – https://just-virtualization.tech
G
Userlevel 3

Hey Chris,

 

Let me check that out...

G
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:

JMeixner
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"

Jochen (Joe) Meixner | Veeam Vanguard 2024 | Veeam Legend 2021 - 2023 | Veeam Certified Architect (VMCA) | Twitter: @JoMeix | BlueSky: @jmeixner.bsky.social
JMeixner
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  

Jochen (Joe) Meixner | Veeam Vanguard 2024 | Veeam Legend 2021 - 2023 | Veeam Certified Architect (VMCA) | Twitter: @JoMeix | BlueSky: @jmeixner.bsky.social
G
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?

JMeixner
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.

Jochen (Joe) Meixner | Veeam Vanguard 2024 | Veeam Legend 2021 - 2023 | Veeam Certified Architect (VMCA) | Twitter: @JoMeix | BlueSky: @jmeixner.bsky.social
G
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.

marcofabbri
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

Backups are like pizza, I love them. | Linkedin: @marco-fabbri-it
G
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!

Chris.Childerhose
Userlevel 7
Badge +21

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.

Chris Childerhose - VMCA2024 | VMCE2023 | Veeam Vanguard 7* | Veeam Legend 4* | vExpert 5* | VCAP-DCV/VCP-DCV | Object First Ace | Cisco Champion | Twitter: @cchilderhose | Blog Site – https://just-virtualization.tech
G
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


Terms & Conditions