2

I am trying to backup and restore a 20GB 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.RelationalEngineTasks)

    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...

Improve this question
2
  • 1
    1. can't do in SQL, need to do it in .NET or something similar. You probably try to export it in SSMS, which has some limits. 2. reads more like a backup failure. Run a verify on the backup, or a DBCC CHECKDB on the db itself. 3. That's, most likely, a network issue, not a db one.. try another way of moving it around.
    – Marian
    Commented Oct 29, 2015 at 22:23
  • Thank you Marian. 1) As long as the data is saving in the engine itself, the fact that we can't read it in Management Studio is a lesser issue. I was concerned that whilst backing up the data through MS, this data could get lost. 2) I've done a DBCC CHECKDB on the db and it's got no issues. It appears to have been a problem with the backup itself but I've run another backup and it's now saying: Backup mediaset is not complete. Files: C:\Databases\2015-10-30\zapkam.bak. Family count:2. Missing family sequence number:2 Is it missing something? How could I find what's missing?
    – William P
    Commented Oct 30, 2015 at 16:49

1 Answer 1

Reset to default
2

Instead of using SQL Server SMO code to do a backup of the database, use the T-SQL BACKUP DATABASE command by connecting to the source SQL Server instance using either SQL Server Management Studio (free download) or sqlcmd.exe. Use the optional WITH COMPRESSION parameter to ensure SQL Server compresses the backup, and use the INIT option to ensure the target backup file only contains a single backup.

Once you have the database backup completed, use 7Zip or WinRAR to compress the backup file even further. Send the file to your host using a reliable FTP program, or preferably SecureFTP or SSH, ensuring you set the session to allow binary transfers prior to sending the file. Do not even attempt to restore the database if the FTP transfer does not complete, since SQL Server will not be able to read the backup file.

Once you have the zip/rar file copied to your host, uncompress it using your compression utility of choice, then run RESTORE VERIFYONLY to ensure the target SQL Server can read the entire backup. If that completes, run a RESTORE DATABASE command to actually restore the database onto the target SQL Server.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.