I have a database backup that has been taken on another server (That I don't have access to) I want to restore the
HEADERONLY and when I do so:
RESTORE HEADERONLY FROM DISK = N'D:\SqlServer\backups\MyDB.bak'
the operation takes around 12minutes to complete. When I look at
sp_whoisactive , I can see the wait is
I can see that the backup was taken on a SQL Server SQL Server 2016 SP2 CU13 and the server restoring is Microsoft SQL Server 2016 (SP2-CU17)
The file has a single backup inside and is on the D:\ drive of the server where I am running the
RESTORE WITH HEADERONLY command. The file is 16GB (the backup is compressed) The server is a test server and is effectively doing nothing so disk activity is low to nil.
This only happens for this backup file, others show the output within a second (files in the same location and of a larger file size)
I found that when I backup the database that was restored from
D:\SqlServer\backups\MyDB.bak to a new file
D:\SqlServer\backups\MyDBnew.bak I can run
RESTORE HEADERONLY FROM DISK = N'D:\SqlServer\backups\MyDBnew.bak'
and it runs instantly.
When I try to backup the database to the same file we used for the restore:
BACKUP DATABASE MyDatabase TO DISK = N'D:\SqlServer\backups\MyDB.bak' WITH STATS =1, COMPRESSION, INIT
Msg 3268, Level 16, State 1, Line 1 Cannot use the backup file 'D:\SqlServer\backups\BuildBackups\MyDB.bak' because it was originally formatted with sector size 512 and is now on a device with sector size 4096.
Is the mismatched sector size of the disks on the source and destination the reason for the slow reading of the header?