3

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.

Michael Hancock
  • 2,673
  • 1
  • 18
  • 37
  • Possible duplicate of [Backup a database on a HDD with a different sector size](https://stackoverflow.com/questions/22448818/backup-a-database-on-a-hdd-with-a-different-sector-size) – tukan Jul 18 '19 at 08:43
  • @tukan I did, and have explained why that question is not relevant. – Michael Hancock Jul 18 '19 at 08:47
  • 2
    Try putting the backup file on a network drive, and using that as the source of the restore. This may even work if the "network drive" is actually the local machine (e.g. `\\machinename\c$\...`). Disclaimer: not tested. The idea behind this is to force the engine to use buffered I/O by going through the network redirector, rather than the unbuffered I/O it uses otherwise (which requires sector sizes to be aligned). You would of course need to do the same if you want to restore the backup on the original machine. – Jeroen Mostert Jul 18 '19 at 09:59
  • The point is that if you scroll down you will find out answer to your Backup-SqlDatabase cmlet. – tukan Jul 18 '19 at 10:15

1 Answers1

3

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

Michael Hancock
  • 2,673
  • 1
  • 18
  • 37