I have a task of making periodical copy of the database, twice a day, and I should not break any backup chain niether accumulate these temporary backups. So I tried to take a
copy_only backup with
init in order to overwrite the single backup file.
My database in encrypted by using TDE.
backup compression at the server level, “backup compression default” is 1 in my sys.configurations. I don’t use explicit
with compression option in my command.
Here my full @@version:
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) – 15.0.4073.23 (X64)
Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0
(Build 14393: ) (Hypervisor)
My problem is this error below:
Msg 3241, Level 16, State 40, Line 8 The media family on device
‘W:SQL_backupcopy_onlybilling_prod2_test.bak’ is incorrectly
formed. SQL Server cannot process this media family. Msg 3013, Level
16, State 1, Line 8 RESTORE DATABASE is terminating abnormally.
Here is the full code I executed:
backup database billing_prod2 to disk = 'W:SQL_backupcopy_onlybilling_prod2_test.bak' with init, copy_only--, format; go restore database billing_1 from disk = 'W:SQL_backupcopy_onlybilling_prod2_test.bak' with move 'billing_prod2' to 'E:SQL_databilling_1.mdf', move 'billing_prod2_log' to 'E:SQL_databilling_1_log.ldf', replace
The first time it executes without errors, the size of full backup is about 35Mb and it restores perfectly.
The second time the backup executes without error but strangely has a size of 260Mb. This is the size I see on the disk and the same size is written in the output of
restore headeronly that still executes without errors. But when I do
restore database the error mentioned above appears.
I tried to execute the same code using another db and another disk to save the backup but the result is always the same: the first time it works, the second time the file is overwritten but has different size that is not just a double size, it’s almost 10 times of initial size.
restore headeronly never fails and always reports only one file within the backup file but the size is multipticated and
restore database fails.
Only when I added
format option the size of backup became normal and restore succeeded.
My question is: why should I use format with my backup in this case to be able to restore?
This is indeed a bug in SQL Server. We (@sepupic and I) had some discussions offline and managed to narrow down the circumstances: The error occurs when:
- You use TDE
- The backup is compressed
- You specify COPY_ONLY
- You specify INIT
- The backup file exists
--The database has TDE --Backupfile doesn't exist at this stage BACKUP DATABASE db1Encrypted TO DISK = 'R:db1Encrypted.bak' WITH INIT, COPY_ONLY, COMPRESSION, CHECKSUM --backup file 660 kb RESTORE VERIFYONLY FROM DISK = 'R:db1Encrypted.bak' WITH CHECKSUM --OK --Backupfile now exists, below causes unreadable backup BACKUP DATABASE db1Encrypted TO DISK = 'R:db1Encrypted.bak' WITH INIT, COPY_ONLY, COMPRESSION, CHECKSUM --backup file 3890 kb RESTORE VERIFYONLY FROM DISK = 'R:db1Encrypted.bak' WITH CHECKSUM --Fails "The media family on device 'R:db1Encrypted.bak' is incorrectly formed"