Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Backup a database on a HDD with a different sector size

Tags:

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?

like image 340
codemonkeh Avatar asked Mar 17 '14 07:03

codemonkeh


People also ask

What all types of DB backups are possible on secondary replicas?

Backup Types Supported on Secondary Replicas BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it's executed on secondary replicas. Copy-only backups don't impact the log chain or clear the differential bitmap. Differential backups aren't supported on secondary replicas.

How do I backup my entire database?

Right-click the database that you wish to backup, point to Tasks, and then select Back Up.... In the Back Up Database dialog box, the database that you selected appears in the drop-down list (which you can change to any other database on the server).

What is defined as partial backup?

Partial Backups A partial backup is any operating system backup short of a full backup, taken while the database is open or shut down. The following are all examples of partial database backups: a backup of all datafiles for an individual tablespace.

How do I restore a database from a higher version to lower?

Here are the basic steps we need to follow: Script the database schema and data from the higher version of SQL Server by using the Generate Scripts Wizard in SSMS. Connect to the lower version of SQL Server, and run the SQL scripts that were generated in the previous step, to create the database schema and data.


2 Answers

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

like image 50
Michael Avatar answered Oct 20 '22 04:10

Michael


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

like image 26
Will L Avatar answered Oct 20 '22 05:10

Will L