No Data in a table when database is restored

Question

No Data in a table when database is restored

Sydknee

Old Hand

Points: 360
More actions
September 13, 2017 at 7:59 am

#349122

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

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

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

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 2

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 Old Hand Points: 360 More actions · Answer 3

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: 10248 More actions · Answer 4

Joe Torre

SSChampion

Points: 10248

September 13, 2017 at 10:27 am

#1959286

Can you describe the process in more detail?

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sydknee Old Hand Points: 360 More actions · Answer 6

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"

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 7

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 Old Hand Points: 360 More actions · Answer 8

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: 6354 More actions · Answer 9

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) ?