Data appears to be missing in over 10 SQL tables in a database on our SQL
2005 server. Here are the facts:

1. The data missing is from 3/31/2008 to 6/9/2008
2. The data was in the table on 6/9/2008 as we have reports that show data
for this period
3. The data disappeared after a restart of the server on 6/10/2008
4. The database had a date and time stamp of 3/29/2008, but all other
databases were dated 6/10/2008 after the restart
5. The database has been restored from two different backups and still shows
data missing even though the reports show that the data was in the database
when the backups were made
6. The database is using simple logging mode
7. The backups were made using our SAN software backup util that appears to
backup the database file and the log file
8. No error messages were raised after the restart or when attaching the
restored database files

Can anyone explain what happened to the data and if there is anyway to
recover it. DBCC Checkdb shows the database to be consistant.

What happened to the data is that someone or some thing deleted it. Plain
and simple.

With simple mode you cannot review the transaction logs to recover the data.
Seems like your ownly solution would be to use a comparison tool such as
Diff from ApexSQL or SQLCompare from Red-Gate to find the data in either
backups or restored copies of the database and migrate it to the production
copy.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
wrote in message news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
> Data appears to be missing in over 10 SQL tables in a database on our SQL
> 2005 server. Here are the facts:
>
> 1. The data missing is from 3/31/2008 to 6/9/2008
> 2. The data was in the table on 6/9/2008 as we have reports that show data
> for this period
> 3. The data disappeared after a restart of the server on 6/10/2008
> 4. The database had a date and time stamp of 3/29/2008, but all other
> databases were dated 6/10/2008 after the restart
> 5. The database has been restored from two different backups and still
> shows
> data missing even though the reports show that the data was in the
> database
> when the backups were made
> 6. The database is using simple logging mode
> 7. The backups were made using our SAN software backup util that appears
> to
> backup the database file and the log file
> 8. No error messages were raised after the restart or when attaching the
> restored database files
>
> Can anyone explain what happened to the data and if there is anyway to
> recover it. DBCC Checkdb shows the database to be consistant.
>

No they didn't the data was there one day and gone the next the backups made
before the data disappeared when restored do not show the data either.

"TheSQLGuru" wrote:

> What happened to the data is that someone or some thing deleted it. Plain
> and simple.
>
> With simple mode you cannot review the transaction logs to recover the data.
> Seems like your ownly solution would be to use a comparison tool such as
> Diff from ApexSQL or SQLCompare from Red-Gate to find the data in either
> backups or restored copies of the database and migrate it to the production
> copy.
>
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
> wrote in message news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
> > Data appears to be missing in over 10 SQL tables in a database on our SQL
> > 2005 server. Here are the facts:
> >
> > 1. The data missing is from 3/31/2008 to 6/9/2008
> > 2. The data was in the table on 6/9/2008 as we have reports that show data
> > for this period
> > 3. The data disappeared after a restart of the server on 6/10/2008
> > 4. The database had a date and time stamp of 3/29/2008, but all other
> > databases were dated 6/10/2008 after the restart
> > 5. The database has been restored from two different backups and still
> > shows
> > data missing even though the reports show that the data was in the
> > database
> > when the backups were made
> > 6. The database is using simple logging mode
> > 7. The backups were made using our SAN software backup util that appears
> > to
> > backup the database file and the log file
> > 8. No error messages were raised after the restart or when attaching the
> > restored database files
> >
> > Can anyone explain what happened to the data and if there is anyway to
> > recover it. DBCC Checkdb shows the database to be consistant.
> >
>
>
>

Sorry, I just can't accept that statement at face value. Nothing personal
meant by that either, btw.

Either:
1) you are incorrect in your statement that the data was there at the point
in time you say it was (despite the fact that is wasn't included in previous
backups)
2) the data was there and truly was deleted and you are simply not aware of
that
3) you have stumbled across a massively damaging bug in the engine (since
you stated that dbcc checkdb showed no problems), in which case it is
definitely time to get on the horn with Microsoft. I have no idea how they
could investigate this without proof of exactly when the data went missing
and what may have caused it though.

My vote is on 1 or 2 or perhaps some other scenario that is not known or
suspected at this time. Perhaps the missing data resided on separate file
groups that were removed from the system or some such.


--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
wrote in message news:13242B19-0159-4028-B6A5-BCE6BB73DC05@microsoft.com...
> No they didn't the data was there one day and gone the next the backups
> made
> before the data disappeared when restored do not show the data either.
>
> "TheSQLGuru" wrote:
>
>> What happened to the data is that someone or some thing deleted it.
>> Plain
>> and simple.
>>
>> With simple mode you cannot review the transaction logs to recover the
>> data.
>> Seems like your ownly solution would be to use a comparison tool such as
>> Diff from ApexSQL or SQLCompare from Red-Gate to find the data in either
>> backups or restored copies of the database and migrate it to the
>> production
>> copy.
>>
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "davids@thorntonsinc.com"
>> <davidsthorntonsinccom@discussions.microsoft.com >
>> wrote in message
>> news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
>> > Data appears to be missing in over 10 SQL tables in a database on our
>> > SQL
>> > 2005 server. Here are the facts:
>> >
>> > 1. The data missing is from 3/31/2008 to 6/9/2008
>> > 2. The data was in the table on 6/9/2008 as we have reports that show
>> > data
>> > for this period
>> > 3. The data disappeared after a restart of the server on 6/10/2008
>> > 4. The database had a date and time stamp of 3/29/2008, but all other
>> > databases were dated 6/10/2008 after the restart
>> > 5. The database has been restored from two different backups and still
>> > shows
>> > data missing even though the reports show that the data was in the
>> > database
>> > when the backups were made
>> > 6. The database is using simple logging mode
>> > 7. The backups were made using our SAN software backup util that
>> > appears
>> > to
>> > backup the database file and the log file
>> > 8. No error messages were raised after the restart or when attaching
>> > the
>> > restored database files
>> >
>> > Can anyone explain what happened to the data and if there is anyway to
>> > recover it. DBCC Checkdb shows the database to be consistant.
>> >
>>
>>
>>

David,

TheSQLGuru is correct, someone or someTHING deleted it. The question is
what things might happen. Since you said the database at restart had a
timestamp of 3/29/2008, could you do the following select to make sure that
no restore was run unawares:

SELECT * FROM msdb.dbo.restorehistory
WHERE destination_database_name = 'yourdatabasename'
ORDER BY restore_date DESC

Or (getting crazy here) could a copy of the database files (mdf & ldf) from
March have been copied over the files you were using while the SQL Server
was shut down?

(FWIW: Way back on SQL Server 4.2 I had a database corruption problem that
lost me some data in a couple of tables, but nothing like that since at
least SQL Server 7.0. But 10 tables, all losing the same date range does
not at all sound like a corruption problem.)

RLF

"davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
wrote in message news:13242B19-0159-4028-B6A5-BCE6BB73DC05@microsoft.com...
> No they didn't the data was there one day and gone the next the backups
> made
> before the data disappeared when restored do not show the data either.
>
> "TheSQLGuru" wrote:
>
>> What happened to the data is that someone or some thing deleted it.
>> Plain
>> and simple.
>>
>> With simple mode you cannot review the transaction logs to recover the
>> data.
>> Seems like your ownly solution would be to use a comparison tool such as
>> Diff from ApexSQL or SQLCompare from Red-Gate to find the data in either
>> backups or restored copies of the database and migrate it to the
>> production
>> copy.
>>
>>
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>>
>> "davids@thorntonsinc.com"
>> <davidsthorntonsinccom@discussions.microsoft.com >
>> wrote in message
>> news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
>> > Data appears to be missing in over 10 SQL tables in a database on our
>> > SQL
>> > 2005 server. Here are the facts:
>> >
>> > 1. The data missing is from 3/31/2008 to 6/9/2008
>> > 2. The data was in the table on 6/9/2008 as we have reports that show
>> > data
>> > for this period
>> > 3. The data disappeared after a restart of the server on 6/10/2008
>> > 4. The database had a date and time stamp of 3/29/2008, but all other
>> > databases were dated 6/10/2008 after the restart
>> > 5. The database has been restored from two different backups and still
>> > shows
>> > data missing even though the reports show that the data was in the
>> > database
>> > when the backups were made
>> > 6. The database is using simple logging mode
>> > 7. The backups were made using our SAN software backup util that
>> > appears
>> > to
>> > backup the database file and the log file
>> > 8. No error messages were raised after the restart or when attaching
>> > the
>> > restored database files
>> >
>> > Can anyone explain what happened to the data and if there is anyway to
>> > recover it. DBCC Checkdb shows the database to be consistant.
>> >
>>
>>
>>

Would anyone that might acutally be reponse to this post. I don't need
a lecture on things that didn't happen.


"Russell Fields" wrote:

> David,
>
> TheSQLGuru is correct, someone or someTHING deleted it. The question is
> what things might happen. Since you said the database at restart had a
> timestamp of 3/29/2008, could you do the following select to make sure that
> no restore was run unawares:
>
> SELECT * FROM msdb.dbo.restorehistory
> WHERE destination_database_name = 'yourdatabasename'
> ORDER BY restore_date DESC
>
> Or (getting crazy here) could a copy of the database files (mdf & ldf) from
> March have been copied over the files you were using while the SQL Server
> was shut down?
>
> (FWIW: Way back on SQL Server 4.2 I had a database corruption problem that
> lost me some data in a couple of tables, but nothing like that since at
> least SQL Server 7.0. But 10 tables, all losing the same date range does
> not at all sound like a corruption problem.)
>
> RLF
>
> "davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
> wrote in message news:13242B19-0159-4028-B6A5-BCE6BB73DC05@microsoft.com...
> > No they didn't the data was there one day and gone the next the backups
> > made
> > before the data disappeared when restored do not show the data either.
> >
> > "TheSQLGuru" wrote:
> >
> >> What happened to the data is that someone or some thing deleted it.
> >> Plain
> >> and simple.
> >>
> >> With simple mode you cannot review the transaction logs to recover the
> >> data.
> >> Seems like your ownly solution would be to use a comparison tool such as
> >> Diff from ApexSQL or SQLCompare from Red-Gate to find the data in either
> >> backups or restored copies of the database and migrate it to the
> >> production
> >> copy.
> >>
> >>
> >> --
> >> Kevin G. Boles
> >> Indicium Resources, Inc.
> >> SQL Server MVP
> >> kgboles a earthlink dt net
> >>
> >>
> >> "davids@thorntonsinc.com"
> >> <davidsthorntonsinccom@discussions.microsoft.com >
> >> wrote in message
> >> news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
> >> > Data appears to be missing in over 10 SQL tables in a database on our
> >> > SQL
> >> > 2005 server. Here are the facts:
> >> >
> >> > 1. The data missing is from 3/31/2008 to 6/9/2008
> >> > 2. The data was in the table on 6/9/2008 as we have reports that show
> >> > data
> >> > for this period
> >> > 3. The data disappeared after a restart of the server on 6/10/2008
> >> > 4. The database had a date and time stamp of 3/29/2008, but all other
> >> > databases were dated 6/10/2008 after the restart
> >> > 5. The database has been restored from two different backups and still
> >> > shows
> >> > data missing even though the reports show that the data was in the
> >> > database
> >> > when the backups were made
> >> > 6. The database is using simple logging mode
> >> > 7. The backups were made using our SAN software backup util that
> >> > appears
> >> > to
> >> > backup the database file and the log file
> >> > 8. No error messages were raised after the restart or when attaching
> >> > the
> >> > restored database files
> >> >
> >> > Can anyone explain what happened to the data and if there is anyway to
> >> > recover it. DBCC Checkdb shows the database to be consistant.
> >> >
> >>
> >>
> >>
>
>
>

You WERE offered other explanations: possible restore you are unaware of
(which could include some action by a SAN admin at the database file level)
and file-group dropping, or an INCREDIBLY unlikely bug in the engine.

Why don't you give Microsoft a call and see what they say to your situation?
My money says they will state unequivocally that your data did not disappear
without outside-the-engine influence.

Another possibility came to mind: someone dropped the database and
reattached it from older files.

Also, did you scan the error logs for anything related to the problem
database?

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
wrote in message news:1E30E04C-FADA-4358-916D-314A74828CBB@microsoft.com...
> Would anyone that might acutally be reponse to this post. I don't
> need
> a lecture on things that didn't happen.
>
>
> "Russell Fields" wrote:
>
>> David,
>>
>> TheSQLGuru is correct, someone or someTHING deleted it. The question is
>> what things might happen. Since you said the database at restart had a
>> timestamp of 3/29/2008, could you do the following select to make sure
>> that
>> no restore was run unawares:
>>
>> SELECT * FROM msdb.dbo.restorehistory
>> WHERE destination_database_name = 'yourdatabasename'
>> ORDER BY restore_date DESC
>>
>> Or (getting crazy here) could a copy of the database files (mdf & ldf)
>> from
>> March have been copied over the files you were using while the SQL Server
>> was shut down?
>>
>> (FWIW: Way back on SQL Server 4.2 I had a database corruption problem
>> that
>> lost me some data in a couple of tables, but nothing like that since at
>> least SQL Server 7.0. But 10 tables, all losing the same date range does
>> not at all sound like a corruption problem.)
>>
>> RLF
>>
>> "davids@thorntonsinc.com"
>> <davidsthorntonsinccom@discussions.microsoft.com >
>> wrote in message
>> news:13242B19-0159-4028-B6A5-BCE6BB73DC05@microsoft.com...
>> > No they didn't the data was there one day and gone the next the backups
>> > made
>> > before the data disappeared when restored do not show the data either.
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> What happened to the data is that someone or some thing deleted it.
>> >> Plain
>> >> and simple.
>> >>
>> >> With simple mode you cannot review the transaction logs to recover the
>> >> data.
>> >> Seems like your ownly solution would be to use a comparison tool such
>> >> as
>> >> Diff from ApexSQL or SQLCompare from Red-Gate to find the data in
>> >> either
>> >> backups or restored copies of the database and migrate it to the
>> >> production
>> >> copy.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> Indicium Resources, Inc.
>> >> SQL Server MVP
>> >> kgboles a earthlink dt net
>> >>
>> >>
>> >> "davids@thorntonsinc.com"
>> >> <davidsthorntonsinccom@discussions.microsoft.com >
>> >> wrote in message
>> >> news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
>> >> > Data appears to be missing in over 10 SQL tables in a database on
>> >> > our
>> >> > SQL
>> >> > 2005 server. Here are the facts:
>> >> >
>> >> > 1. The data missing is from 3/31/2008 to 6/9/2008
>> >> > 2. The data was in the table on 6/9/2008 as we have reports that
>> >> > show
>> >> > data
>> >> > for this period
>> >> > 3. The data disappeared after a restart of the server on 6/10/2008
>> >> > 4. The database had a date and time stamp of 3/29/2008, but all
>> >> > other
>> >> > databases were dated 6/10/2008 after the restart
>> >> > 5. The database has been restored from two different backups and
>> >> > still
>> >> > shows
>> >> > data missing even though the reports show that the data was in the
>> >> > database
>> >> > when the backups were made
>> >> > 6. The database is using simple logging mode
>> >> > 7. The backups were made using our SAN software backup util that
>> >> > appears
>> >> > to
>> >> > backup the database file and the log file
>> >> > 8. No error messages were raised after the restart or when attaching
>> >> > the
>> >> > restored database files
>> >> >
>> >> > Can anyone explain what happened to the data and if there is anyway
>> >> > to
>> >> > recover it. DBCC Checkdb shows the database to be consistant.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

David,

When a doctor is trying to work through your symptoms, how is he supposed to
know what happened or did not happen without asking questions, using the
stethoscope, taking X-Rays, etc? Nobody here is trying to say you are
either clueless or lying, but we are trying to you dig through
possibilities. (Especially since we cannot login to your server and domain
and examine everything ourselves.)

So, what mysteriously caused all data in tables from 3/31/2008 to 6/9/2008
to disappear? Got no idea.

RLF

"davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
wrote in message news:1E30E04C-FADA-4358-916D-314A74828CBB@microsoft.com...
> Would anyone that might acutally be reponse to this post. I don't
> need
> a lecture on things that didn't happen.
>
>
> "Russell Fields" wrote:
>
>> David,
>>
>> TheSQLGuru is correct, someone or someTHING deleted it. The question is
>> what things might happen. Since you said the database at restart had a
>> timestamp of 3/29/2008, could you do the following select to make sure
>> that
>> no restore was run unawares:
>>
>> SELECT * FROM msdb.dbo.restorehistory
>> WHERE destination_database_name = 'yourdatabasename'
>> ORDER BY restore_date DESC
>>
>> Or (getting crazy here) could a copy of the database files (mdf & ldf)
>> from
>> March have been copied over the files you were using while the SQL Server
>> was shut down?
>>
>> (FWIW: Way back on SQL Server 4.2 I had a database corruption problem
>> that
>> lost me some data in a couple of tables, but nothing like that since at
>> least SQL Server 7.0. But 10 tables, all losing the same date range does
>> not at all sound like a corruption problem.)
>>
>> RLF
>>
>> "davids@thorntonsinc.com"
>> <davidsthorntonsinccom@discussions.microsoft.com >
>> wrote in message
>> news:13242B19-0159-4028-B6A5-BCE6BB73DC05@microsoft.com...
>> > No they didn't the data was there one day and gone the next the backups
>> > made
>> > before the data disappeared when restored do not show the data either.
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> What happened to the data is that someone or some thing deleted it.
>> >> Plain
>> >> and simple.
>> >>
>> >> With simple mode you cannot review the transaction logs to recover the
>> >> data.
>> >> Seems like your ownly solution would be to use a comparison tool such
>> >> as
>> >> Diff from ApexSQL or SQLCompare from Red-Gate to find the data in
>> >> either
>> >> backups or restored copies of the database and migrate it to the
>> >> production
>> >> copy.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> Indicium Resources, Inc.
>> >> SQL Server MVP
>> >> kgboles a earthlink dt net
>> >>
>> >>
>> >> "davids@thorntonsinc.com"
>> >> <davidsthorntonsinccom@discussions.microsoft.com >
>> >> wrote in message
>> >> news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
>> >> > Data appears to be missing in over 10 SQL tables in a database on
>> >> > our
>> >> > SQL
>> >> > 2005 server. Here are the facts:
>> >> >
>> >> > 1. The data missing is from 3/31/2008 to 6/9/2008
>> >> > 2. The data was in the table on 6/9/2008 as we have reports that
>> >> > show
>> >> > data
>> >> > for this period
>> >> > 3. The data disappeared after a restart of the server on 6/10/2008
>> >> > 4. The database had a date and time stamp of 3/29/2008, but all
>> >> > other
>> >> > databases were dated 6/10/2008 after the restart
>> >> > 5. The database has been restored from two different backups and
>> >> > still
>> >> > shows
>> >> > data missing even though the reports show that the data was in the
>> >> > database
>> >> > when the backups were made
>> >> > 6. The database is using simple logging mode
>> >> > 7. The backups were made using our SAN software backup util that
>> >> > appears
>> >> > to
>> >> > backup the database file and the log file
>> >> > 8. No error messages were raised after the restart or when attaching
>> >> > the
>> >> > restored database files
>> >> >
>> >> > Can anyone explain what happened to the data and if there is anyway
>> >> > to
>> >> > recover it. DBCC Checkdb shows the database to be consistant.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

I don't see a lecture anywhere. I see ful suggestions trying to offer
things for you to look into to discover a cause. Since there isn't a magic
button labeled "delete all data in all tables, where some column has a value
between date 1 and date 2", and since we can't investigate for ourselves,
I'm not sure what else you might expect. Complaining about the
certainly isn't going to your situation.




On 6/12/08 2:06 PM, in article
1E30E04C-FADA-4358-916D-314A74828CBB@microsoft.com,
"davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
wrote:

> Would anyone that might acutally be reponse to this post. I don't need
> a lecture on things that didn't happen.

Well then let’s try this again.

1. No one but me has ever restored or attached a database no one in our
company knows how to it or has access to it and our IT department is the size
of a peanut.
2. The data existing before a backup was made
3. The data was gone after a restart
4. The backup was restored and the data was still gone

These are NOT guesses or estimation but hard facts. I don’t want to believe
you don’t want to believe it, but it is true. I’ve worked here for seven
years and created the database seven years ago and this is the first time
this has ever happened to me. Tell me that this isn’t what happened is
useless. Take the scenario and run with it. If you don’t have answer then
simply say there is not answer and call MS and be done with it. My firm
belief is that the database had some kind of corruption back in March that
set all of this in motion and that no one will be able to fix it. I figured
before giving up completely I would post it here to see if anyone has
experienced the issue in the past. I’ve worked in the computer industry for
20 years and MS products for 12 years so I’m not new to SQL Server I’ve been
a dedicated fan since SQL 7.0 and would recommend the products anyone looking
to buy.


"Aaron Bertrand [SQL Server MVP]" wrote:

> I don't see a lecture anywhere. I see ful suggestions trying to offer
> things for you to look into to discover a cause. Since there isn't a magic
> button labeled "delete all data in all tables, where some column has a value
> between date 1 and date 2", and since we can't investigate for ourselves,
> I'm not sure what else you might expect. Complaining about the
> certainly isn't going to your situation.
>
>
>
>
> On 6/12/08 2:06 PM, in article
> 1E30E04C-FADA-4358-916D-314A74828CBB@microsoft.com,
> "davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
> wrote:
>
> > Would anyone that might acutally be reponse to this post. I don't need
> > a lecture on things that didn't happen.
>
>
>

> 4. The database had a date and time stamp of 3/29/2008, but all other
> databases were dated 6/10/2008 after the restart

I think this is an important clue. Database corruption won't change file
file date. I suggest you verify the correct storage was presented to the
host after the restart. Perhaps the current file is still out there
somewhere in SAN-land.

> 7. The backups were made using our SAN software backup util that appears
> to
> backup the database file and the log file

Do you have any SQL backups or only backups at the SAN level?

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
wrote in message news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
> Data appears to be missing in over 10 SQL tables in a database on our SQL
> 2005 server. Here are the facts:
>
> 1. The data missing is from 3/31/2008 to 6/9/2008
> 2. The data was in the table on 6/9/2008 as we have reports that show data
> for this period
> 3. The data disappeared after a restart of the server on 6/10/2008
> 4. The database had a date and time stamp of 3/29/2008, but all other
> databases were dated 6/10/2008 after the restart
> 5. The database has been restored from two different backups and still
> shows
> data missing even though the reports show that the data was in the
> database
> when the backups were made
> 6. The database is using simple logging mode
> 7. The backups were made using our SAN software backup util that appears
> to
> backup the database file and the log file
> 8. No error messages were raised after the restart or when attaching the
> restored database files
>
> Can anyone explain what happened to the data and if there is anyway to
> recover it. DBCC Checkdb shows the database to be consistant.
>

> useless. Take the scenario and run with it. If you don¹t have answer then
> simply say there is not answer and call MS and be done with it.

If that's the only answer you will accept, then call MS and be done with it.

Since calling MS will most certainly cost you money, and will not resolve
the problem in minutes, we were trying to point out things to investigate so
that maybe that call would not be necessary. If you just want to continue
to tell us that none of our theories are possible, what more do you want?
<shrug>

A

I've checked the restore log no restores have been performed on the database
prior to this one in the past two years.
I got the files directly from the Sys admin and reattached them from two
separate backups with the same affect.
We have no backups made directly by MSSQL all backups are handled by the sys
admin using the SAN and Backup Exec with the Backup Exec SQL Agent.
The same storage system that this database is using is the same one that all
of the other databases used that came up properly this still wouldn’t explain
why the database is missing the data after a restore. The database has been
restored to two different servers both running SQL Server with the same
affect.

One again to clarify:

Data in the database verified on reports
Database backup
Same Data still appears on reports
Restart occurs
Data gone
2 different restores performed to serverX and 2 different restores performed
to serverY and data still gone

Even with moving storage, SAN gone south, and everyone here having the
intelligence of a gelatinous cube the restore should still have the data. We
have done restores in the past to other databases without issue so that leads
me to believe that the backup/restore part of the equation works or at least
it has in the past.


"Dan Guzman" wrote:

> > 4. The database had a date and time stamp of 3/29/2008, but all other
> > databases were dated 6/10/2008 after the restart
>
> I think this is an important clue. Database corruption won't change file
> file date. I suggest you verify the correct storage was presented to the
> host after the restart. Perhaps the current file is still out there
> somewhere in SAN-land.
>
> > 7. The backups were made using our SAN software backup util that appears
> > to
> > backup the database file and the log file
>
> Do you have any SQL backups or only backups at the SAN level?
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
> wrote in message news:1F886A4D-8FB8-4817-959A-8E366E24788D@microsoft.com...
> > Data appears to be missing in over 10 SQL tables in a database on our SQL
> > 2005 server. Here are the facts:
> >
> > 1. The data missing is from 3/31/2008 to 6/9/2008
> > 2. The data was in the table on 6/9/2008 as we have reports that show data
> > for this period
> > 3. The data disappeared after a restart of the server on 6/10/2008
> > 4. The database had a date and time stamp of 3/29/2008, but all other
> > databases were dated 6/10/2008 after the restart
> > 5. The database has been restored from two different backups and still
> > shows
> > data missing even though the reports show that the data was in the
> > database
> > when the backups were made
> > 6. The database is using simple logging mode
> > 7. The backups were made using our SAN software backup util that appears
> > to
> > backup the database file and the log file
> > 8. No error messages were raised after the restart or when attaching the
> > restored database files
> >
> > Can anyone explain what happened to the data and if there is anyway to
> > recover it. DBCC Checkdb shows the database to be consistant.
> >
>

On Jun 13, 2:46am, dav...@thorntonsinc.com
<davidsthorntonsinc...@discussions.microsoft.com > wrote:
> I've checked the restore log no restores have been performed on the database
> prior to this one in the past two years.
> I got the files directly from the Sys admin and reattached them from two
> separate backups with the same affect.
> We have no backups made directly by MSSQL all backups are handled by the sys
> admin using the SAN and Backup Exec with the Backup Exec SQL Agent.
> The same storage system that this database is using is the same one that all
> of the other databases used that came up properly this still wouldn’t explain
> why the database is missing the data after a restore. The database has been
> restored to two different servers both running SQL Server with the same
> affect.
>
> One again to clarify:
>
> Data in the database verified on reports
> Database backup
> Same Data still appears on reports
> Restart occurs
> Data gone
> 2 different restores performed to serverX and 2 different restores performed
> to serverY and data still gone
>
> Even with moving storage, SAN gone south, and everyone here having the
> intelligence of a gelatinous cube the restore should still have the data. We
> have done restores in the past to other databases without issue so that leads
> me to believe that the backup/restore part of the equation works or at least
> it has in the past.

So, to summarize once again:

- your sysadmin hands you a MDF and LDF file and says "this is the
latest backup"
- files are dated 3/29/2008
- you attach the files and have no data later than 3/29/2008

Isn't it PAINFULLY obvious what's happening here? Seriously?

I don't know how to you recover this "lost" data, but I'm 100%
confident that SQL Server didn't eat it. Personally, I NEVER, EVER
rely on a "sysadmin" to be responsible for my database backups. I do
my own backups, database and logs, directly to disk. The "sysadmin"
or tape jockey grabs those disk files and does what he will with
them. I always have recent backups available to me on disk, copied to
multiple locations, so that I know exactly what I have to work with in
case of emergency.

No data is in the these files data 3/31 in a file dated 3/29. I'm not a DBA
I'm a developer. I don't care how you maintain your backups.

"Tracy McKibben (http://www.realsqlguy.co" wrote:

> On Jun 13, 2:46 am, dav...@thorntonsinc.com
> <davidsthorntonsinc...@discussions.microsoft.com > wrote:
> > I've checked the restore log no restores have been performed on the database
> > prior to this one in the past two years.
> > I got the files directly from the Sys admin and reattached them from two
> > separate backups with the same affect.
> > We have no backups made directly by MSSQL all backups are handled by the sys
> > admin using the SAN and Backup Exec with the Backup Exec SQL Agent.
> > The same storage system that this database is using is the same one that all
> > of the other databases used that came up properly this still wouldn’t explain
> > why the database is missing the data after a restore. The database has been
> > restored to two different servers both running SQL Server with the same
> > affect.
> >
> > One again to clarify:
> >
> > Data in the database verified on reports
> > Database backup
> > Same Data still appears on reports
> > Restart occurs
> > Data gone
> > 2 different restores performed to serverX and 2 different restores performed
> > to serverY and data still gone
> >
> > Even with moving storage, SAN gone south, and everyone here having the
> > intelligence of a gelatinous cube the restore should still have the data. We
> > have done restores in the past to other databases without issue so that leads
> > me to believe that the backup/restore part of the equation works or at least
> > it has in the past.
>
> So, to summarize once again:
>
> - your sysadmin hands you a MDF and LDF file and says "this is the
> latest backup"
> - files are dated 3/29/2008
> - you attach the files and have no data later than 3/29/2008
>
> Isn't it PAINFULLY obvious what's happening here? Seriously?
>
> I don't know how to you recover this "lost" data, but I'm 100%
> confident that SQL Server didn't eat it. Personally, I NEVER, EVER
> rely on a "sysadmin" to be responsible for my database backups. I do
> my own backups, database and logs, directly to disk. The "sysadmin"
> or tape jockey grabs those disk files and does what he will with
> them. I always have recent backups available to me on disk, copied to
> multiple locations, so that I know exactly what I have to work with in
> case of emergency.
>

> No data is in the these files data 3/31 in a file dated 3/29.

If a file was last modified on 3/29, why would you expect it to contain data
from 3/31? Does your paycheck dated 6/15 include payment for hours you
worked on 6/21?

While the MDF file doesn't change for every single data modification, the
timestamp would certainly change if it was detached properly, copied, backed
up/restored, etc.

> I don't care how you maintain your backups.

You certainly aren't speaking to the people who are tying to you, like
a person who actually wants . :-(

What Tracy is trying to explain to you is that if you use proper SQL backups
instead of letting the SAN guys do it, you would have a better chance at
recovery the next time this happens... or more likely, it won't happen at
all.

In the meantime, I am fairly confident you are pooched, unless you find
another copy of that database somewhere. Have you called MS yet?

1. I have NOT asked for a suggested back up strategy so offering one is of
no .
2. Stating that it is obvious what happened does either
3. My question is if you have nothing to offer that will deal with the
current situation why post at all. I know the company and they are not going
to hire a DBA or pay for the better back up plan I’ve asked they have said
no. I could just say you get what you pay for, but I like give anything my
best effort.
4. I didn’t start acting like an ass until I was treated like a total idiot.
5. I know where the backups come from and I know the dates.

Let’s clear this up.

A restore from 5/28 returns MDF and LDF with a 3/29 timestamp on them.
These database files contain data from 3/31 which also makes no sense. When
the restart occurred this is the same date it put on the files. All of the
restores have returned exactly the same data. I don’t expect anyone to
because no one takes this at face value they keep pointing their fingers at a
delete or backup/restores that did not occur. I’ve pretty much given up on
recovering the data I would like to be to explain what happened, but that
seems to be impossible as well.

"Aaron Bertrand [SQL Server MVP]" wrote:

> > No data is in the these files data 3/31 in a file dated 3/29.
>
> If a file was last modified on 3/29, why would you expect it to contain data
> from 3/31? Does your paycheck dated 6/15 include payment for hours you
> worked on 6/21?
>
> While the MDF file doesn't change for every single data modification, the
> timestamp would certainly change if it was detached properly, copied, backed
> up/restored, etc.
>
> > I don't care how you maintain your backups.
>
> You certainly aren't speaking to the people who are tying to you, like
> a person who actually wants . :-(
>
> What Tracy is trying to explain to you is that if you use proper SQL backups
> instead of letting the SAN guys do it, you would have a better chance at
> recovery the next time this happens... or more likely, it won't happen at
> all.
>
> In the meantime, I am fairly confident you are pooched, unless you find
> another copy of that database somewhere. Have you called MS yet?
>
>

Yep, I guess so. <shrug> Hope you don't approach all problems this way.




On 6/13/08 1:43 PM, in article
048CF447-E0A7-4BD6-BC99-3798E2D0C3A8@microsoft.com,
"davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
wrote:

> 1. I have NOT asked for a suggested back up strategy so offering one is of
> no .
> 2. Stating that it is obvious what happened does either
> 3. My question is if you have nothing to offer that will deal with the
> current situation why post at all. I know the company and they are not going
> to hire a DBA or pay for the better back up plan I¹ve asked they have said
> no. I could just say you get what you pay for, but I like give anything my
> best effort.
> 4. I didn¹t start acting like an ass until I was treated like a total idiot.
> 5. I know where the backups come from and I know the dates.
>
> Let¹s clear this up.
>
> A restore from 5/28 returns MDF and LDF with a 3/29 timestamp on them.
> These database files contain data from 3/31 which also makes no sense. When
> the restart occurred this is the same date it put on the files. All of the
> restores have returned exactly the same data. I don¹t expect anyone to
> because no one takes this at face value they keep pointing their fingers at a
> delete or backup/restores that did not occur. I¹ve pretty much given up on
> recovering the data I would like to be to explain what happened, but that
> seems to be impossible as well.

davids@thorntonsinc.com wrote:
I’ve worked in the computer industry for
> 20 years and MS products for 12 years so I’m not new to SQL Server I’ve been
> a dedicated fan since SQL 7.0 and would recommend the products anyone looking
> to buy.

With the attitude you show, I am highly skeptical of the above
statement. You clearly refuse to accept ful ideas to you for free
about YOUR PROBLEM, get a clue, lose the attitude and be grateful for
their time and expertise.

Thanks I find the "V" in MVP infinitely valuable, nothing personal
meant by that either, btw.

"Aaron Bertrand [SQL Server MVP]" wrote:

> Yep, I guess so. <shrug> Hope you don't approach all problems this way.
>
>
>
>
> On 6/13/08 1:43 PM, in article
> 048CF447-E0A7-4BD6-BC99-3798E2D0C3A8@microsoft.com,
> "davids@thorntonsinc.com" <davidsthorntonsinccom@discussions.microsoft.com >
> wrote:
>
> > 1. I have NOT asked for a suggested back up strategy so offering one is of
> > no .
> > 2. Stating that it is obvious what happened does either
> > 3. My question is if you have nothing to offer that will deal with the
> > current situation why post at all. I know the company and they are not going
> > to hire a DBA or pay for the better back up plan I¹ve asked they have said
> > no. I could just say you get what you pay for, but I like give anything my
> > best effort.
> > 4. I didn¹t start acting like an ass until I was treated like a total idiot.
> > 5. I know where the backups come from and I know the dates.
> >
> > Let¹s clear this up.
> >
> > A restore from 5/28 returns MDF and LDF with a 3/29 timestamp on them.
> > These database files contain data from 3/31 which also makes no sense. When
> > the restart occurred this is the same date it put on the files. All of the
> > restores have returned exactly the same data. I don¹t expect anyone to
> > because no one takes this at face value they keep pointing their fingers at a
> > delete or backup/restores that did not occur. I¹ve pretty much given up on
> > recovering the data I would like to be to explain what happened, but that
> > seems to be impossible as well.
>
>