HELP w/Restoring db

Yesterday morning, I discovered roughly six tables on our production db had
no data in them. Interestingly enough, it appeared that the tables had been
dropped and then recreated, as the 'Create Date' said '10/31/2005 ' and thes
e
tables were originally created several months ago. I want to inspect the lo
g
files so I downloaded Log Rescue.
I perform a full backup on our db every night, and then tran log backups
every 30 minutes. It had been some time since I had restored a db w/both th
e
.bak files and .trn, so upon reading up a bit, I read that you should back
up
the transaction log before starting the restore (which I did). The timestam
p
on this file is 10/31/2005 10:07 am.
My transaction log files run every thirty minutes, on the hour/half hour.
Now, I want to restore up until we discovered this issue which was
approximately 9:45 am. This is what I did:
1) Restored from .bak file (created nightly at 2am.)
2) Restored each .trn file sequentially (from 2am).
The problem I'm having is when I get to the .trn file from 10:07 am, (which
is the one I created after we discovered the missing data) I receive the
error "The log in this backup set begins at LSN 1340000000240700001, which i
s
too late to apply..." I do not see a 10am .trn file - I have 9:00 am, 9:30
am, and the next is the 10:07am file.
I also tried to restore the db from the .bak, and then restore just the
10:07am log file (ignoring all the others) and I received the same error.At first blush, it sounds as if someone or something deleted the 10AM log
file backup...you are missing one in the sequence. Does the job say it
completed that one? How long did it take?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:36587CEC-F849-4BB2-B22D-B6B6DE05F26A@.microsoft.com...
> Yesterday morning, I discovered roughly six tables on our production db
> had
> no data in them. Interestingly enough, it appeared that the tables had
> been
> dropped and then recreated, as the 'Create Date' said '10/31/2005 ' and
> these
> tables were originally created several months ago. I want to inspect the
> log
> files so I downloaded Log Rescue.
> I perform a full backup on our db every night, and then tran log backups
> every 30 minutes. It had been some time since I had restored a db w/both
> the
> .bak files and .trn, so upon reading up a bit, I read that you should back
> up
> the transaction log before starting the restore (which I did). The
> timestamp
> on this file is 10/31/2005 10:07 am.
> My transaction log files run every thirty minutes, on the hour/half hour.
> Now, I want to restore up until we discovered this issue which was
> approximately 9:45 am. This is what I did:
> 1) Restored from .bak file (created nightly at 2am.)
> 2) Restored each .trn file sequentially (from 2am).
> The problem I'm having is when I get to the .trn file from 10:07 am,
> (which
> is the one I created after we discovered the missing data) I receive the
> error "The log in this backup set begins at LSN 1340000000240700001, which
> is
> too late to apply..." I do not see a 10am .trn file - I have 9:00 am,
> 9:30
> am, and the next is the 10:07am file.
>
> I also tried to restore the db from the .bak, and then restore just the
> 10:07am log file (ignoring all the others) and I received the same error.|||Just checked my Maint. Plan History and the 10AM log was created
successfully.
"Kevin3NF" wrote:

> At first blush, it sounds as if someone or something deleted the 10AM log
> file backup...you are missing one in the sequence. Does the job say it
> completed that one? How long did it take?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:36587CEC-F849-4BB2-B22D-B6B6DE05F26A@.microsoft.com...
>
>|||Eric,
Try using "Log Rescue" to find out who created the tables using the 10:07 am
log backup.
AMB
"Eric" wrote:
> Just checked my Maint. Plan History and the 10AM log was created
> successfully.
> "Kevin3NF" wrote:
>|||If the plan completed the job and wrote the file...yet its not there...I
smell fish. :(
Hopefully you don't have a user trying to cover his/her tracks.
Please post back with whatever you find out...
Kevin3NF
"Eric" wrote:
> Just checked my Maint. Plan History and the 10AM log was created
> successfully.
> "Kevin3NF" wrote:
>|||Thanks for you responses. I don't know of anyone here who would actually
know how to cover their tracks. Our IT dept consits of myself (I handle
development/DBA stuff) and then we have our network guy. Recently we let go
our other devloper, and think maybe this person somehow is responsible.
Anyway, I'm wondering why I can't just restore the BAK, and then restore the
log file I created after i discovered what happened (w/the 10:07 am
timestamp).
Why is it that I still get the 'The log in this backup set begins at LSN
1339000000922300001, which is too late...' error. Any other suggestions
are appreciated.
"Kevin3NF" wrote:
> If the plan completed the job and wrote the file...yet its not there...I
> smell fish. :(
> Hopefully you don't have a user trying to cover his/her tracks.
> Please post back with whatever you find out...
> Kevin3NF
> "Eric" wrote:
>|||When I try to restore from this log file (after restoring the BAK file), I
get the following error: "The log in this backup set begins at LSN
1339000000922300001, which is too late..."
"Alejandro Mesa" wrote:
> Eric,
> Try using "Log Rescue" to find out who created the tables using the 10:07
am
> log backup.
>
> AMB
> "Eric" wrote:
>|||Because the log backups have to all be there, and restored in order. You ar
e
missing one. If you could skip log files, your data becomes inconsistent an
d
suspect
"Eric" wrote:
> Thanks for you responses. I don't know of anyone here who would actually
> know how to cover their tracks. Our IT dept consits of myself (I handle
> development/DBA stuff) and then we have our network guy. Recently we let
go
> our other devloper, and think maybe this person somehow is responsible.
> Anyway, I'm wondering why I can't just restore the BAK, and then restore t
he
> log file I created after i discovered what happened (w/the 10:07 am
> timestamp).
> Why is it that I still get the 'The log in this backup set begins at LSN
> 1339000000922300001, which is too late...' error. Any other suggestions
> are appreciated.
> "Kevin3NF" wrote:
>|||Kevin:
Here's what I've noticed. In the SQL Server log, it says that the 10:00am
log file was created. However, the name of the log file is also the name of
log file I created at 10:07am. So did the one I create somehow overwrite th
e
original?
here's what the SQL Server Log looks like:
Date:
2005-10-31 10:00:00:37
Log backed up: Database: main, creation date(time): 2005/06/02(11:42:32),
first LSN: 1340:2407:1, last LSN: 1340:2689:1, number of dump devices: 1,
device information: (FILE=1, TYPE=DISK: {'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\main\main_tlog_20051
0311000.TRN'}).
And then:
Date:
2005-10-31 10:00:00:37
Log backed up: Database: main, creation date(time): 2005/06/02(11:42:32),
first LSN: 1340:2689:1, last LSN: 1340:2689:1, number of dump devices: 1,
device information: (FILE=2, TYPE=DISK: {'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\main\main_tlog_20051
0311000.TRN'}).
"Kevin3NF" wrote:
> Because the log backups have to all be there, and restored in order. You
are
> missing one. If you could skip log files, your data becomes inconsistent
and
> suspect
> "Eric" wrote:
>|||Notice the FILE=1 and FILE=2 in the entries. Looks to me like you have two
logfiles, and both were backed up (timestamp on the SQL log is the same)
"Eric" wrote:
> Kevin:
> Here's what I've noticed. In the SQL Server log, it says that the 10:00am
> log file was created. However, the name of the log file is also the name
of
> log file I created at 10:07am. So did the one I create somehow overwrite
the
> original?
> here's what the SQL Server Log looks like:
> Date:
> 2005-10-31 10:00:00:37
> Log backed up: Database: main, creation date(time): 2005/06/02(11:42:32),
> first LSN: 1340:2407:1, last LSN: 1340:2689:1, number of dump devices: 1,
> device information: (FILE=1, TYPE=DISK: {'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\main\main_tlog_20051
0311000.TRN'}).
> And then:
> Date:
> 2005-10-31 10:00:00:37
> Log backed up: Database: main, creation date(time): 2005/06/02(11:42:32),
> first LSN: 1340:2689:1, last LSN: 1340:2689:1, number of dump devices: 1,
> device information: (FILE=2, TYPE=DISK: {'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\main\main_tlog_20051
0311000.TRN'}).
> "Kevin3NF" wrote:
>

Help Restoring SQL 7

I have a SQL 7 Server that crashed and I don't have a backup of the data. I
do have the data directory that held the database. How can I get my database
running again? I have reinstalled SQL and tried to overwrite the existing
data directory but that didn't work. Can anyone help me?
If you have all of the database files, primary (.mdf), secondary (.ndf), but
especially the log file(s) (.ldf), then after you've reinstalled, you can use
the primary file (.mdf) to sp_attach_db.
This will do recovery on the database; so, you might want to use copies of
the files at first. If there is corruption, you will need to get with MS PSS
to see if there is a way for them to recover it.
Sincerely,
Anthony Thomas
"New2SQL" wrote:

> I have a SQL 7 Server that crashed and I don't have a backup of the data. I
> do have the data directory that held the database. How can I get my database
> running again? I have reinstalled SQL and tried to overwrite the existing
> data directory but that didn't work. Can anyone help me?

Help Restoring SQL 7

I have a SQL 7 Server that crashed and I don't have a backup of the data. I
do have the data directory that held the database. How can I get my databas
e
running again? I have reinstalled SQL and tried to overwrite the existing
data directory but that didn't work. Can anyone help me?If you have all of the database files, primary (.mdf), secondary (.ndf), but
especially the log file(s) (.ldf), then after you've reinstalled, you can us
e
the primary file (.mdf) to sp_attach_db.
This will do recovery on the database; so, you might want to use copies of
the files at first. If there is corruption, you will need to get with MS PS
S
to see if there is a way for them to recover it.
Sincerely,
Anthony Thomas
"New2SQL" wrote:

> I have a SQL 7 Server that crashed and I don't have a backup of the data.
I
> do have the data directory that held the database. How can I get my datab
ase
> running again? I have reinstalled SQL and tried to overwrite the existing
> data directory but that didn't work. Can anyone help me?

Help Restoring SQL 7

I have a SQL 7 Server that crashed and I don't have a backup of the data. I
do have the data directory that held the database. How can I get my database
running again? I have reinstalled SQL and tried to overwrite the existing
data directory but that didn't work. Can anyone help me?If you have all of the database files, primary (.mdf), secondary (.ndf), but
especially the log file(s) (.ldf), then after you've reinstalled, you can use
the primary file (.mdf) to sp_attach_db.
This will do recovery on the database; so, you might want to use copies of
the files at first. If there is corruption, you will need to get with MS PSS
to see if there is a way for them to recover it.
Sincerely,
Anthony Thomas
"New2SQL" wrote:
> I have a SQL 7 Server that crashed and I don't have a backup of the data. I
> do have the data directory that held the database. How can I get my database
> running again? I have reinstalled SQL and tried to overwrite the existing
> data directory but that didn't work. Can anyone help me?