在具有不同扇区大小的 HDD 上备份数据库 [英] Backup a database on a HDD with a different sector size

问题描述

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

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 2012 作为我们的标准开发服务器，具有 SQL 兼容级别 2005 (90)，以保持对旧系统的支持.现在我们发现在一台特定开发人员的机器上尝试备份数据库时出现以下错误:

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:

无法使用备份文件D:MyDB.bak"，因为它是最初格式化为扇区大小 512，现在在具有扇区大小 4096.BACKUP 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.

命令为:

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.

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

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.

我的查询是:

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

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

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

推荐答案

此问题是由不同驱动器使用的扇区大小不同引起的.

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

请注意，我已将 NOFORMAT 更改为 FORMAT 并删除了 NOSKIP.

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

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

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

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

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

这篇关于在具有不同扇区大小的 HDD 上备份数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！