0

Hello I am trying to create a new plan on SQL server to backup all my database.

My goal is to backup them to a network drive thus if I do have some trouble with my server, I will be able to restore databases to other server thanks to backup present in the network drive.

When my plan is executed, I do have some error so I try to execute manually the relative query.
After some investigation, it seems even net use command doesn't work (whereas it is working and I do it from cmd)

EXEC XP_CMDSHELL 'net use Z: \\ServerName\loggin/user:loggin password'

error is

System error 1450 has occurred. Insufficient system resources exist to complete the requested service.

Beside, I do have another server where it is working so I suppose some configuration missing but can't find them

DonKnacki
  • 427
  • 4
  • 11
  • You should execute this in a command window, under the SQL Server service account. And use `/persistent:yes`. Then you won't need `XP_CMDSHELL` which has security issues – Charlieface May 31 '21 at 10:43
  • ok but how can I use it in a backup plan ? – DonKnacki Oct 04 '21 at 08:56
  • If you have a scheduled backup plan that runs under SQL Server Agent then create a Powershell job step – Charlieface Oct 04 '21 at 09:00
  • So i try to follow instruction found in [microsoft web site](https://learn.microsoft.com/en-us/powershell/module/sqlserver/backup-sqldatabase?view=sqlserver-ps#example-7--backup-all-databases-in-a-server-instance-to-a-network-file-share) and do have an error 58. I also try to use net use and use z:\ instead of full network drive path and do have error 3 ... – DonKnacki Oct 04 '21 at 10:49
  • Did you use `/persistent:yes` and did you execute it under the SQL Server service account? – Charlieface Oct 04 '21 at 10:51
  • How can I execute a script under the SQ Service account? I try to run powershell from Management studio but without success: it ask me to install SqlServer module but I first need NuGet . Unfortunatly, it doesn't succed to install to install it and even manually, I am not able to install it – DonKnacki Oct 04 '21 at 13:56
  • What happens if you execute it as a SQL Server Agent Powershell job? I suppose as a one-off you could just use `xp_cmdshell`, but you should really keep it off most of the time. You don't need it all the time if you use `/persistent:yes`. – Charlieface Oct 04 '21 at 13:58
  • I do create a new SQL Server Agent powersheel job. I do use such script : `net use z: \\SERVER_NAME/user:USER_NAME PASSWORD /persistent:yes Backup-SqlDatabase -ServerInstance "SERVER_INSTANCE_NAME" -Database "test.bak" -BackupFile "z:\test\test.bak".` I do get an error. I test it to change only path destination with `C:\test\test.bak` and it does work – DonKnacki Oct 04 '21 at 14:37

1 Answers1

0

as my network drive is also accessible via FTP, I chose this way to make the job : create a batch file that run winscp and use this batch file in a SQL agent job . I need to add right to batch file to SQL Server agent account. I also need to define a credential and a proxy to be used in the job.

DonKnacki
  • 427
  • 4
  • 11