SQL Server Performance

Problem with RESTORE

Discussion in 'General DBA Questions' started by photura, Jun 16, 2006.

  1. photura New Member

    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!
  2. Luis Martin Moderator

    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.



  3. photura New Member

    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.




  4. Luis Martin Moderator

    If original mdf and log path are not the same in the new server.


    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.



  5. photura New Member

    No they are not:<br /><br />Production Data file:<br />E:program FilesMicrosoft SQL ServerMSSQLData...<br /><br />Production Log File:<br />D:...<br /><br />Stage Data AND Log File:<br />D:program 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">
  6. Luis Martin Moderator

    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.



  7. photura New Member

    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 'E:program FilesMicrosoft SQL ServerMSSQLdataTrack_DB_data.mdf' TO 'D:program 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.




  8. Luis Martin Moderator

    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.



  9. photura New Member

    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.




  10. Luis Martin Moderator

    "I'm pulling my hair out".
    So do I. Also I find the similar question in microsoft forum and no answer till now.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=321744&SiteID=1

    I have no idea, so wait for others members.


    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.



  11. Haywood New Member

    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.
  12. sylentwitness New Member

    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.
  13. sramesh New Member

    Hi
    Check this

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




    Thanks
    S. Ramesh
  14. photura New Member

    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 'D:program FilesMicrosoft SQL ServerMSSQLdataTrack_DB_data.mdf',
    MOVE 'Track_DB_Log' TO 'D:program FilesMicrosoft SQL ServerMSSQLdataTrack_DB_log.ldf',
    STATS = 10




    Thanks
    S. Ramesh
  15. satya Moderator

  16. photura New Member

    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.
  17. satya Moderator

    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.
  18. photura New Member

    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.
  19. satya Moderator

    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.
  20. photura New Member

    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.
  21. satya Moderator

    Not on the backup of database and only in case of Transaction log backup, as you cannot in SIMPLE.

    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.
  22. photura New Member

    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.
  23. satya Moderator

    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.
  24. photura New Member

    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.
  25. photura New Member

    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.
  26. Haywood New Member

    Show us what/how you do the backup, that will help probably.
  27. fireweb New Member

    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
  28. photura New Member

    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.

Share This Page