restoring a db to a drive w/t diff size sectors

[sdtechlog]

when I try to restore adb to a different server that has a RAID drive with different size sectors I get the following error:
Server: Msg 3269, Level 16, State 1, Line 1
Cannot restore the file 'Database_log' because it was originally written with sector size 512; 'e:\mssql\data\Database.ldf' is now on a device with sector size 4096.

Anyone know of any workarounds?

 

[mrdenny]

I would say that you are hosed.

This address the issue based on the backup file, not the log file, but it probably still applies.

A backup file could not be used because it was originally formatted with one sector size and is now on a device with a different sector size.
SQL Server uses nonbuffered I/O, which requires sectors to be aligned. You must restore the backup set from a disk with the same sector size, or over a network, which uses buffered I/O. Alternatively, you can specify a WITH BLOCKSIZE clause when you back up the database.

This is from

http://msdn.microsoft.com/library/?url=/library/en-us/trblsql/tr_servdatabse_04x3.asp

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sdtechlog]

Well, its getting wierder...
I checked the drive specs and both servers are at 512k.
I re-formated the drive on the target server and set the "allocation unit size" to 512k.

Still having the same error. What am I missing?

 

[sdtechlog]

Just for the record, when I run on the source server:
BACKUP DATABASE DatabaseName TO DatabaseName_backup
WITH BLOCKSIZE = 4096

I get:
Server: Msg 3268, Level 16, State 1, Line 1
Cannot use the backup file 'DatabaseName_backup' because it was originally formatted with sector size 512 and is now on a device with sector size 4096.

HELP!!!

 

[sdtechlog]

Ok, I was able to run the above command by first deleting the original .bak file. BUT, I continue to get the original error even if I use the file created with the above command (i.e.: WITH BLOCKSIZE = 4096).

Anyone out there? Mrdenny, how about a little more canadian humor?

 

[mrdenny]

What if you backup to a different file name without the blocksize command?

What if you backup over the network directly to the destintion servers hard drive?

What is the block size of the drive that the database and log sit on, on the source server?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sdtechlog]

Hey MrDenny,
thanks for the help, but nothing seems to work I tried both suggestions. While the backup file generates, the restore command gives me the same error regardless if I use the "with blocksize = 4096" or not. I'm kind of stumped. You think this could have someting to do with the underlying RAID structure of these drives?

 

[mrdenny]

It's entirely possible.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sdtechlog]

I set them both to raid 5, still failing. I'm going to try to break out a 300gb partition and see if that make a difference.

 

[sdtechlog]

Nope, tried breaking off a 300gb and then a 68gb partition, but I still get the same error when I try to run the restore command.

any comments/suggestions are welcome...

 

[sdtechlog]

I think I may be going down the wrong path. I checked (using the chkdsk command) the allocation unit size on the target server and it is 4096 for both the c: drive, to which I successfully restored, and the e: drive where its failing. The souce server has and allocation unit size of 4096 for both the c: and e: drives. I also tried (agin I think) running the backup command directly to the target disk using the BLOCKSIZE = 4096 parameter, but it still fails.

 

[mrdenny]

After you restore to C can you detach the database and move it to the E Drive?

It sounds like something funky is definetly going on with your E drive on the destination server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sdtechlog]

No change, same error message.

 

[sdtechlog]

Ok got it to work, kind off... First a little background hardware I'm using:
RAID Controller - ARC-1220
- Firmware - V1.36 2005-5-18
Drives - ST3500641AS (500GB Seagate Barracuda)

I found that I could get it to restore (with or without the BLOCKSIZE command) if I put the data on a RAID 0 (no raid) partition that consists of only one of the above mentioned drives.
As I really would like this to work on a RAID 5 setup, my next step is to check I have the latest Firmware and driver.

I'll let you know how it goes.

 

[sdtechlog]

I updated the Driver, firmware, and bios of the controller card. I was able to restore the db to a RAID 5 partition built with 3 drives to a size of 1TB. Subsequently I rebuilt the system with the full 8 drive RAID 6 (3TB) partition and I had the same error come up when I tried to restore. I'm now trying again with a 4 drive RAID 5 (1.5TB) partition, I'll update when I'm finished.

Anyone know if this could be caused by trying to use a +2TB partition in Windows Server 2003?

 

[mrdenny]

That is entirely possible. Partitions should be 2TB or smaller to work correctly and be fully addressable by a 32bit OS.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sdtechlog]

I think what I meant was SQL 2000 not win server 2k3, since the os does not appear to have any problems with it, I can format it and I can save data to it.