MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
RegardsNo. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
>> Not sure if this is possible, but a client wants to now if we can use a
>> backup we made on 2/24
>> and then pull transaction out of the database using the daily
>> transaction log backups to get the database to be an effective copy of
>> what it looked like on 2/13/06.
>> We have all the daily transaction logs up to today and the 2/24/06 is
>> the latest backup we have.
>> Any help would be greatly appreciated.
>> Regards
>|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
Regards
No. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
>
|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
RegardsNo. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
>|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

MSSQL 2000 Activity Logs

Does the SQL server logs the information on when a database has ben
deleted, attached, deatached, and so on?
This morning I recived a call from remote site that the aplication won't
connect to the server. It turns out that the user database has disapeared
from the server. I can't see it in the EM, nor I can see the .mdf/.ldf files
on the disk. The SQL server logs (from the EM) just show me that the
database was opened (started) at some time this weekend, and that's it.
Are there more detailed logs, wich I was unable to find?
Mario
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hrHi,
I feel that SQL Server wont store any information of that sort by default.
If you keep enabled the profiler then you can see the command DROP DATABASE
, by using that you can identify the user who has dropped it. But old data
can not be retrived.
Thanks
Hari
MCDBA
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrnc5td57.f2.majk@.fly.srk.fer.hr...
> Does the SQL server logs the information on when a database has ben
> deleted, attached, deatached, and so on?
> This morning I recived a call from remote site that the aplication won't
> connect to the server. It turns out that the user database has disapeared
> from the server. I can't see it in the EM, nor I can see the .mdf/.ldf
files
> on the disk. The SQL server logs (from the EM) just show me that the
> database was opened (started) at some time this weekend, and that's it.
> Are there more detailed logs, wich I was unable to find?
> Mario
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr|||On 2004-03-22, Hari Prasad <hari_prasad_k@.hotmail.com> wrote:
> Hi,
> I feel that SQL Server wont store any information of that sort by default.
> If you keep enabled the profiler then you can see the command DROP DATABAS
E
> , by using that you can identify the user who has dropped it. But old data
> can not be retrived.
>
Yes, when I have the profiler running, then I can see what's going on. But,
most of the time, I don't keep the profiler up.
Is there a way to tell SQL server 'do, please, keep logs of who and when
created/dropped/attached/deatached databases'?
Mike
--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr|||Mario
You can run SQL Server Profile to capture those commands and save it to the
table.
As far as I know with SQL Server 2005 you will be able to define a trigger
on a table or even on a database ( I am not certain) for drop table events.
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrnc5tjfd.p0k.majk@.fly.srk.fer.hr...
> On 2004-03-22, Hari Prasad <hari_prasad_k@.hotmail.com> wrote:
default.
DATABASE
data
> Yes, when I have the profiler running, then I can see what's going on.
But,
> most of the time, I don't keep the profiler up.
> Is there a way to tell SQL server 'do, please, keep logs of who and when
> created/dropped/attached/deatached databases'?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr|||On 2004-03-22, Uri Dimant <urid@.iscar.co.il> wrote:
> Mario
> You can run SQL Server Profile to capture those commands and save it to th
e
> table.
So, if I'm not running the profiler at the moment that actuall event of
droping/deleting the database was taking place, I have no way of seeing what
happened.
The funniest thing is that the missing database was published for
replication, so you can't just delete it from the EM. If you try to delete
the file from the disk (using Windows Explorer, for instance), it will tell
you that the file is in use (because SQL server is using it).
But, SQL server was running all the time (uptime is more than 6 mths), and
the database just dissapeared. No .mdf files found on the disk, no errors
(that I can find) on the SQL server. If I run the NTFS-Undelete utility (i
tried several ones) I can find dozens of deleted files (some were deleted
more than 6 months ago!), but none of the files are my database files.
At this point of time I have no way or knowledge to explain what exactley
happened.
Mike
--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr|||On 2004-03-22, Mario Splivalo <majk@.fly.srk.fer.hr> wrote:
> On 2004-03-22, Uri Dimant <urid@.iscar.co.il> wrote:
>
I browsed the transaction log for the master database, and found that
database was deleted
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr

MSDTS Transaction Failed.

Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
server sp4. We are checking the logs for sql server and observe that
sometimes this error is displayed
Error: 8510, Severity: 20, State: 1
Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
Do you know what could be the reason'
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***This could be one reason for 8510 errors:
SQL Server error 8510 may be reported unnecessarily
http://support.microsoft.com/defaul...kb;en-us;307802
You are probably running into some other MSDTC problem if 8510 are logged to
SQL errorlogs. You should capture a Profiler trace on both linked servers
(with DTCTransaction and all Warnings and Error events) and review them for
other SQL errors leading up to 8510 errors. Also ensure that all connections
executing linked-server queries have SET XACT_ABORT ON.
Adrian
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uYyL0kGRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
> server sp4. We are checking the logs for sql server and observe that
> sometimes this error is displayed
> Error: 8510, Severity: 20, State: 1
> Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
> Do you know what could be the reason'
> Thanks a lot for your help.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***

MSDTS Transaction Failed.

Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
server sp4. We are checking the logs for sql server and observe that
sometimes this error is displayed
Error: 8510, Severity: 20, State: 1
Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
Do you know what could be the reason?
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
This could be one reason for 8510 errors:
SQL Server error 8510 may be reported unnecessarily
http://support.microsoft.com/default...b;en-us;307802
You are probably running into some other MSDTC problem if 8510 are logged to
SQL errorlogs. You should capture a Profiler trace on both linked servers
(with DTCTransaction and all Warnings and Error events) and review them for
other SQL errors leading up to 8510 errors. Also ensure that all connections
executing linked-server queries have SET XACT_ABORT ON.
Adrian
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uYyL0kGRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
> server sp4. We are checking the logs for sql server and observe that
> sometimes this error is displayed
> Error: 8510, Severity: 20, State: 1
> Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
> Do you know what could be the reason?
> Thanks a lot for your help.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***

MSDTS Transaction Failed.

Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
server sp4. We are checking the logs for sql server and observe that
sometimes this error is displayed
Error: 8510, Severity: 20, State: 1
Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
Do you know what could be the reason'
Thanks a lot for your help.
*** Sent via Developersdex http://www.developersdex.com ***This could be one reason for 8510 errors:
SQL Server error 8510 may be reported unnecessarily
http://support.microsoft.com/default.aspx?scid=kb;en-us;307802
You are probably running into some other MSDTC problem if 8510 are logged to
SQL errorlogs. You should capture a Profiler trace on both linked servers
(with DTCTransaction and all Warnings and Error events) and review them for
other SQL errors leading up to 8510 errors. Also ensure that all connections
executing linked-server queries have SET XACT_ABORT ON.
Adrian
"MariaGuzman" <marisa@.devdex.com> wrote in message
news:uYyL0kGRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi, we have sql server 2000 sp3 std edition installed on a windows 2000
> server sp4. We are checking the logs for sql server and observe that
> sometimes this error is displayed
> Error: 8510, Severity: 20, State: 1
> Enlist of MSDTC transaction failed: Result Code = 0x8004d00e..
> Do you know what could be the reason'
> Thanks a lot for your help.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***