SQL 在具有不同扇区大小的 HDD 上备份数据库

Backup a database on a HDD with a different sector size

提问by codemonkeh

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.

在我们的开发环境中，我们长期以来一直通过各种 SQL Server 版本和不同的环境配置为我们的每个产品使用特定的备份和恢复脚本，没有任何问题。

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:

最近，我们升级到 SQL Server 2012 作为我们的标准开发服务器，具有 SQL 兼容级别 2005 (90)，以保持对旧系统的支持。现在我们发现在一台特定开发人员的机器上尝试备份数据库时出现以下错误：

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.

无法使用备份文件“D:\MyDB.bak”，因为它最初的格式化扇区大小为 512，现在位于扇区大小为 4096 的设备上。BACKUP DATABASE 异常终止。

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 BLOCKSIZEoption, but that then gives me the same error message.

从我的研究（即谷歌搜索）来看，除了使用该WITH BLOCKSIZE选项的建议外，关于这个问题的内容并不多，但这给了我同样的错误信息。

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?

谁能解释一下我如何将数据库备份和恢复到具有不同扇区大小的 HDD？

回答by Michael

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

你所要做的就是用一个不同的名字来支持它。

回答by Will L

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.

请注意，我已将 NOFORMAT 更改为 FORMAT 并删除了 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

在 MSDN 上以下博客文章的评论部分找到了解决此问题的提示： SQL Server–存储空间/VHDx 和 4K 扇区大小

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

以及有关 4k 扇区驱动器的更多信息：http: //blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

回答by Gayan Dasanayake

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

只需删除现有的 .bak 文件并重新运行。

回答by Van Vangor

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:

我遇到了与 OP 相同的问题。在开发机器上，我们有一个 PowerShell 脚本，用于从远程数据库服务器备份数据库并将备份文件存储在本地。该脚本一遍又一遍地覆盖相同的备份文件，并且该脚本已经运行了几年。然后我将旋转媒体驱动器克隆到开发机器中的 SSD。突然，我们遇到了与 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.

Backup-SqlDatabase : System.Data.SqlClient.SqlError: 无法使用备份文件“\DevMachine\Back-Up\Demo.bak”，因为它最初的格式化扇区大小为 4096，现在位于扇区大小为 512 的设备上。

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

当然，我可以删除所有现有.bak文件来解决问题。但如果它再次发生呢？我想要一个始终有效的命令行解决方案。

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-SqlDatabasecmdlet, -FormatMediais 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://docs.microsoft.com/en-us/powershell/module/sqlps/backup-sqldatabase

请注意，如果您阅读了Backup-SqlDatabasecmdlet的文档，-FormatMedia则被列为仅适用于磁带而不适用于磁盘备份。但是，它似乎可以在备份到磁盘时清除现有备份文件。
- https://docs.microsoft.com/en-us/powershell/module/sqlps/backup-sqldatabase

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

我发现如果我单独使用该-FormatMedia选项，它会产生以下错误：

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

Backup-SqlDatabase ：FormatMedia 和 SkipTapeHeader 属性设置冲突。

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

我通过添加一个附加选项固定在第二错误：-SkipTapeHeader。显然，这也适用于磁带备份，但它奏效了。

回答by Greg

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).

从 2005 年到 2008 年，我们遇到了同样的问题。问题是我们在 2008 年尝试使用与 2005 年使用的相同的备份文件（将备份一起附加到 1 个文件中）。

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

我们将脚本更改为备份到不同的文件，问题得到解决。我想移动/删除旧文件会产生同样的影响

回答by etveszprem

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."

我有同样的问题，但只是使用restore。我在 Management Studio 中收到此错误：“指定的演员表无效。（SqlManagerUI）”……查询中出现此错误：“SQL Server 无法处理此媒体系列。”

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.bakIt worked. I restored it from this place. :-S It looks like the SQL is sector-size sensitive.

然后我做了一件简单的事情：我将备份集处理到默认备份文件夹中。例如：C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Backup\bckup.bak它起作用了。我从这个地方恢复了它。:-S看起来 SQL 对扇区大小敏感。