April 29, 2015 at 7:30 am
Hi All,
I have a weird one that I hope somebody can help me with. I'll lay the groundwork just in case any of it is relevant. I started with a production SQL 2000 database (yes, I know it's bad, we're trying to fix that). In order to test the upgrade to 2005 (yes, I know 2005 is almost EOL but there is a reason we're doing it this way), we need to scrub the database and restore it to a DEV environment. Our scrubbing routing only works with 2005 and above databases though, so what we did was stand up an empty Win2k8 R2 server with SQL 2005 on it in the production environment, restore a backup of the database to it, scrub it, and back up the scrubbed database. So far no issues.
Here's where it gets weird. After copying the scrubbed backup down to DEV, another 2008 R2 server with the same exact build of SQL 2005, the database won't restore. It gives me the message
The media family on device xxx is incorrectly formatted. SQL server cannot process this media family. RESTORE HEADERONLY is terminating abnormally".
My first thought is that the backup was corrupt, so I redid the backup and tried again. Same message. OK, so maybe the scrubbed database was corrupt. Nope, DBCC CHECKDB found no errors. Then I tried restoring correctly the backup on the server that originally backed it up and it worked fine. OK, that's odd. So I detached the freshly restored database and copied over the MDF and LDF files to the DEV server and tried to just attach the files. No joy. Now the error says
XXX is not a primary database file.
I'm stumped. Does anybody have any suggestions?
April 29, 2015 at 9:49 am
That is strange. I can't help but think that you have a version issue somewhere, or perhaps a weird network issue. You also say a 2008R2 server with 2005 installed. Is that right?
Can you do this?
- make new db on 2005 prod on an external HDD or USB (2MB)
- detach
- attach, make sure it's there
- detach.
- move drive to dev
- attach
Try the same thing with a restore.
April 29, 2015 at 10:01 am
Thanks for the suggestions. Version was one of the first things I checked. Both instances of SQL are running the exact same version (9 SP4, build #9.0.5069). I will try the external drive DB copy and post the update when it's done.
April 29, 2015 at 11:07 am
Odd thought.
When you run the backup of the scrubbed DB, are you backing up to a USB attached drive of some sort, or to a "local" drive?
I *think* I ran into a similar problem quite some time ago backing up DBs to USB drives and being unable to restore them, but if I backed up to a "local" drive then copied the bak to the USB drive, all was well.
April 29, 2015 at 11:09 am
Backups are being done to a local drive and are copied over the network to the other machine.
April 29, 2015 at 12:05 pm
is it a native backup or using a third party tool. what is the extension of the backup file.
can you do restore headeronly and filelistonly on the server where the backup was taken(not on the copied version).
April 29, 2015 at 2:10 pm
Steve Jones - SSC Editor (4/29/2015)
That is strange. I can't help but think that you have a version issue somewhere, or perhaps a weird network issue. You also say a 2008R2 server with 2005 installed. Is that right?
Yes
Can you do this?
1. make new db on 2005 prod on an external HDD or USB (2MB)
2. detach
3. attach, make sure it's there
4. detach.
5. move drive to dev
6. attach
7.Try the same thing with a restore.
1-5 work fine. 6 and 7 fail with the same error messages as before.
April 29, 2015 at 2:11 pm
mxy (4/29/2015)
is it a native backup or using a third party tool.
native
what is the extension of the backup file.
.bak
can you do restore headeronly and filelistonly on the server where the backup was taken(not on the copied version).
Yes, and I can also do a normal restore.
April 29, 2015 at 2:42 pm
I had the same issue where I was trying to restore from a Redgate backup so in the end I used the native backup and restore - it just took longer than I wanted.
April 29, 2015 at 2:48 pm
Can you provide build numbers of both servers.
Do you have any other server UAT or test where you can restore?
April 29, 2015 at 3:12 pm
That is really strange. Thanks for testing.
Along with the suggestion from mxy above, can you restore/attach on a third 2005Sp4 box?
Also, if you take a backup or create a small box on DEV, can you move it to prod (or a third box).
I suspect there's some issue with your dev server, but you need to verify ASAP that you can move a backup from prod elsewhere in case something really breaks.
April 30, 2015 at 8:23 am
So I tried both suggestions:
myx - the build number was already higher in the thread. It's SQL 9 SP4, build #9.0.5069.
April 30, 2015 at 8:33 am
It really sounds like the corruption happened during the scrubbing.
If I understand correctly:
You backed up/restored the database from SS2000 to SS2005.
That should have done an upgrade of the database.
Then you scrubbed the data.
Next you backed up the database and restored it to another SS2005 server where it is reported as corrupt.
Can you try a test?
Back up the database on the SS2000 server.
Restore it to the final destination SS2005 server without scrubbing. This would mean not providing access during the test.
If the database restores. That indicates the corruption is happening on the 'middle' server where the data scrubbing happens. Now do this test:
Backup the database on SS2000 server
Restore on the 'data scrubber server'
DO NOT scrub the data.
Backup the database and restore it on the final server.
If it is corrupt, the issue is with the 'data scrubber server'.
If it restores correctly, the issue is with scrubbing the data.
-SQLBill
April 30, 2015 at 8:48 am
Thank you all for your help and suggestions. I figured out what's going on. When I asked for IT to stand up a SQL 2005 server so I could do the scrubbing, they grabbed an old server that wasn't being used anymore. What they didn't tell me is that it used to be a production server and that it has a third party application installed that does data-at-rest encryption for SQL and all SQL files.
[Bangs head against wall]
[Repeatedly]
At least now I know and can fix it.
April 30, 2015 at 9:41 am
LOL, I'm sorry and I can certainly appreciate the frustration, Recurs1on. Glad you figured it out and that's a good one to keep in the back of my mind.
Can you disclose the encryption product?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply