Restore-SqlDatabase using a backup with a different sector size

Question

I have a PowerShell script which copies backups from a production environment and restores them to a local sql server instance in order to perform some operations on the database then re-backs them up to a local drive.

Part of this command uses the Restore-SqlDatabase command.

This errors due to a sector size difference in my machine (4096) and the production environment (512).

Cannot use the backup file '.bak' because it was originally formatted with sector size 512 and is now on a device with sector size 4096

I want to automate this workflow so am looking for a way to run this script regardless of the sector size of the target machine or the production environment. Is there anyway within PowerShell to get around this issue?

Restore-SqlDatabase -ServerInstance $DatabaseServer -Database $DatabaseName -RelocateFile $relocate -BackupFile $BackupFilePath -RestoreAction Database

I am aware of the answers in this question, but specifically I want to do this in powershell (with the Restore-SqlDatabase command), not SQL.

Answer 1

Restore

It is possible to dictate the sector size during Restore-SqlDatabase with the BlockSize parameter. The block size which you pass in here needs to be the block size of your physical hard-drive.

Eg.

Restore-SqlDatabase -BlockSize 4096 -ServerInstance $DatabaseServer -Database $DatabaseName -RelocateFile $relocate -BackupFile $BackupFilePath -RestoreAction Database 

It is possible to determine the block size programmatically using the following command:

Get-CimInstance -ClassName Win32_Volume | Select-Object BlockSize

Backup

The Backup-SqlDatabase command also accepts a -BlockSize argument, however setting this to the original block size or my machine's block size did not work when overwriting the original backup file.

It was possible to completely omit the parameter and simply get backup to be saved elsewhere on disc. In this case SQL Server will select the block size of the hard drive (4096 in my case).