1

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 PARALLEL_BACKUP_QUEUE

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

I get

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?

3
  • @AaronBertrand of course, updated
    – SEarle1986
    Oct 26 '21 at 17:17
  • @AaronBertrand that's a good idea, I'll do that tomorrow and see what happens (I assume you mean the restored database) Unfortunately the server the backup originates from is not one I have access to, though i will be getting a new backup of it daily, so I will see what happens with tomorrow's backup too
    – SEarle1986
    Oct 26 '21 at 20:36
  • @AaronBertrand updated after some more testing....
    – SEarle1986
    Oct 27 '21 at 9:41

Your Answer

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

Browse other questions tagged or ask your own question.