0

Yes the databases (source and destination) are the same version. I have checked 3 times. Both are "Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64)"

The database itself restores ok without error. Only when I try the read-only file group restore does it give the error. I have redone the back up and attempted the restore again just in case I had something wrong with the backup.

I have done this numerous times with ss 2016 and even when upgrading from ss2016 to ss2019. But now I am testing the restores it just looks broken to me. Almost like this is a bug

3
  • 1
    Posting the restore commands (both functioning and non-functioning) would be a good start. Also, RESTORE HEADERONLY, FLINELISTONLY and VERIFYONLY might be revealing. – Tibor Karaszi Jan 19 at 7:47
  • restore database abcd from disk='B:\backup\local\abcd_20210120.bak' with replace,norecovery, move 'abcddata' to 'D:\abcd\abcd.mdf', move 'abcd_log' to 'D:\abcd\abcd_log.ldf', move 'DMS_REVISIONFILE1' to 'D:\abcd\DMS_REVISIONFILE1.mdf' restore database abcd Filegroup='DMS_REVISIONFILE1' from disk='B:\backup\local\readonlyabcdDMS_REVISION1.bak' with replace,norecovery, move 'DMS_REVISIONFILE1' to 'D:\abcd\DMS_REVISIONFILE1.mdf' It fails with the "media family is incorrect" on this last step – Thostenor Jan 20 at 3:08
  • Note I have reproduced this by restoring the same database back to the same server where the database was backed up. Only seems to be a problem with ss2019 CU8 – Thostenor Jan 20 at 3:11
0

I think you have encountered a problem with disk sector size, the old (very old) standard was 512 bytes per sector and the new one is 4kb. When you restore a database between these 2 standards the restore re-writes the data to 4kb sectors but with a read-only filegroup my hypothesis is that this would not be allowed. There is a basic discussion of the change here :-

https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-new-drives-use-4k-sector-size/ba-p/316277

1
  • I am using latest hardware rack-mounted Lenovos on Windows 2019 for both. This answer does not seem likely. Besides I have done exactly this on SS 2016 with the same hardware. Only difference being I am using TDE with ss 2019 so that is an extra factor – Thostenor Jan 20 at 1:17
0

I am confident this is a bug. I can recreate the issue on different servers at will backing up and restoring on the same server. It only occurs if TDE is turned on for the database. If TDE is turned off or the source database server is an earlier version of sqlserver this problem does not occur. It could also just be a bug with SS2019 CU8. I haven't got all month to test all variations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.