Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2015
    Posts
    2

    Question Unanswered: Backing up / restoring a 20GB database with images stored as binary code

    Please excuse me as I am a beginner.

    I am trying to backup and restore a 20GB SQL database from a SQL Server 2012 to another SQL Server 2014, but I have come across the following issues:

    1) The developers [against best practice] have stored multiple images in fields within the database as binary code.

    This therefore exceeds the 65532 character limit in some fields, so even though the images do show [based upon the data saved within this field], I cannot find the data in the field beyond this 65532 limit, within SQL Server.

    How can I export / locate this data after the 65532 character limit?

    2) When I have attempted to restore the database I am getting this error message:

    Restore of database 'zapkam' failed. (Microsoft.SqlServer.Management.RelationalEngineTa sks)

    Additional information:

    System.Data.SqlClient.SqlError: RESTORE detected an error on page (1:1592996) in database "zapkam" as read from the backup set. (Microsoft.SqlServer.SmoExtended)

    I have managed to restore two other smaller databases using the same technique, but am wondering if it's an issue with the database itself.

    3) I have uploaded this database to the new server using FileZilla FTP Client, but it has cut out, painfully at 80% + 90% on a couple of occasions.

    Is there a better solution for uploading these big files that I could possibly use please? For example, uploading table by table or similar...

    Would be very grateful if anyone could shed light on any of these issues?

    Thank you!

    William

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It sounds like you may have a corrupt backup file. Run DBCC CHECKDB on your database to make sure the corruption is not in your current database as well. If the database is clean, get a new backup, and try to restore that one.

    What tool are you using to look at the image data in SQL Server? I would wager that SQL Server Management Studio will not show more than that, but that would be a limitation of the tool, rather than the engine.

    Also, I have not found any best practice regarding where to save image data. As far as I know, it is a rather hotly contested debate even today. It basically comes down to your needs, as almost everything is a tradeoff.

  3. #3
    Join Date
    Oct 2015
    Posts
    2
    Thank you very much for your response!

    I have run the DBCC CHECKDB and there is no corruption:

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'x'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    I have been using Microsoft SQL Server Management Studio 2012.

    As the database is 20GB, is there anything that I should look out for when I am backing it up and restoring it?

    I did read somewhere that corruption can sometimes be caused by loss of connection whilst uploading through FTP. Is there any way of protecting the DB from this during FTP upload? Or is there a better solution you can recommend?

    Really appreciate your help.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Damage to the file after it leaves SQL Server is a little hard to protect against. You could try to back up the database with compression. That may at least give you faster upload, but if the stored images are already compressed, you won't see much compression in the backup size.

Tags for this Thread

Posting Permissions

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