Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs?
I assume you didn't protect the delete with BEGIN TRAN? If you did, just do ROLLBACK.
Are you performing regular transaction log backups? Is database in full or bulk logged recovery
mode? If answers to these questions are "no", you will probably need to go to your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs?

Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs'I assume you didn't protect the delete with BEGIN TRAN? If you did, just do
ROLLBACK.
Are you performing regular transaction log backups? Is database in full or b
ulk logged recovery
mode? If answers to these questions are "no", you will probably need to go t
o your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in
better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'

Recovery from Delete statement using the transaction logs

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs'I assume you didn't protect the delete with BEGIN TRAN? If you did, just do ROLLBACK.
Are you performing regular transaction log backups? Is database in full or bulk logged recovery
mode? If answers to these questions are "no", you will probably need to go to your most recent
backup or just accept the data loss. If answers are "yes", you *might* be in better shape.
Anyhow, I suggest you read my article on the topic at:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:179b01c51500$f58a6c00$a601280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'

recovery from a delete statement

I ran some delete statements in Query Analyzer and I
found out it was the wrong database. Can you rollback the
delete statements and recover the data from the
transaction logs'Robert wrote:
> I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'
You'll have to use a log reader tool from Lumigent or LogPi. Otherwise,
you need to restore from a backup. If the table is static, you may be
able to restore the database to another (test) server and use DTS to
copy the table.
Some backup/recovery tools like our LiteSpeed product offer object level
recovery.
David Gugick
Imceda Software
www.imceda.com|||Restore a backup to a temporary database, then copy over the missing
data.
Too late now but it's a good idea to bracket your ad-hoc DML statements
with BEGIN TRAN ... ROLLBACK TRAN until you are sure that the
modification is correct. Also a good idea to have a current backup of
course :-)
David Portas
SQL Server MVP
--|||On Thu, 17 Feb 2005 06:55:14 -0800, Robert wrote:

>I ran some delete statements in Query Analyzer and I
>found out it was the wrong database. Can you rollback the
>delete statements and recover the data from the
>transaction logs'
Hi Robert,
You can't rollback after committing the transaction.
Besides the options posted by David and David, the might be one more
option for you: If your database is in full recovery mode, you can use
point in time recovery to restore yoour database to the point in time just
before you ran the unintended deletes. This will of course destroy ALL
modifications since that time, so it may or may not be usefull.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Might not add to the posts already posted, but I have an article about this
scenario at
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <robert@.expressprogramming.com> wrote in message
news:294701c51500$b01e3480$a501280a@.phx.gbl...
>I ran some delete statements in Query Analyzer and I
> found out it was the wrong database. Can you rollback the
> delete statements and recover the data from the
> transaction logs'

Recovering from Deadlocks: retrying the query

I'm having some deadlocking issues in my ASP.Net 2.0 web application with SQL Server 2000. One solution I've come across involves catching the the deadlock error (at the application level) and then retrying the query again, in the hopes that whatever was causing the deadlock is no longer locked.

I've tried to implement such a solution, where I catch the deadlock exception, then try to execute the same query again. Only now I'm getting a "The transaction is in doubt" exception.

So am I going about this wrong? Am I supposed to retry the entire transaction, or is valid to just try to redo the one query that it failed on? Is there some way around the transaction being in doubt?

You should determine the cause of the deadlock and correct your SQL code rather than retry mechanism. Retry mechanism can be complicated depending on what you are doing, whether you are in a transaction and so on. Deadlock typically means that you have access paths in your code that is not optimized and different for similar operations. Check out the following links on how to troubleshoot deadlocks:

http://msdn2.microsoft.com/en-us/library/ms177433.aspx

http://msdn2.microsoft.com/en-us/library/ms188246.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8i93.asp

sql

Recover the data

Hi,
My appln runs on a remote server. A few minutes ago i accidently ran a
'Truncate table' command. Is there anyway to recover it thru Query Analyzer.
The DB Recovery model is SIMPLE and i have the dbOwner permission.
Thanking in Advance
LaraNo. Restore from the latest database backup or re-load the data. You can try
any of the log reading
tools (I've listed some on my links page), but the data is most probably not
in the log anymore
because of simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lara" <lara169@.gmail.com> wrote in message news:%23Pp%23vE$9FHA.3952@.TK2MSFTNGP09.phx.gbl.
.
> Hi,
> My appln runs on a remote server. A few minutes ago i accidently ran a 'Tr
uncate table' command.
> Is there anyway to recover it thru Query Analyzer. The DB Recovery model i
s SIMPLE and i have the
> dbOwner permission.
> Thanking in Advance
> Lara
>|||Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uqaa3U$9FHA.1996@.TK2MSFTNGP10.phx.gbl...
> No. Restore from the latest database backup or re-load the data. You can
> try any of the log reading tools (I've listed some on my links page), but
> the data is most probably not in the log anymore because of simple
> recovery mode.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Lara" <lara169@.gmail.com> wrote in message
> news:%23Pp%23vE$9FHA.3952@.TK2MSFTNGP09.phx.gbl...
>

recover deleted data

I want to learn that if my query deleted all data in the table by mistake, is it possible recovery and how?

Hi Sinem,

If you did taht in a transaction:

BEGIN TRANSACTION
<DELETQuery>
--YOu Can do a ROLLBACK Here

If not or you commited it′, you need a backup to restore. If you have a Full backup and kept the transaction log backups you can do a Point in time recovery to the second where you deleted it. No backup, no data.

HTH, Jens Suessmeyer.

Recover data from Database Transaction Log

Hi Everyone
I have a problem with one of our databases. One of our developers run a
query on a live database which resulted in the deletion of several records
in a group of tables. No database Backup is available for the data erased
and so comes my question, is there any way of rolling back those
transactions from the database logs?
Thanks in advance for your attentionJohn Grisham wrote:
> Hi Everyone
> I have a problem with one of our databases. One of our developers run a
> query on a live database which resulted in the deletion of several records
> in a group of tables. No database Backup is available for the data erased
> and so comes my question, is there any way of rolling back those
> transactions from the database logs?
> Thanks in advance for your attention
>
What recovery model?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The idea is to recover the lost data in any way from a detached database,
and later re introduce the recovered data in the live database. The erased
records correspond to tariff data fro some products and offer variations
during the whole year 2006.
Thanks in advance...
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD3FF2.1060900@.realsqlguy.com...
> John Grisham wrote:
>> Hi Everyone
>> I have a problem with one of our databases. One of our developers run a
>> query on a live database which resulted in the deletion of several
>> records in a group of tables. No database Backup is available for the
>> data erased and so comes my question, is there any way of rolling back
>> those transactions from the database logs?
>> Thanks in advance for your attention
> What recovery model?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John Grisham wrote:
> The idea is to recover the lost data in any way from a detached database,
> and later re introduce the recovered data in the live database. The erased
> records correspond to tariff data fro some products and offer variations
> during the whole year 2006.
>
Again, what recovery model? Simple, Bulk-Logged, or Full?
If Full, you should be able to take a backup of the database, followed
by a log backup, then use the STOPAT clause of the RESTORE command to
restore the database and log to a point just prior to the deletion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The recovery model is set to simple.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD4361.6090506@.realsqlguy.com...
> John Grisham wrote:
>> The idea is to recover the lost data in any way from a detached
>> database, and later re introduce the recovered data in the live database.
>> The erased records correspond to tariff data fro some products and offer
>> variations during the whole year 2006.
> Again, what recovery model? Simple, Bulk-Logged, or Full?
> If Full, you should be able to take a backup of the database, followed by
> a log backup, then use the STOPAT clause of the RESTORE command to restore
> the database and log to a point just prior to the deletion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John Grisham wrote:
> The recovery model is set to simple.
>
Not much you can do then... Backup, backup, backup, it can't be said
enough times... You really should read up on the various recovery
models, and understand how they can benefit you.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ok, thanks for your attention and kind advice
Regards
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE2968.9070106@.realsqlguy.com...
> John Grisham wrote:
>> The recovery model is set to simple.
> Not much you can do then... Backup, backup, backup, it can't be said
> enough times... You really should read up on the various recovery models,
> and understand how they can benefit you.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

RecordSelectionFormula not appearing in the Query

I am using Crystal Reports 7 with VB6.
I am sending some user defined parameters from the application to filter the records and its getting set to the CRPEAuto.Report.RecordSelectionFormula, along with a hardcoded condition in the Report-Edit Selection Formula at design time. The design time and runtime parameter values are getting concatenated properly with the required relational operator.

1.
But the method CRPEAuto.Report.SQLQueryString, retrieves the query of the report(Show SQL Query), without the condition set in the RecordSelectionFormula above.

2.
The query returned above, when executed in the SQL Navigator, runs in 6mins and records are fetched. But the report preview from the application which displays the records on Crystal Report runs indefinetly and no output is got.

Any inputs on this would be appreciated.
Many Thanks.You can use stored procedure with input parameters
Did the query returned is valid?

RecordSelectionFormula

hi , i am newbie in crstal report,
I had face a problem when i try to set the formula for selection.

I hope to query record that inv_date and c_date bigger than certain date.
so i set the formula like below:

r.RecordSelectionFormula =
"{ViewReportTraking.inv_date} >= DateValue('" & datefrom.ToString() & "') or {ViewReportTraking.c_date} >= DateValue('" & datefrom.ToString() & "')"

What happen is the query only select the record inv_date >= certainDate,
but not the c_date >=certainDate.

when i try to set it to opposite
r.RecordSelectionFormula =
"{ViewReportTraking.c_date} >= DateValue('" & datefrom.ToString() & "') or {ViewReportTraking.inv_date} >= DateValue('" & datefrom.ToString() & "')"

I need query either inv_date or c_date >= certainDate.Are you wanting to retrieve records back where both the inv_Date and c_date are >= your "certain date"? If so change your "or" to "and", then you will get back records where both conditions are true.

GJ|||No, i need either inv_date > certainDate or c_date > certainDate is true then query record.
but the 'or' look like not function, but the 'and' is function.

records wont delete

I have some records that will not delete, whenever I run a delete
statement in the Query analyzer, it never completes the statement, and
I am only deleting one record at a time. Can anyone tell me why a
record wouldn't delete?[posted and mailed, please reply in news]

alloowishus (alloowishus@.yahoo.com) writes:
> I have some records that will not delete, whenever I run a delete
> statement in the Query analyzer, it never completes the statement, and
> I am only deleting one record at a time. Can anyone tell me why a
> record wouldn't delete?

There are several possible reasons:

o The row you are trying to delete is locked by another process.
o There is a foreign-key constraint from a referencing table, and that
table is large, but the referencing column is not indexed, so
checking whether the row is deletable takes a very long time.
o A variation of the above, but the foreign-key relationship uses
cascading updates.
o The table has a DELETE trigger which takes a very long time to
execute.

To check for locking, issue an sp_who from another connection. If you see
a non-zero value in the Blk column, the process on that row is blocked by
the process in the Blk column.

To check the other, use sp_helpconstraint and sp_helptrigger to look
for constraints and triggers.

The above list is not necessarily exhaustive.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Records Duplicating after a Join

I have two tables as follows:
Orders (ORDERID, CODE, ORDERQTY)
and
STOCK (CODE, LOCATION, STOCKQTY).
I am running a query that will display all the records in the Orders table,
and also a valid location for that code i.e. Fields returned are:
ORDERID, CODE, ORDERQTY, LOCATION
I am using an INNER JOIN to get the result.
This is fine if there is only one location per code but if I have multiple
locations for a code then the result set will show all locations (therefore
duplicating rows).
How do I just display one location code for each record in the orders table?
I dont mind which location code it is - I just need to avoid having multiple
records returned!
Thanks,
Wez
On Fri, 17 Jun 2005 10:07:07 -0700, Wez wrote:

>I have two tables as follows:
>Orders (ORDERID, CODE, ORDERQTY)
>and
>STOCK (CODE, LOCATION, STOCKQTY).
>I am running a query that will display all the records in the Orders table,
>and also a valid location for that code i.e. Fields returned are:
>ORDERID, CODE, ORDERQTY, LOCATION
>I am using an INNER JOIN to get the result.
>This is fine if there is only one location per code but if I have multiple
>locations for a code then the result set will show all locations (therefore
>duplicating rows).
>How do I just display one location code for each record in the orders table?
>I dont mind which location code it is - I just need to avoid having multiple
>records returned!
Hi Wez,
In the future, please include table structure (as CREATE TABLE
statements), some sample data (as INSERT statements) and expected output
when asking for help - it makes helping you a lot easier if I don't have
to guess! See www.aspfaq.com/5006 for more details.
Anyway, try if this helps:
SELECT o.OrderId, o.Code, o.OrderQty,
MIN(s.Location) AS Location
FROM Orders AS o
INNER JOIN Stock AS s
ON s.Code = o.Code
GROUP BY o.OrderId, o.Code, o.OrderQty
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

recording query

Hello,

How can I record certain type of querys ? I would like to record
delete command sent to a specific database, and using a specific
login account.

This "query capture" should run in background because I dont know
the exact time someone will delete a record.

Im using SQL-Server 7.0

Thank you,

Eduardo.eakinto@.buscape.com.br (Eduardo) wrote in message news:<693f7309.0404071351.dc027a@.posting.google.com>...
> Hello,
> How can I record certain type of querys ? I would like to record
> delete command sent to a specific database, and using a specific
> login account.
> This "query capture" should run in background because I dont know
> the exact time someone will delete a record.
> Im using SQL-Server 7.0
> Thank you,
> Eduardo.

Profiler would be the first place to look, or perhaps a trace.

Simon

RecordCount

I've got to count the rocrds of this query (SELECT DISTINCT lm.idlm, lm.descrizione FROM lm, grf, saf, cfc WHERE cfc.idlm = lm.idlm AND cfc.idsaf = saf.idsaf AND cfc.idgrf = grf.idgrf) but i can't use the function RecordCount of Visual Basic... how can I do?
Please Help ME!why can't you use the recordcount property? be more specific.|||in order to use recordcount, you have to use the proper (client-side)cursor. I dont remember what it is right off..
use an expensive one then work your way down ;)

recordcount

I've got to count the rocrds of this query (SELECT DISTINCT lm.idlm, lm.descrizione FROM lm, grf, saf, cfc WHERE cfc.idlm = lm.idlm AND cfc.idsaf = saf.idsaf AND cfc.idgrf = grf.idgrf) but i can't use the function RecordCount of Visual Basic... how can I do?
Please Help ME!Hello,

whats about

SELECT COUNT(DISTINCT lm.idlm, lm.descrizione)
FROM lm, grf, saf, cfc WHERE cfc.idlm = lm.idlm AND cfc.idsaf = saf.idsaf AND cfc.idgrf = grf.idgrf


?

Hope that helps ?

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

record sequence?

I add 3 records into a table, the sequence is record 1, 2, and 3. When I use
the Query Analyser to query the table, it shows incorrect sequence, sometime
2,1,3 or sometime 3,2,1. Why not 1,2,3?
Thanks.Tables are not ordered, and rows in a table do not have a sequence. If you
want rows returned in a certain order, you have to specify an ORDER BY
clause. Otherwise SQL Server will assume that the order doesn't matter to
you, and will return the rows in whichever order is the fastest. Which order
is actually the fastest can differ from time to time depending on the server
load, the actual data in the table, and whether parts of the table are
already in memory or have to be read from disk.
Jacco Schalkwijk
SQL Server MVP
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:33A73494-D942-4E15-B8A8-BE986898006C@.microsoft.com...
>I add 3 records into a table, the sequence is record 1, 2, and 3. When I
>use
> the Query Analyser to query the table, it shows incorrect sequence,
> sometime
> 2,1,3 or sometime 3,2,1. Why not 1,2,3?
> Thanks.|||Joe
Specify ORDER BY clause and you get an expected result
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:33A73494-D942-4E15-B8A8-BE986898006C@.microsoft.com...
> I add 3 records into a table, the sequence is record 1, 2, and 3. When I
use
> the Query Analyser to query the table, it shows incorrect sequence,
sometime
> 2,1,3 or sometime 3,2,1. Why not 1,2,3?
> Thanks.|||Unless you use clause ORBER BY in a SELECT statement, sql server does not
guarantee any order of the data selected. You can also create a clustered
index by this column if you will order by it frequently.
AMB
"Joe" wrote:

> I add 3 records into a table, the sequence is record 1, 2, and 3. When I u
se
> the Query Analyser to query the table, it shows incorrect sequence, someti
me
> 2,1,3 or sometime 3,2,1. Why not 1,2,3?
> Thanks.|||The rows of a table have no "ordering." If you want an ordered
result, you need to include an ORDER BY clause in the query,
which allows you to order the rows according to information in
the rows.
If you need to preserve the order in which rows are inserted,
an IDENTITY column or a [datetime] column with default of
GETDATE() are possible ways to do this.
Steve Kass
Drew University
Joe wrote:

>I add 3 records into a table, the sequence is record 1, 2, and 3. When I us
e
>the Query Analyser to query the table, it shows incorrect sequence, sometim
e
>2,1,3 or sometime 3,2,1. Why not 1,2,3?
>Thanks.
>|||Rows in a table do not have any sequences associated with them. Irrespective
of whichever sequence you insert them into a table, they are returned in
some arbitrary order. ( This is because, simply put, relational databases
has its partial basis on set theory and sets by themselves do not associate
ordering of their elements. )
If you are interested in returning the rows in some defined sequence, use
the ORDER BY clause in your SELECT statement.
Anith|||Thanks all.
"Jacco Schalkwijk" wrote:

> Tables are not ordered, and rows in a table do not have a sequence. If you
> want rows returned in a certain order, you have to specify an ORDER BY
> clause. Otherwise SQL Server will assume that the order doesn't matter to
> you, and will return the rows in whichever order is the fastest. Which ord
er
> is actually the fastest can differ from time to time depending on the serv
er
> load, the actual data in the table, and whether parts of the table are
> already in memory or have to be read from disk.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:33A73494-D942-4E15-B8A8-BE986898006C@.microsoft.com...
>
>