No Data in a table when database is restored

  • Sydknee

    SSC Enthusiast

    Points: 190

    Got a bit of an unusual one that's got me scratching my head.

    I'm covering a colleague while they are on holiday and as per the law of sod, his BI system decides to go wrong on the first day.
    After picking my way through it all, I discovered the issue lies with a database that is restored onto a different server daily is failing to copy the data in one table (again sods law it just happens to be the most important table).
    There are no errors on all his procedures and tasks so this has proved hard to find.
    I've ran the whole process manually and still doesn't bring the data through.
    Checked all the standard things, even restored the backup to a different server.
    Has anyone else ever had this happen.

    Thanks in advance.

    Chris

  • Jeff Moden

    SSC Guru

    Points: 993661

    Sydknee - Wednesday, September 13, 2017 7:59 AM

    Got a bit of an unusual one that's got me scratching my head.

    I'm covering a colleague while they are on holiday and as per the law of sod, his BI system decides to go wrong on the first day.
    After picking my way through it all, I discovered the issue lies with a database that is restored onto a different server daily is failing to copy the data in one table (again sods law it just happens to be the most important table).
    There are no errors on all his procedures and tasks so this has proved hard to find.
    I've ran the whole process manually and still doesn't bring the data through.
    Checked all the standard things, even restored the backup to a different server.
    Has anyone else ever had this happen.

    Thanks in advance.

    Chris

    Did you check to make sure that the table in the source database has data?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Lowell

    SSC Guru

    Points: 323331

    it is not possible that a database restore skips a table.
    that means the table was empty at the time of the backup.
    you most likely need to go to a previous backup and find one where the table was not yet truncated/deleted.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Sydknee

    SSC Enthusiast

    Points: 190

    The original table has data in it and still has, that was one of the first things I checked once I had tracked down what was happening.
    I've tried all the basic stuff and can not find any reason for this, technically its impossible.
    Backed up and restored a number of times both manually and running his processes.
    Data is in the table at the time of the backup and In Theory should be in the restore but for some unknown reason its not.
    The automated processes that run this were not changed and it suddenly started a couple of days ago, no errors or anything to let me know what happened.
    As I said its a real head scratcher.

  • Joe Torre

    SSChampion

    Points: 10223

    Can you describe the process in more detail?

  • Jeff Moden

    SSC Guru

    Points: 993661

    Sydknee - Wednesday, September 13, 2017 8:42 AM

    The original table has data in it and still has, that was one of the first things I checked once I had tracked down what was happening.
    I've tried all the basic stuff and can not find any reason for this, technically its impossible.
    Backed up and restored a number of times both manually and running his processes.
    Data is in the table at the time of the backup and In Theory should be in the restore but for some unknown reason its not.
    The automated processes that run this were not changed and it suddenly started a couple of days ago, no errors or anything to let me know what happened.
    As I said its a real head scratcher.

    Check to see how many actual backup sets there are in the file.  You might be restoring from an earlier set in the same file.  Check your backup code/GUI selection to see if you're appending to the media or not.  If you are, that could certainly be the problem.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Sydknee

    SSC Enthusiast

    Points: 190

    Joe Torre - Wednesday, September 13, 2017 10:27 AM

    Can you describe the process in more detail?

    Its a standard backup and restore procedure.  Nothing more than this process has been doing for a couple of years without issues.

    Jeff Moden - Wednesday, September 13, 2017 10:30 AM

    Sydknee - Wednesday, September 13, 2017 8:42 AM

    The original table has data in it and still has, that was one of the first things I checked once I had tracked down what was happening.
    I've tried all the basic stuff and can not find any reason for this, technically its impossible.
    Backed up and restored a number of times both manually and running his processes.
    Data is in the table at the time of the backup and In Theory should be in the restore but for some unknown reason its not.
    The automated processes that run this were not changed and it suddenly started a couple of days ago, no errors or anything to let me know what happened.
    As I said its a real head scratcher.

    Check to see how many actual backup sets there are in the file.  You might be restoring from an earlier set in the same file.  Check your backup code/GUI selection to see if you're appending to the media or not.  If you are, that could certainly be the problem.

    There are no other backup sets, just a single full backup. 
    Data in the original database has date / time stamp of when it was created, the last entries in the restored database correspond with the time the backup was taken.
    I've managed to narrow it down to the restore on the other server as it restores perfectly on the server with the original database.

    New Marketing slogan for SQL Server - "Making the Impossible, Possible"

  • ThomasRushton

    SSC-Insane

    Points: 22591

    Is it actually a table, or is it an alias for a table elsewhere on the server?

    do you see any error messages when you try to query the table from the other server, or does it return an empty dataset with all the right columns?

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Sydknee

    SSC Enthusiast

    Points: 190

    ThomasRushton - Thursday, September 14, 2017 4:02 AM

    Is it actually a table, or is it an alias for a table elsewhere on the server?

    do you see any error messages when you try to query the table from the other server, or does it return an empty dataset with all the right columns?

    It is actually a table, when you query the table it returns nothing, no errors.

  • Arsh

    SSCertifiable

    Points: 6031

    Can you query the data in other tables in the target database and let know if they have the latest data (in accordance with backup timestamp at source) ?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply