HowTo-UpgradeNAVDatabase and SQL Time in Backup step

I am working with a relatively small database (<6GB) and I am encountering an SQL time out error when the script performs the backup of the database in the "Backup the database before upgrade" step "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.". When I run the backup from SQL Management Studio (SSMS) the backup takes just over one minute. I have tried setting the lock time-out in the Backup command (in the ..\Upgrade\Cmdlets\SQL\Backup-NAVSqlDatabase.ps1 module) to no effect :
Original
$query = " BACKUP DATABASE `"$DatabaseName`" TO DISK = '$DatabaseBackupFilePath'
Invoke-Sqlcmd $query -ServerInstance $SqlServerInstance
Updated
$query = " SET LOCK_TIMEOUT -1
  BACKUP DATABASE `"$DatabaseName`" TO DISK = '$DatabaseBackupFilePath'
  SET LOCK_TIMEOUT 30000"
Invoke-Sqlcmd $query -ServerInstance $SqlServerInstance
But I don't think this is a "lock" timeout issue.

This script works fine with a Demo Database as the source for the upgrade.

Thoughts/Suggestions?

Answers

  • taher.bhataher.bha Member Posts: 48
    Didn't try this but adding the querytimeout parameter may help
    Invoke-Sqlcmd $query -ServerInstance $SqlServerInstance -querytimeout 65535
    plz, let me know if this works
  • greatscott001greatscott001 Member Posts: 32
    I did try setting the default time but not on the command like that. Will advise. Sorry for the delay I have been out of the country.
Sign In or Register to comment.