Search code examples
sql-serverazurepowershellbackup

Powershell Backup-SqlDatabase backs up a snapshot instead of a full backup

The process: an Azure agent that runs on a Windows 10 32bit pro machine with SQL Server 2014 Express installed.

The pipeline is built and runs successfully with PowerShell scripts as follows:

  1. Create blank database
  2. Create tables needed
  3. C# application runs and populates the tables executed via a PowerShell script
  4. Cross reference tables to update data needed.
  5. Build a SSIS package

After result from SSIS package is success perform a backup

Command:

Backup-SqlDatabase -ServerInstance "$env.ComputerName" -Database "RealDB" 
                   -BackupAction Database -BackupFile $Path -Blocksize 4096

This all works with one exception the actual backup I get is missing the data from the SSIS package run. BUT if I log into the machine and restore the backup used from $Path it is missing the data.

When I query the database after this process the data is there in the database. There is only one database so its not backing up a different one.

I can run this command in powershell on the machine and my backup has the missing data that the powershell command from the agent does not.

Also interesting enough if I remove the -Blocksize 4096, it works as I expect and the backup has the data in it. I am considering abandoning the powershell due to this but thought I would ask to see if anyone experienced this or no.

Any help or thoughts are appreciated.

Thank you

Solution

  • Thank you @user19702 I was so consumed looking at the backup command with the added -BlockSize that I completely ignored the fact that my data has increased (thanks random process I never heard of before today) and even though the powershell is written to start the backup AFTER the SSIS package the process was not done. To find it I started task manager on the machine while the build was running and watched the process stay in memory for a few seconds when the backup started. I added a powershell command to make it wait a few seconds before processing the backup and its working.

    In case anyone is wondering this is the command

    $result = $package.Execute("false", $null)
Write-Host "Package ID Result: " $result
Start-Sleep -Seconds 10
Backup-SqlDatabase -ServerInstance "$env:ComputerName" -Database "RealDB" -BackupAction Database -BlockSize 4096 -BackupFile $Path

    Thank You!!