SQL SERVER – Fix Error – Cannot use the backup file because it was originally formatted with sector size 4096 and is now on a device with sector size 512

Here is a recent email which I received from Madhu. He is a beginner in the SQL Server and when he tried to take a backup from SSMS (SQL Server Management Studio), he got error related to backup file. As soon as he sent me an email I knew what was the exact problem, he was facing and I was able to help him quickly. Let us first recreate the same error and later on see how to fix the same.

When he tried to take backup of his database, he received following error:

System.Data.SqlClient.SqlError: Cannot use the backup file because it was originally formatted with sector size 4096 and is now on a device with sector size 512. (Microsoft.SqlServer.Smo)

You can see in the following image the error window in SSMS.

Solarwinds

SQL SERVER - Fix Error - Cannot use the backup file because it was originally formatted with sector size 4096 and is now on a device with sector size 512 sectorerror1

Well, the solution for the same is very easy.

Solution / Workaround:

The reason for the error is that the backup file which was created was created with different sector size and it is not possible to use it now. In the SSMS we can see there is already one file in the Destination.

First remove the existing file in SSMS.

SQL SERVER - Fix Error - Cannot use the backup file because it was originally formatted with sector size 4096 and is now on a device with sector size 512 sectorerror2

Next, add a new file to the backup location.

SQL SERVER - Fix Error - Cannot use the backup file because it was originally formatted with sector size 4096 and is now on a device with sector size 512 sectorerror3

That’s it! We are done!

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Script level upgrade for database ‘master’ failed because upgrade step ‘upgrade_ucp_cmdw.sql’
Next Post
SQL SERVER – GetRegKeyAccessMask : Could Not Get Registry Access Mask For Registry Key – SQL Server Cluster

Related Posts

20 Comments. Leave new

  • Thank you. I was having a weekend migration freak out.

    Reply
  • Thanks! It works!

    Reply
  • Thank you for explain.i have a question ,when i try to restore DB from Avamar Backup system to different SQL server instance it failed because it was originally formatted with sector size 512 and is now on a device with sector size 65536.any idea please?

    Reply
  • Thank you very much. You are a great help.

    Reply
  • Thank You it was very helpful

    Reply
  • Hello i got the same error in my database backup but the difference is that the backup its set up automatically triggered by a Job that have a scheduled, in this case, where should I modify, the path its Ok in the script of my backup :

    DECLARE @DB_NAME VARCHAR(200)

    DECLARE DATABASES CURSOR FAST_FORWARD FOR
    SELECT name
    FROM sys.databases
    WHERE state_desc = ‘ONLINE’
    AND name NOT IN (‘tempdb’)
    ORDER by name

    OPEN DATABASES
    FETCH NEXT FROM DATABASES INTO @DB_NAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
    ——————————————————————————————————————————-
    print(”);
    print(‘—————————————————‘)
    print( CAST(GETDATE() AS VARCHAR(100))+’ BACKUP : ‘+@DB_NAME )

    EXEC ( ‘BACKUP DATABASE [‘+@DB_NAME+’]
    TO DISK = ”\\path\SQL_Backups\ACEINA_PROD\MyInstance\’ + @DB_NAME + ‘.Bak”
    WITH NOFORMAT, INIT, COMPRESSION,CHECKSUM, NOSKIP’ )
    ——————————————————————————————————————————-
    FETCH NEXT FROM DATABASES INTO @DB_NAME
    END
    CLOSE DATABASES
    DEALLOCATE DATABASES

    Reply
  • Thanks man!!! good stuff!

    Reply
  • Easy fix, thanks!

    Reply
  • thanks!

    Reply
  • very helpful, hank you

    Reply
  • sqlerror thanks
    February 16, 2019 2:03 am

    Thanks! System.Data.SqlClient.SqlError: Cannot use the backup file ‘backupfile.bak’ because it was originally formatted with sector size 512 and is now on a device with sector size 4096. (Microsoft.SqlServer.Smo)

    Reply
  • You area alegend, that’s working perfectly fine… You saved lots of my time…

    Reply
  • That’s working fine…. Helpful information for every one

    Reply
  • Türk Taraftarlar Birliği
    August 27, 2019 11:23 am

    Thanks:)

    Reply
  • Just had the exact problem and fixed it with your solution. Thanks a million

    Reply

Leave a Reply

Menu