Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: (Simple) Backup wont Restore

    Ive been periodically backing up my database, thinking I was smart. But I just tried to restore a backup and it failed with this error:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ADDITIONAL INFORMATION:

    Read on "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\MyDatabase .bak" failed: 38(Reached the end of the file.) (Microsoft SQL Server, Error: 3203)



    I do my backups by right clicking on the database in SQL Server Management Studio and selecting Tasks->Back up. I usually go with the default options, but Ive tried changing some obvious ones like compression and nothing fixed my problem. I restore with Tasks->Restore.

    I noticed that the backups I create are about 50 MB, but there are other backups of the same database in the backup directory that are about 350 MB, and those ones restore fine! So someone is doing backups differently and correctly. The problem is I feel like Ive tried all the options for Backup and they all result in 50 MB duds.

    Any ideas what I'm doing wrong? Im sure its something easy.

    Thanks!

  2. #2
    Join Date
    Feb 2010
    Posts
    19
    There are two clues here. One, a normal backup is 350MB, and yours is 50MB. Two, the error message says "reached the end of the file," which seems to say the system expected to get more data. Preliminary conclusion: the trouble is in the backup stage, not the restore. Maybe the backup task runs into something and stops unexpectedly, leaving the backup file incomplete.

    You could check the log for your backup task and see if there's something there. You could also run DBCC CHECKDB on your database to see if there's an issue with the data that's not causing other major errors. Another thing to check is why the other backups finish properly: what are they doing differently from yours?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A couple things to check. Run this:
    Code:
    restore headeronly from disk =  "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\MyDatabase .bak"
    on the 350 and the 50 MB backups. I suspect someone is stuffing 7 backups in the 350 MB file, and this will prove or disprove that theory.

    Are there any messages in the errorlog when you make your backups? There should be an informational message about the backup itself, but are there any indications of an error?

  4. #4
    Join Date
    Feb 2010
    Posts
    75
    The backups finish happily with out error. It goes 10% 20% ..etc. 100% Success! You have sucessefully backed up the database (or whatever it says). Theres no details button or anything to press. You click ok and the backup window disappears.

    MCrowley, that neat script seems to provide a little more information than pressing the 'Contents' button from the backup/restore screens. It turns out the good backups do NOT contain multiple copies of the database, its one big backup that takes 350 MB. I had that same suspicion when I noticed that my backups were almost exactly 1/7 the size of the good ones. Must be just a coincidence. Just for fun I tried appending 7 backups to the same file, and inspected it using your script. Sure enough it shows 7 items.

    Interstingly, when I run that script on my backups, it returns the result data, but also gives me an error in the Messages tab:

    Msg 3203, Level 16, State 2, Line 1
    Read on "C:\Backup2010-04-28" failed: 38(Reached the end of the file.)


    Thanks for the help but I am still very stumped!

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    OK. we have seen SSMS do strange things before, this may be one of them. Try running this:
    Code:
    backup database yourdbnamehere to disk = 'c:\testBkup.bak' with init, stats = 10
    Do you still come up with a 50 MB file?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You don't happen to have DoubleTake running on the server, do you? Also may want to funnel the backups to a folder that the virus scanner will not do an On Access Scan on:

    http://www.dbforums.com/microsoft-sq...tabase-vm.html

  7. #7
    Join Date
    Feb 2010
    Posts
    75
    Yessir, and still wont restore.

  8. #8
    Join Date
    Feb 2010
    Posts
    75
    I dont know what DoubleTake is, and I dont see it in the taskbar or installed programs, so I'm going to assume no.

    The AntiVirus thing is interesting but I dont think I want to mess with that right now. Besides if an antivirus changes your files, thats a serious bug in the antivirus is it not?

    Heres some more food for thought:

    Theres always a small chance someone went in and did a major 'cleanup' of the database recently, so I'm trying not to get too hung up on file sizes. On the other hand the "read past end of file" error seems to point back to file sizes.

    If I do a backup on the server and then do another backup, select the first backup, and hit 'contents' (basically the same as your restore headeronly script), then I DONT get an error. However on my local server, merely trying to view the contents of a backup results in the error.

    So I started thinking that maybe the process of transferring the backup from the server to my machine is corrupting the file somehow. So I did a test: backup on the server, copy to my machine, and copy back to the server. Now view contents on the server, and I get an error! So it seems like downloading the backup from remote desktop might be corrupting something. However, I am still able to copy the older backups from the server to local and they work fine. So Im not so sure about that theory anymore.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is there any possiblity that the source server is running either LiteSpeed Backup or SQL 2008 Enterprise (which are the same thing), and is using backup compression while the destination server is running without benefit of compression? Typically my backups run closer to 10% than 14%, but that is exactly the error message that is reported when somebody tries to restore a compressed backup onto a server without compression.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Feb 2010
    Posts
    75
    Compression is set to "use default", but I also have tried explicitly setting it to both yes and no. It was one of the first things I tried, after noticing the file size differences.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does the destination server have compression enabled? It would have to be SQL 2008 Enterprise in order to be able to turn compression on.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Feb 2010
    Posts
    75
    The destination doesnt have compression. I got a *different* error when trying to restore the compressed backup.

    But I dont think thats relevant. I have tried explicitly setting the backup to uncompressed, and Ive found out that the default is also uncompressed. Im pretty sure compression is not the issue.

    I am using 2008 by the way. Not sure about wether its Enterprise or not, will have to check next time Im at my work PC.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •