I am trying to set up a new server that is using iscsi for the storage of the SQL Server data. When I try to restore to the new setup, it tells me it can due to the block size being different with the old being 512 and the new at 64k or 8192 (this depends on how the iSCSI is created and 8192 is the smallest I can get it).
Basically, it is the same question as this but the suggestion didn't work.
Restore-SqlDatabase using a backup with a different sector size
Any ideas on how I can get this working?
Steps in the linked article above and lots of google searching
The error I am getting either by restoring with the UI or the PowerShell command is as follows: Microsoft.Data.SqlClient.SqlError: Cannot restore the file '' because it was originally written with sector size 512. 'E:\data\myTestDB.mdf' is not on a device with sector size 8192. (Microsoft.SqlServer.SmoExtended)
I have tried this in the SQL Query per that article and some others I have found:
$DatabaseServer = "localhost"
$DatabaseName = "myDB"
$BackupFilePath = "\\10.1.11.77\DatabaseStaging\myDB.bak"
$RelocateData = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $assemblySqlServerSmoExtendedFullName"('myTestDB', 'E:\data\myTestDB.mdf')
$RelocateLog = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile, $assemblySqlServerSmoExtendedFullName"('myTestDB_Log', 'E:\data\myTestDB.ldf')
$relocate=@($RelocateData,$RelocateLog)
Restore-SqlDatabase -BlockSize 8192 -ServerInstance $DatabaseServer -Database $DatabaseName -RelocateFile $relocate -BackupFile $BackupFilePath -RestoreAction Database
or
Restore-SqlDatabase -BlockSize 512 -ServerInstance $DatabaseServer -Database $DatabaseName -RelocateFile $relocate -BackupFile $BackupFilePath -RestoreAction Database
