Dear sir,
We have a live database, we thought that we backup it everyday. However we
made a terrible mistake today. We did not find any backup file, so our only
hope is on the log file.
Does any one know how can I restore the database based on log file?
The mistake we made is that our programmer runs a update script
"UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
However, our programmer made a mistake on the WHERE clause, so every user is
updated now.
Any help on this?
regards,
Guoqi Zheng
http://www.ureader.comThis is a multi-part message in MIME format.
--090703010304090403040702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
guoqi zheng wrote:
>Dear sir,
>We have a live database, we thought that we backup it everyday. However we
>made a terrible mistake today. We did not find any backup file, so our only
>hope is on the log file.
>Does any one know how can I restore the database based on log file?
>
You cannot. A RESTORE LOG statement must come after a RESTORE DATABASE
... WITH NORECOVERY statement. So if you have no database backup file
and only transaction log backup files, you may as well throw those
transaction log backup files in the bin as they are useless to you.
>The mistake we made is that our programmer runs a update script
>"UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
>However, our programmer made a mistake on the WHERE clause, so every user is
>updated now.
>Any help on this?
>
Got a copy on paper? Are there many rows in this table? You may have
to re-enter them by hand (assuming you know what the old usernames &
passwords were, that is). I did something similar in my humble
beginnings - I updated an insurance policy table to change the broker
for a particular policy but I forgot the WHERE clause (hey, it was when
I was first starting, I was just a lowly programmer), and at that stage
we hadn't set up a proper backup strategy. We had to re-enter all the
correct broker codes for each policy by hand (there were about 4000
policies in the table) - took half a dozen people 2 or 3 days (luckily
we had a policy listing print out that was only a couple days old).
The most important thing I learnt from that embarrassing mistake was to
start each batch with a BEGIN TRAN statement and only COMMIT the
transaction when you've checked the data to make sure it's right
(otherwise do a ROLLBACK).
Good luck.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
--090703010304090403040702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
guoqi zheng wrote:
<blockquote cite="mid2c47e60de8294ace94a284ffc1ea6492@.ureader.com"
type="cite">
<pre wrap="">Dear sir,
We have a live database, we thought that we backup it everyday. However we
made a terrible mistake today. We did not find any backup file, so our only
hope is on the log file.
Does any one know how can I restore the database based on log file?
</pre>
</blockquote>
<tt>You cannot. A RESTORE LOG statement must come after a RESTORE
DATABASE ... WITH NORECOVERY statement. So if you have no database
backup file and only transaction log backup files, you may as well
throw those transaction log backup files in the bin as they are useless
to you.<br>
</tt>
<blockquote cite="mid2c47e60de8294ace94a284ffc1ea6492@.ureader.com"
type="cite">
<pre wrap="">The mistake we made is that our programmer runs a update script
"UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
However, our programmer made a mistake on the WHERE clause, so every user is
updated now.
Any help on this?
</pre>
</blockquote>
<tt>Got a copy on paper? Are there many rows in this table? You may
have to re-enter them by hand (assuming you know what the old usernames
& passwords were, that is). I did something similar in my humble
beginnings - I updated an insurance policy table to change the broker
for a particular policy but I forgot the WHERE clause (hey, it was when
I was first starting, I was just a lowly programmer), and at that stage
we hadn't set up a proper backup strategy. We had to re-enter all the
correct broker codes for each policy by hand (there were about 4000
policies in the table) - took half a dozen people 2 or 3 days (luckily
we had a policy listing print out that was only a couple days old).<br>
<br>
The most important thing I learnt from that embarrassing mistake was to
start each batch with a BEGIN TRAN statement and only COMMIT the
transaction when you've checked the data to make sure it's right
(otherwise do a ROLLBACK).<br>
<br>
Good luck.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
</body>
</html>
--090703010304090403040702--|||"guoqi zheng" wrote:
> Dear sir,
> We have a live database, we thought that we backup it everyday. However we
> made a terrible mistake today. We did not find any backup file, so our only
> hope is on the log file.
> Does any one know how can I restore the database based on log file?
> The mistake we made is that our programmer runs a update script
> "UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
> However, our programmer made a mistake on the WHERE clause, so every user is
> updated now.
> Any help on this?
> regards,
> Guoqi Zheng
> http://www.ureader.com
>
You should use LogExplorer .This tool scan log files and you can rollback
your operation.Don't worry,it's easy.|||A quick search a few products on the net is named "Log Explorer", could you
clarify which one you're talking about?
"luyan" <luyan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
> You should use LogExplorer .This tool scan log files and you can rollback
> your operation.Don't worry,it's easy.|||Lumigent Log Explorer
--
HTH. Ryan
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:OOzCgl0GGHA.984@.tk2msftngp13.phx.gbl...
>A quick search a few products on the net is named "Log Explorer", could you
>clarify which one you're talking about?
> "luyan" <luyan@.discussions.microsoft.com>
> ¼¶¼g©ó¶l¥ó·s»D:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
>> You should use LogExplorer .This tool scan log files and you can rollback
>> your operation.Don't worry,it's easy.
>|||Just curious how this works. The transaction file ought to (I'm not sure)
save the steps of changes made to the database only. Is it supposed to store
the origional value of the UPDATE action? And store the whole set of records
when perform delete?
"luyan" <luyan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
>
> "guoqi zheng" wrote:
>> Dear sir,
>> We have a live database, we thought that we backup it everyday. However
>> we
>> made a terrible mistake today. We did not find any backup file, so our
>> only
>> hope is on the log file.
>> Does any one know how can I restore the database based on log file?
>> The mistake we made is that our programmer runs a update script
>> "UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
>> However, our programmer made a mistake on the WHERE clause, so every user
>> is
>> updated now.
>> Any help on this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>
> You should use LogExplorer .This tool scan log files and you can rollback
> your operation.Don't worry,it's easy.|||Note that if the database is in simple recovery mode or if no database backup has ever been
produced, the log file is un "auto-truncate mode" meaning that the log records necessary to undo the
operations might not exist in the ldf file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
>
> "guoqi zheng" wrote:
>> Dear sir,
>> We have a live database, we thought that we backup it everyday. However we
>> made a terrible mistake today. We did not find any backup file, so our only
>> hope is on the log file.
>> Does any one know how can I restore the database based on log file?
>> The mistake we made is that our programmer runs a update script
>> "UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
>> However, our programmer made a mistake on the WHERE clause, so every user is
>> updated now.
>> Any help on this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>
> You should use LogExplorer .This tool scan log files and you can rollback
> your operation.Don't worry,it's easy.|||That's a very good point, before going down the Log Explorer route what does
the following give you :-
SELECT DATABASEPROPERTYEX('YourDbNameHere', 'Recovery')
If it returns SIMPLE there is no log for the Lumigent tools to work with...
--
HTH. Ryan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23$y2$U1GGHA.3728@.tk2msftngp13.phx.gbl...
> Note that if the database is in simple recovery mode or if no database
> backup has ever been produced, the log file is un "auto-truncate mode"
> meaning that the log records necessary to undo the operations might not
> exist in the ldf file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "luyan" <luyan@.discussions.microsoft.com> wrote in message
> news:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
>>
>> "guoqi zheng" wrote:
>> Dear sir,
>> We have a live database, we thought that we backup it everyday. However
>> we
>> made a terrible mistake today. We did not find any backup file, so our
>> only
>> hope is on the log file.
>> Does any one know how can I restore the database based on log file?
>> The mistake we made is that our programmer runs a update script
>> "UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId =>> xxx"
>> However, our programmer made a mistake on the WHERE clause, so every
>> user is
>> updated now.
>> Any help on this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>>
>> You should use LogExplorer .This tool scan log files and you can rollback
>> your operation.Don't worry,it's easy.
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment