Restoring from non-truncated transaction log

We have a database which unfortunately someone has restored from a backup
which was 2+ months old after he ran a script against the database that had
negative effects. So, now there is a gap in the data from Nov23 – Jan 31.
What I am wondering is would it be possible to restore the missing data in
the database with the transaction log? We were not backing up the
transaction log; so, it was not truncated. We have detached the database an
d
the transaction log and reattached them on another server.
For some reason every time I explain this in person, people miss something.
So, here are the basic facts:
MS SQL server 2000 database restored from backup
2 months of missing data
in tact(not truncated) transaction log
a script was run against the database before it was restored from backup on
Jan 31
database structure is identical.
Is this savable data, or should we just give up. I am not a DBA. He quit
about 1.5 months ago; so please be specific about what to do as I am not yet
extreamly familiar with SQL server 2000. By the way we have been working on
this since the 31st, and all we know for sure is that SQL doesn't like it
when you do something like this. Thanks in advance for any guidance either
way.> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that ha
d
> negative effects.
That restore overwrites everything inside the database. Including the entrie
s in the transaction
log. This of a restore like applying a VM Ware image on a machine.

> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log?
Are you saying that you saved something, somehow (backup, detach) before tha
t restore was performed?
If not, I fail to see anyway to get anything back. Unelss you have some type
of backup which is more
recent that the 2 month old backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Support" <Support@.discussions.microsoft.com> wrote in message
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that ha
d
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database
and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss something
.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup o
n
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not y
et
> extreamly familiar with SQL server 2000. By the way we have been working
on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance eithe
r
> way.|||> That restore overwrites everything inside the database. Including the
entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
Like I said I am not very experienced with SQL. Please forgive my ignorance
in this matter. We were not backing up the Transaction log, just the
database. When the restore was done it wasn't restoring the transaction log
.
Does it remove the entries from the transaction log when you restore the
database form a backup even if it doesn't restore the transaction log? I
would think that would be the opposite of what the transaction log was
supposed to do.
"Tibor Karaszi" wrote:

> That restore overwrites everything inside the database. Including the entr
ies in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
>
> Are you saying that you saved something, somehow (backup, detach) before t
hat restore was performed?
> If not, I fail to see anyway to get anything back. Unelss you have some ty
pe of backup which is more
> recent that the 2 month old backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Support" <Support@.discussions.microsoft.com> wrote in message
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
>
>|||Hi,
In my opinion, if
1- the DB was in Full recovery mode
and
2- you have have the complete T-log since Nov
then it will work.
But I would be surprised if you meet both requirement
Chris
"Support" <Support@.discussions.microsoft.com> a crit dans le message de
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that
had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database
and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss
something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup
on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not
yet
> extreamly familiar with SQL server 2000. By the way we have been working
on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance
either
> way.|||>> 1- the DB was in Full recovery mode
Sorry about this. Like I said, I am not very experienced with SQL. I had
NOT been backing up the transaction log, so I would assume the transaction
log would still have all of that data in it. Does it remove data from the
transaction log when you restore a database but not the transaction log? As
far as the "Full recovery mode" goes, do you mean backup the entire database
every time. We do backup the entire database as opposed to just the changes
,
but not the transaction logs. Is this what you are referring to, or is it a
setting on the restore?
"Chris" wrote:

> Hi,
> In my opinion, if
> 1- the DB was in Full recovery mode
> and
> 2- you have have the complete T-log since Nov
> then it will work.
> But I would be surprised if you meet both requirement
> Chris
>
> "Support" <Support@.discussions.microsoft.com> a écrit dans le message de
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> had
> and
> something.
> on
> yet
> on
> either
>
>|||Hi - I suggest you read the Books Online topics on recovery models
('Selecting a Recovery Model' is a good start) as that will help you
understand the issues and alternatives you have depending on what recovery
model you're using.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in
message news:BABCE850-09CC-4766-A370-71360F7C9C8C@.microsoft.com...
> Sorry about this. Like I said, I am not very experienced with SQL. I had
> NOT been backing up the transaction log, so I would assume the transaction
> log would still have all of that data in it. Does it remove data from the
> transaction log when you restore a database but not the transaction log?
As
> far as the "Full recovery mode" goes, do you mean backup the entire
database
> every time. We do backup the entire database as opposed to just the
changes,
> but not the transaction logs. Is this what you are referring to, or is it
a[vbcol=seagreen]
> setting on the restore?
> "Chris" wrote:
>
backup[vbcol=seagreen]
that[vbcol=seagreen]
31.[vbcol=seagreen]
data in[vbcol=seagreen]
database[vbcol=seagreen]
backup[vbcol=seagreen]
quit[vbcol=seagreen]
not[vbcol=seagreen]
working[vbcol=seagreen]
it[vbcol=seagreen]|||The transaction log is there to support transactional consistency (rollbacks
and recovery) as well
as supporting "incremental" backup (backup of the transaction log).
A database backup contains both data pages as well as the necessary log reco
rds (from the
transaction log file(s)). This is so that SQL Server when you restore can ap
ply the data pages and
perform recovery (roll -forward and -back). When your restore a database bac
kup, the contents of the
ldf file of the current database will be overwritten. I agree with Paul R re
garding reading up on
the suggested section on Books Online. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in
message
news:12970127-DB33-4D7B-ADE7-7136BD17E4F8@.microsoft.com...[vbcol=seagreen]
> entries in the transaction
> Like I said I am not very experienced with SQL. Please forgive my ignoran
ce
> in this matter. We were not backing up the Transaction log, just the
> database. When the restore was done it wasn't restoring the transaction l
og.
> Does it remove the entries from the transaction log when you restore the
> database form a backup even if it doesn't restore the transaction log? I
> would think that would be the opposite of what the transaction log was
> supposed to do.
>
> "Tibor Karaszi" wrote:
>