在具有不同扇区大小的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 Compatibility Level 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.备份数据库异常终止.

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的博客文章的评论部分中找到了解决此问题的提示:

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屋！