SQL Server 2005 to 2008 DB attach elp please!

Question

I have SQL Server 2005 Standard on my personal machine. I created a very big DB about 21 gb. I made a backup and transferred the .bak file via an ftp program to my dedicated server. I have SQL Server 2008 Enterprise Edition on my dedicated server. I tried restore the transferred .bak file but got an error. I posted the error on here and was told the database is corrupt. How? I don't know. The connection was not interrupted during the ftp transfer. The DB works on my own machine. So then I detached the db on my own machine and transferred the mdf and ldf file to my dedicated server through ftp again and again there were not interruptions. Now I try to attach the db and get this error: The header for file 'DB.mdf' is not a valid database file header. The FILE SIZE property is incorrect. (Microsoft SQL Server, Error: 5172)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1442&EvtSrc=MSSQLServer&EvtID=5172&LinkId=20476

I already wasted 21 gb transferring the .bak file. Now I used another 21 to transfer mdf and additional ldf file. Please tell me there's a solution. The db can detach and attach fine on my machine in sql server 2005 but not SQL server 2008 on my server.

Answer 1

I'm guessing your transfer is somehow getting corrupted, even though it appears to have successfully completed. It does happen.

My tip would be to Zip your BAK file before transferring. You should find the BAK file compresses quite well, down to 3 or 4GB from 21GB I'd expect. WinRAR is even better at compressing the data.

Restoring a 2005 BAK file in to a SQL 2008 setup is certainly possible, as is moving the raw MDF and LDF files over and attaching to the SQL 2008 server as documented here.

Answer 2

What FTP program are you using ? Some cheap ones can't handle files larger than 2/4 gigs without rolling over.

Also, grab a simple CRC program and run it against your local archive and the remote one. use CRC32 since it's much faster than even MD5 and you're not interested in it's cryptographic properties anyway.