Database Restore Failed

Question

Database Restore Failed

gshyam23 92006

Mr or Mrs. 500

Points: 550
More actions
August 10, 2012 at 1:41 pm

#279456

Hi,
Any Help is really appreciated
System.Data.SqlClient.SQLError: cannot Restore the file "_log" because it was originally written with sector size 512;
'path _.ldf' is now on a device with sector size 1024.
Took Backup from Server 1 and tried to Restore on Server 2 and it gave with above error.
Hardware Configurations:
On Server 1 drives C, M, F are on 512 sector size, where as on Server 2 C is 512, M and N drives on1024 size.
Here I can restore to C drive on Server 2.
M and N drive cannot be changed to 512 sector size(from Hardware Guy).
so I googled with the above error, it recommended to take backup with sector size to 1024.
After the backup then I tried restoring with Sector size 1024, same problem.
any suggestions??
Srcript i used.
BACKUP
DATABASE ImageStatsDB
TO
Disk = '\\Ipaddress $\BackUp\Image1.Bak' with BLOCKSIZE=1024
Restore.
RESTORE
DATABASE [Image] FROMDISK = N'N:\BackUp\Image1.Bak' WITHFILE = 1,
MOVE
N'ImageStatsDB' TO N'N:\MSSQL\Data\Image.mdf',MOVE N'ImageStatsDB_log' TO N'N:\MSSQL\Data\Image_1.ldf',NOUNLOAD,STATS = 10,
BLOCKSIZE
=1024
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Restore the DB and put the log onto a drive with 512 sector size (it's just temporary). Add a new log file on the drive that you want the log file on. Let the DB get used enough that all the active portion of the log is in the new log file. Drop the old log file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gshyam23 92006 Mr or Mrs. 500 Points: 550 More actions · Answer 2

Hi Gila,

Thanks for your responce, we have only one drive and that drive is on 1024 sector, the database size is 600 GB. Also this is not only with ldf file, it errors for mdf too.

-Shyam.

DPhillips-731960 Hall of Fame Points: 3904 More actions · Answer 3

Try first copying the backup files to restore to the drive you intend restore on, and then exec the restore.

Info source:

http://www.mombu.com/microsoft/sql-server-msde/t-cannot-use-backup-file-s-originally-formatted-sector-size-512-and-now-device-sector-size-2048-254989.html

---

Edit/Additional information:

Confirmed at (near the end):

http://technet.microsoft.com/en-us/library/aa275793(v=sql.80).aspx

And:

http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=SQL+Server&ProdVer=2000.80.760.0&EvtID=3269&EvtSrc=MSSQLServer&LCID=1033

Also, you may thank GilaMonster for putting the question to the Twitter-verse!