Problem with RESTORE

I have a copy of the same database on two different servers (stage and production). I backup production to a share on stage. When I try to restore the backup from production to stage I get the following error:

Error 3242: The File on device 'D:ackupackup_db.BAK' is not a valid Microsoft Tape Format backup set.
RESTORE FILELIST is terminating abnormally

Does anyone know what the problem is? I've searched the Internet all it says is the backup file is corrupt. So, I've backed up production directly to a drive on stage and still get the error when I try a restore.

Thanks in advance!

Hi Luis: What do you mean by path destination?

quote:Originally posted by LuisMartin

Just in case, did you check path destination in process backup?.


Luis Martin
Moderator
SQL-Server-Performance.com

All in Love is Fair
Stevie Wonder


All postings are provided “AS IS” with no warranties for accuracy.

No they are not:<br /><br />Production Data file:<br />Erogram FilesMicrosoft SQL ServerMSSQLData...<br /><br />Production Log File:<br />D:...<br /><br />Stage Data AND Log File:<br />Drogram FilesMicrosoft SQL ServerMSSQLdata<br />If this is the problem, how would I correct the issue so that the backup from Production will restore on Stage?<br /><br />Thanks Luis! You're brilliant! <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by LuisMartin</i><br /><br />If original mdf and log path are not the same in the new server.<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All in Love is Fair <br />Stevie Wonder<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

Ugh! Still doesn't work. Any ideas??

Here's the statement I'm running in Query Analyzer:

RESTORE DATABASE Track_DB
FROM DISK = 'D:BACKUPTrack_db_200606160200.BAK'
WITH RECOVERY,
MOVE 'Erogram FilesMicrosoft SQL ServerMSSQLdataTrack_DB_data.mdf' TO 'Drogram FilesMicrosoft SQL ServerMSSQLdataTrack_DB_data.mdf',
STATS = 10

Here's the exact erorr I still get:

Server: Msg 3242, Level 16, State 1, Line 1
The file on device 'D:BACKUPTrack_db_200606160200.BAK' is not a valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

quote:Originally posted by LuisMartin

Check Move option if you are using Query Analyzer or options tab if you are using EM.


Luis Martin
Moderator
SQL-Server-Performance.com

All in Love is Fair
Stevie Wonder


All postings are provided “AS IS” with no warranties for accuracy.

No cluster. They are standalone servers and the D drive that I backed up production to is the local D drive on the stage server.

Any ideas? I'm pulling my hair out. Thanks.

quote:Originally posted by LuisMartin

Any of the server is cluster and other no?


Luis Martin
Moderator
SQL-Server-Performance.com

All in Love is Fair
Stevie Wonder


All postings are provided “AS IS” with no warranties for accuracy.

Run the below command in QA:

RESTORE VERIFYONLY FROM DISK = 'D:BACKUPTrack_db_200606160200.BAK'

Check to see if your backup is corrupt or not. This isn't 100% foolproof but it will give you some possible answers.

Based on the error you are getting it seems like you are trying to restore from tape and not device. In EM expand out to the database you are restoring to. Right click, all tasks, restore database. In the new window be sure to select "from device". Once that is selected click on "select devices" then click the add button. Browse to your .bak file that you have saved in that shared directory. Press ok, ok, and ok one more time. The restore process should begin.

I hope it is something that easy for you.

Peace.

Hi
Check this

RESTORE DATABASE Track_DB
FROM DISK = 'D:BACKUPTrack_db_200606160200.BAK'
WITH RECOVERY,
MOVE 'Track_DB_data' TO 'Drogram FilesMicrosoft SQL ServerMSSQLdataTrack_DB_data.mdf',
MOVE 'Track_DB_Log' TO 'Drogram FilesMicrosoft SQL ServerMSSQLdataTrack_DB_log.ldf',
STATS = 10




Thanks
S. Ramesh

Nothing is working. I even tried with a fresh backup this morning and it still gives the error.

I try to restore verifyonly and it gives me the error saying not a valid tape format.

Any other ideas? I'm at a loss and need to get this solved.

Thanks!!!

quote:Originally posted by sramesh

Hi
Check this

RESTORE DATABASE Track_DB
FROM DISK = 'D:BACKUPTrack_db_200606160200.BAK'
WITH RECOVERY,
MOVE 'Track_DB_data' TO 'Drogram FilesMicrosoft SQL ServerMSSQLdataTrack_DB_data.mdf',
MOVE 'Track_DB_Log' TO 'Drogram FilesMicrosoft SQL ServerMSSQLdataTrack_DB_log.ldf',
STATS = 10




Thanks
S. Ramesh

Unfortunately, I'm only trying to backup and restore to/from a disk and still get the error. I even tried a fresh back up thinking that the backup file is damaged and still the same problem.

Could there be a problem with the backup process on the production server? I think I will try to manually backup the production database with a verify backup directly to the stage server and then do the restore to the stage database from that again and see what I get.

I'm out of answers and I've scoured the Internet for answers so if any has any thoughts, any at all, I'd appreciate reading them.

Thanks.

quote:Originally posted by satya

See this KBAhttp://support.microsoft.com/?kbid=905616 is any help if you are on Win 2k3.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

No, I don't believe the drive is compressed. How would I verify that?

I also don't believe there are any SQL service account priviledges issues but I'm opening to exploring anything at this point. BTW, it's being restored using SA account

Thanks again.

quote:Originally posted by satya

BTW the drive used here is compressed?
And I hope no privileges issue here for the SQL service account.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

By the way, one difference I see is that the production database has a 'full' recovery model and the stage database has a 'simple' recovery model. Would this make a difference?

quote:Originally posted by satya

Check the drive properties from Windows explorer and in general if drive is compressed then it will be shown in blue colour. BTW have you verified the restore file too as suggested above.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Yes, I tried a RESTORE VERIFYONLY and it won't let me. It returns the same error saying it's not a valid Microsoft Tape Format....

quote:Originally posted by satya

Check the drive properties from Windows explorer and in general if drive is compressed then it will be shown in blue colour. BTW have you verified the restore file too as suggested above.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

I verified that the disk is not compressed.

quote:Originally posted by photura

Yes, I tried a RESTORE VERIFYONLY and it won't let me. It returns the same error saying it's not a valid Microsoft Tape Format....

quote:Originally posted by satya

Check the drive properties from Windows explorer and in general if drive is compressed then it will be shown in blue colour. BTW have you verified the restore file too as suggested above.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

I'm going to try backing up production manually with verify backup turned on to a another server on the network and copy the backup to the stage server and try the restore again.

quote:Originally posted by satya

Can you try backup to a different drive or perform LOG backup to a network location (change recovery model).
I guess could be the hardware problem too, get the vendor to certify the hardware.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Show us what/how you do the backup, that will help probably.

FYI,
I backed up a sql2K database on a production server to hard disk, then copied the .mdf & .ldf onto CD.
Restore went OK for Win2003 server with SQL2005.
Restore to my laptop with XP & SQL2K failed with 'invalid tape format' error, even though a tape device was never specified either in backup or restore commands.

Tried all suggestions posted here like Photura, but no luck either.
HTH,
fireweb

In order to give back to my "DBA Community" I want to make sure I post the solution to any issue I figure out offline. It seems I've missed a few, so...

What happened it this case was: The backups on the production server were getting backed up with Litespeed and getting compressed. I had no idea that this company even owned this software and it was only on this specific server. When looking at the backup file I could not tell it was compressed. It had a normal .BAK extension and all looked fine.

When it was transferred to the stage server and I tried to restore it using SQL Server native restore then this error popped up because it was compressed.

All worked fine after that.

So, if you ever get this error in your own environment and there are many things that are in place priority to your arrive, check file compression.

quote:Originally posted by photura

Unfortunately, I'm only trying to backup and restore to/from a disk and still get the error. I even tried a fresh back up thinking that the backup file is damaged and still the same problem.

Could there be a problem with the backup process on the production server? I think I will try to manually backup the production database with a verify backup directly to the stage server and then do the restore to the stage database from that again and see what I get.

I'm out of answers and I've scoured the Internet for answers so if any has any thoughts, any at all, I'd appreciate reading them.

Thanks.

quote:Originally posted by satya

See this KBAhttp://support.microsoft.com/?kbid=905616 is any help if you are on Win 2k3.

Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.