Backup failed

Question

Backup failed

VegasL

Hall of Fame

Points: 3006
More actions
August 8, 2018 at 10:56 am

#368755

Hello,
I'm newbie to SQL. I am trying to backup a db on local pc, but am receiving this error:
Backup failed for Server 'DESKTOP-123ABCD\SQLEXPRESS01'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: Cannot use the backup file 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS01\MSSQL\Backup\myfilename.bak' because it was originally formatted with sector size 4096 and is now on a device with sector size 512. (Microsoft.SqlServer.Smo)
Background:
I imported db from SQL Server 12 into SQLExpress for 2014. Import was successful. I since added more tables to it and am now trying to back up db but get the above error.
I also pressed ADD
and tried creating a new db name, like below
but still got error.
How can I fix?
Thx

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

VegasL - Wednesday, August 8, 2018 10:56 AM
Hello,
I'm newbie to SQL. I am trying to backup a db on local pc, but am receiving this error:
Backup failed for Server 'DESKTOP-123ABCD\SQLEXPRESS01'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: Cannot use the backup file 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS01\MSSQL\Backup\myfilename.bak' because it was originally formatted with sector size 4096 and is now on a device with sector size 512. (Microsoft.SqlServer.Smo)
Background:
I imported db from SQL Server 12 into SQLExpress for 2014. Import was successful. I since added more tables to it and am now trying to back up db but get the above error.
I also pressed ADD
and tried creating a new db name, like below
but still got error.
How can I fix?
Thx

Did you delete the file that was showing in the first screenshot where it shows an existing backup file?
You had the right idea as you need to delete that existing one and create a new one. It sounds like maybe you left the original one in the list of files.
Delete anything that shows up for the files when you try to do the backup. And then just create a totally new one, not named like any of the others.

Sue

VegasL Hall of Fame Points: 3006 More actions · Answer 2

That worked perfectly. Thank you so much. I was afraid to erase original db, but thanks to you're confident answer I took leap of faith and back up worked. 🙂

Sue_H SSC Guru Points: 90860 More actions · Answer 3

VegasL - Wednesday, August 8, 2018 3:55 PM
That worked perfectly. Thank you so much. I was afraid to erase original db, but thanks to you're confident answer I took leap of faith and back up worked. 🙂

You are very welcome - thanks for posting back. And you still have your database 🙂

Sue

VegasL Hall of Fame Points: 3006 More actions · Answer 4

I looked back, the new back up in path \Microsoft SQL Server\MSSQL14.SQLEXPRESS01\MSSQL\Backup

is a type: file

where as the original back up was a file, type: BAK file

will this create a issue if ever need to restore db?

Also within path \Microsoft SQL Server\MSSQL14.SQLEXPRESS01\MSSQL\Backup in file explorer, i see both the original db file I deleted via SSMS as well as new one. I guess thats expected and non issue?

Sue_H SSC Guru Points: 90860 More actions · Answer 5

VegasL - Thursday, August 9, 2018 8:03 AM
I looked back, the new back up in path \Microsoft SQL Server\MSSQL14.SQLEXPRESS01\MSSQL\Backup
is a type: file
where as the original back up was a file, type: BAK file
will this create a issue if ever need to restore db?
Also within path \Microsoft SQL Server\MSSQL14.SQLEXPRESS01\MSSQL\Backup in file explorer, i see both the original db file I deleted via SSMS as well as new one. I guess thats expected and non issue?

Those file types are based on nothing other than what you used for the file extension on the backup. It's usually the standard practice to use bak for the file extension for SQL Server backups.
In that window where you you put the file for the backup, it's not going to delete anything from the file system that you delete from that window. It's just displaying the file and often shows the last backup file in case you want to append another backup to that same file (you can add another backup to the same file so it has more than one backup in that file). So yes...it's a non-issue.

Sue