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.
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.
Next, add a new file to the backup location.
That’s it! We are done!
Reference: Pinal Dave (https://blog.sqlauthority.com)
22 Comments. Leave new
Thank you. I was having a weekend migration freak out.
I am glad after reading this comment.
Thanks! It works!
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?
Amavar backups are not SQL native backups. You might need to contact their support team.
Thank you very much. You are a great help.
You are welcome @Mohammed
Thank You it was very helpful
My pleasure @kociol
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
Thanks for sharing.
Thanks man!!! good stuff!
Easy fix, thanks!
thanks!
very helpful, hank you
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)
You area alegend, that’s working perfectly fine… You saved lots of my time…
That’s working fine…. Helpful information for every one
Thanks:)
Just had the exact problem and fixed it with your solution. Thanks a million
Thanks! Simple when you know how!
Helped me in 2022 :)