24

In our development environment we have long been using a particular backup and restore script for each of our products through various SQL Server versions and different environment configurations with no issues.

Recently we have upgraded to SQL Server 2012 as our standard development server with SQL Compatibility Level 2005 (90) to maintain support with legacy systems. Now we find that on one particular dev's machine we get the following error when attempting to backup the database:

Cannot use the backup file 'D:\MyDB.bak' because it was originally formatted with sector size 512 and is now on a device with sector size 4096. BACKUP DATABASE is terminating abnormally.

With the command being:

BACKUP DATABASE MyDB TO  DISK = N'D:\MyDB.bak' WITH  INIT , NOUNLOAD ,  NAME = N'MyDB backup',  NOSKIP ,  STATS = 10,  NOFORMAT

The curious thing is that neither the hardware nor partitions on that dev's machine have changed, even though their sector size is different this has not previously been an issue.

From my research (i.e. googling) there is not a lot on this issue apart from the advice to use the WITH BLOCKSIZE option, but that then gives me the same error message.

With my query being:

BACKUP DATABASE MyDB TO  DISK = N'D:\MyDB.bak' WITH  INIT , NOUNLOAD ,  NAME = N'MyDB backup',  NOSKIP ,  STATS = 10,  NOFORMAT, BLOCKSIZE = 4096

Can anyone shed some light on how I can backup and restore a database to HDDs with different sector sizes?

codemonkeh
  • 2,054
  • 1
  • 20
  • 36
  • The backup is attempting to overwrite the old backup file via `WITH INIT`. Have you tried simply backing up to a different file name? `BACKUP DATABASE MyDB TO DISK = N'D:\MyDB2.bak'...` – Dave Mason Mar 17 '14 at 13:31
  • @DMason same error i'm afraid. – codemonkeh Mar 17 '14 at 21:27
  • Hmmm. You mentioned the SQL 2012 upgrade, which doesn't seem like it should be an issue. What about the hardware? You mentioned the hardware and partitions haven't changed. So, it's still the same disk system, right? – Dave Mason Mar 18 '14 at 14:36
  • I found this article that mentions using trace flags to investigate: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d2d2e4f5-c1b8-463b-ba99-659d2157a572/sql-server-backup-using-blocksize-parameter?forum=sqldatabaseengine – Dave Mason Mar 18 '14 at 14:37
  • @DMason the hardware is literally the same, the only addition was a new version of SQL server. Curiously though i have noticed that this doesn't happen with another of our products on the same box, even though the database should be of a similar format. Thanks for the link I will give it a try. – codemonkeh Mar 18 '14 at 23:01

8 Answers8

42

All you have to do is back it up with a different name.

Michael
  • 421
  • 4
  • 2
25

This issue is caused by different sector sizes used by different drives.

You can fix this issue by changing your original backup command to:

BACKUP DATABASE MyDB TO  DISK = N'D:\MyDB.bak' WITH  INIT , NOUNLOAD ,  NAME = N'MyDB backup',  STATS = 10,  FORMAT

Note that I've changed NOFORMAT to FORMAT and removed NOSKIP.

Found a hint to resolving this issue in the comment section of the following blog post on MSDN: SQL Server–Storage Spaces/VHDx and 4K Sector Size

And more information regarding 4k sector drives: http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

Will L
  • 557
  • 1
  • 7
  • 17
  • I just backed up to a different file name in the same location. not sure why it was ever an issue. In some cases, this might be a bogus error? – user1585204 Jun 20 '17 at 18:19
7

Just remove the existing .bak file and re-run.

Gayan Dasanayake
  • 1,933
  • 2
  • 17
  • 22
  • 1
    This is because the NOFORMAT will force to preserve the existing media header which is different in this case. Just removing the file will make it write a new file with correct media header. – Gayan Dasanayake Sep 12 '17 at 08:04
5

I ran into the same issue as the OP. On a dev machine, we had a PowerShell script that backed up databases from remote database servers and stored the backup files locally. The script overwrote the same backup files, over and over, and the script had been working fine for a couple years. Then I cloned the spinning media drive to an SSD in the dev machine. Suddenly, we were getting the same error as the OP:

Backup-SqlDatabase : System.Data.SqlClient.SqlError: Cannot use the backup file '\DevMachine\Back-Up\Demo.bak' because it was originally formatted with sector size 4096 and is now on a device with sector size 512.

Sure, I could delete all of the existing .bak files to fix the problem. But what if it happens, again? I wanted a command line solution that consistently worked.

Here's our original code: 

Backup-SqlDatabase -ServerInstance "DBServer1" -Database "Demo" -BackupFile "\\DevMachine\Back-Up\Demo.bak" -BackupAction Database -CopyOnly -CompressionOption On -ConnectionTimeout 0 -Initialize -Checksum -ErrorAction Stop

After some fiddling around, I changed it to the following to fix the problem: 

Backup-SqlDatabase -ServerInstance "DBServer1" -Database "Demo" -BackupFile "\\DevMachine\Back-Up\Demo.bak" -BackupAction Database -CopyOnly -CompressionOption On -ConnectionTimeout 0 -Initialize -Checksum -FormatMedia -SkipTapeHeader -ErrorAction Stop

Basically, the following options were added to fix the issue:

-FormatMedia -SkipTapeHeader

Note that if you read the documentation for the Backup-SqlDatabase cmdlet, -FormatMedia is listed as only applying to tapes and not to disk backups. However, it appears to do the job of blowing away the existing backup file when backing up to disk.
- https://learn.microsoft.com/en-us/powershell/module/sqlps/backup-sqldatabase

I found that if I used the -FormatMedia option by itself, it generated the following error:

Backup-SqlDatabase : The FormatMedia and SkipTapeHeader properties have conflicting settings.

I fixed the second error by adding an additional option: -SkipTapeHeader. Clearly that's also intended for tape backups, but it worked.

Van Vangor
  • 398
  • 3
  • 9
4

We had the same problem going from 2005 to 2008. The problem was that we were trying to use the same backup file in 2008 that we used in 2005 (appending backups together into 1 file).

We changed the script to backed up to a different file and the problem was resolved. I would imagine that moving/deleting the old file would have the same affect

Greg
  • 3,442
  • 3
  • 29
  • 50
1

I had the same problem, but just with restore. I got this error in Management studio: "Specified cast is not valid. (SqlManagerUI)" ...and this error in query: "SQL Server cannot process this media family."

Then I done a simple thing: I coped backup set into the default backup folder. For example: C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Backup\bckup.bak It worked. I restored it from this place. :-S It looks like the SQL is sector-size sensitive.

etveszprem
  • 141
  • 1
  • 6
1

Most probably that Michael's answer is the solution that you all need. You just have another backup file with the same name and path.

https://stackoverflow.com/a/32662406/7841170

All you have to do is back it up with a different name.

AmitK
  • 91
  • 1
  • 6
0

In my case I was trying to overwrite the existing DB backup which had the same file name. I just deleted the existing file and saved the new backup file with same name.

Usman Farooq
  • 938
  • 8
  • 10