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

Improve this question
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, 2021 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, 2021 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, 2021 at 3:11

3 Answers 3

Reset to default
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

Improve this answer
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, 2021 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.

Improve this answer
0

I have just come across the same problem which only started happening after we upgraded from SQL2019 CU4 to CU14. The databases are TDE'd. We can take partial backups, and restore the read-write file groups but not the read only ones. It fails just after saying it has reached 100%. I removed TDE and it started working. I will try again with TDE after our next CU install and update if it starts working again. Steve.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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