Accidently delete records

Hello All,
I'm need help!
I have accidently deleted all record from a table. I don't have a backup at the time I deleted. Could you all tell me if there's a way to recover those records?
Thanks.
David
You may be able to use your transaction logs to do a point in time restore,
I'm not sure, but there is hope.
"David" <David@.discussions.microsoft.com> wrote in message
news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> Hello All,
> I'm need help!
> I have accidently deleted all record from a table. I don't have a backup
at the time I deleted. Could you all tell me if there's a way to recover
those records?
> Thanks.
> David
|||Lyle,
How do I do that? Could you please share some hint?
"Lyle Spencer" wrote:

> You may be able to use your transaction logs to do a point in time restore,
> I'm not sure, but there is hope.
> "David" <David@.discussions.microsoft.com> wrote in message
> news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> at the time I deleted. Could you all tell me if there's a way to recover
> those records?
>
>
|||YOu will need to take your full backups and apply the
trans logs up to the point in time you deleted the table
and you can then retrieve the records.
Here's an example from BOL
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
[vbcol=seagreen]
>--Original Message--
>Lyle,
>How do I do that? Could you please share some hint?
>
>"Lyle Spencer" wrote:
point in time restore,[vbcol=seagreen]
message[vbcol=seagreen]
69D4AA671891@.microsoft.com...[vbcol=seagreen]
don't have a backup[vbcol=seagreen]
there's a way to recover
>.
>
|||Hmmm... My problem is that I've never done a backup for that database.
"anonymous@.discussions.microsoft.com" wrote:

> YOu will need to take your full backups and apply the
> trans logs up to the point in time you deleted the table
> and you can then retrieve the records.
> Here's an example from BOL
> -- Restore the database backup.
> RESTORE DATABASE MyNwind
> FROM MyNwind_1, MyNwind_2
> WITH NORECOVERY
> GO
> RESTORE LOG MyNwind
> FROM MyNwind_log1
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> GO
> RESTORE LOG MyNwind
> FROM MyNwind_log2
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> point in time restore,
> message
> 69D4AA671891@.microsoft.com...
> don't have a backup
> there's a way to recover
>
|||Hi,
This will work only if you have the database in FULL recovery model, must
have a FULL database Backup ,
all the subsequent trasnaction log backup files after the full backup.
I feel that in your case you cant revert back with out a backup.
Note:-
Based on the criticality of data please choose a recovery model and define
necessory backup procedures
for your SQL Server databases.
Thanks
Hari
MCDBA
"David" <David@.discussions.microsoft.com> wrote in message
news:EEEAE0E7-BA74-40BF-BC32-840649B457DA@.microsoft.com...[vbcol=seagreen]
> Hmmm... My problem is that I've never done a backup for that database.
>
> "anonymous@.discussions.microsoft.com" wrote:
|||> Hmmm... My problem is that I've never done a backup for that database.
Then you must not consider the data very important!
http://www.aspfaq.com/2449
|||I've never use Lumigent, but they claim to go through the internal free list
(as well as syslogs) to recover what might never be recoverable otherwise.
"David" <David@.discussions.microsoft.com> wrote in message
news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> Hello All,
> I'm need help!
> I have accidently deleted all record from a table. I don't have a backup
at the time I deleted. Could you all tell me if there's a way to recover
those records?
> Thanks.
> David
|||Hi David,
Saw your reply and try download the Log P.I software and this is realyl good.
The web site is www.logpi.com You can restore the deleted transactions, but make sure you follow the steps they have mentioned.
Regards
RK.
"David" wrote:

> Hello All,
> I'm need help!
> I have accidently deleted all record from a table. I don't have a backup at the time I deleted. Could you all tell me if there's a way to recover those records?
> Thanks.
> David

Accidently delete records

Hello All,
I'm need help!
I have accidently deleted all record from a table. I don't have a backup at
the time I deleted. Could you all tell me if there's a way to recover those
records?
Thanks.
DavidYou may be able to use your transaction logs to do a point in time restore,
I'm not sure, but there is hope.
"David" <David@.discussions.microsoft.com> wrote in message
news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> Hello All,
> I'm need help!
> I have accidently deleted all record from a table. I don't have a backup
at the time I deleted. Could you all tell me if there's a way to recover
those records?
> Thanks.
> David|||Lyle,
How do I do that? Could you please share some hint?
"Lyle Spencer" wrote:

> You may be able to use your transaction logs to do a point in time restore
,
> I'm not sure, but there is hope.
> "David" <David@.discussions.microsoft.com> wrote in message
> news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> at the time I deleted. Could you all tell me if there's a way to recover
> those records?
>
>|||YOu will need to take your full backups and apply the
trans logs up to the point in time you deleted the table
and you can then retrieve the records.
Here's an example from BOL
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

>--Original Message--
>Lyle,
>How do I do that? Could you please share some hint?
>
>"Lyle Spencer" wrote:
>
point in time restore,[vbcol=seagreen]
message[vbcol=seagreen]
69D4AA671891@.microsoft.com...[vbcol=seagreen]
don't have a backup[vbcol=seagreen]
there's a way to recover[vbcol=seagreen]
>.
>|||Hmmm... My problem is that I've never done a backup for that database.
"anonymous@.discussions.microsoft.com" wrote:

> YOu will need to take your full backups and apply the
> trans logs up to the point in time you deleted the table
> and you can then retrieve the records.
> Here's an example from BOL
> -- Restore the database backup.
> RESTORE DATABASE MyNwind
> FROM MyNwind_1, MyNwind_2
> WITH NORECOVERY
> GO
> RESTORE LOG MyNwind
> FROM MyNwind_log1
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> GO
> RESTORE LOG MyNwind
> FROM MyNwind_log2
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
>
> point in time restore,
> message
> 69D4AA671891@.microsoft.com...
> don't have a backup
> there's a way to recover
>|||Hi,
This will work only if you have the database in FULL recovery model, must
have a FULL database Backup ,
all the subsequent trasnaction log backup files after the full backup.
I feel that in your case you cant revert back with out a backup.
Note:-
Based on the criticality of data please choose a recovery model and define
necessory backup procedures
for your SQL Server databases.
Thanks
Hari
MCDBA
"David" <David@.discussions.microsoft.com> wrote in message
news:EEEAE0E7-BA74-40BF-BC32-840649B457DA@.microsoft.com...[vbcol=seagreen]
> Hmmm... My problem is that I've never done a backup for that database.
>
> "anonymous@.discussions.microsoft.com" wrote:
>|||> Hmmm... My problem is that I've never done a backup for that database.
Then you must not consider the data very important!
http://www.aspfaq.com/2449|||I've never use Lumigent, but they claim to go through the internal free list
(as well as syslogs) to recover what might never be recoverable otherwise.
"David" <David@.discussions.microsoft.com> wrote in message
news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> Hello All,
> I'm need help!
> I have accidently deleted all record from a table. I don't have a backup
at the time I deleted. Could you all tell me if there's a way to recover
those records?
> Thanks.
> David|||Hi David,
Saw your reply and try download the Log P.I software and this is realyl good
.
The web site is www.logpi.com You can restore the deleted transactions, but
make sure you follow the steps they have mentioned.
Regards
RK.
"David" wrote:

> Hello All,
> I'm need help!
> I have accidently deleted all record from a table. I don't have a backup a
t the time I deleted. Could you all tell me if there's a way to recover thos
e records?
> Thanks.
> David

Accidently delete records

Hello All,
I'm need help!
I have accidently deleted all record from a table. I don't have a backup at the time I deleted. Could you all tell me if there's a way to recover those records?
Thanks.
DavidYou may be able to use your transaction logs to do a point in time restore,
I'm not sure, but there is hope.
"David" <David@.discussions.microsoft.com> wrote in message
news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> Hello All,
> I'm need help!
> I have accidently deleted all record from a table. I don't have a backup
at the time I deleted. Could you all tell me if there's a way to recover
those records?
> Thanks.
> David|||Lyle,
How do I do that? Could you please share some hint?
"Lyle Spencer" wrote:
> You may be able to use your transaction logs to do a point in time restore,
> I'm not sure, but there is hope.
> "David" <David@.discussions.microsoft.com> wrote in message
> news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> > Hello All,
> >
> > I'm need help!
> > I have accidently deleted all record from a table. I don't have a backup
> at the time I deleted. Could you all tell me if there's a way to recover
> those records?
> >
> > Thanks.
> > David
>
>|||YOu will need to take your full backups and apply the
trans logs up to the point in time you deleted the table
and you can then retrieve the records.
Here's an example from BOL
-- Restore the database backup.
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
>--Original Message--
>Lyle,
>How do I do that? Could you please share some hint?
>
>"Lyle Spencer" wrote:
>> You may be able to use your transaction logs to do a
point in time restore,
>> I'm not sure, but there is hope.
>> "David" <David@.discussions.microsoft.com> wrote in
message
>> news:37E4E743-B412-4BF9-BFE2-
69D4AA671891@.microsoft.com...
>> > Hello All,
>> >
>> > I'm need help!
>> > I have accidently deleted all record from a table. I
don't have a backup
>> at the time I deleted. Could you all tell me if
there's a way to recover
>> those records?
>> >
>> > Thanks.
>> > David
>>
>.
>|||Hmmm... My problem is that I've never done a backup for that database.
"anonymous@.discussions.microsoft.com" wrote:
> YOu will need to take your full backups and apply the
> trans logs up to the point in time you deleted the table
> and you can then retrieve the records.
> Here's an example from BOL
> -- Restore the database backup.
> RESTORE DATABASE MyNwind
> FROM MyNwind_1, MyNwind_2
> WITH NORECOVERY
> GO
> RESTORE LOG MyNwind
> FROM MyNwind_log1
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> GO
> RESTORE LOG MyNwind
> FROM MyNwind_log2
> WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> >--Original Message--
> >Lyle,
> >
> >How do I do that? Could you please share some hint?
> >
> >
> >"Lyle Spencer" wrote:
> >
> >> You may be able to use your transaction logs to do a
> point in time restore,
> >> I'm not sure, but there is hope.
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in
> message
> >> news:37E4E743-B412-4BF9-BFE2-
> 69D4AA671891@.microsoft.com...
> >> > Hello All,
> >> >
> >> > I'm need help!
> >> > I have accidently deleted all record from a table. I
> don't have a backup
> >> at the time I deleted. Could you all tell me if
> there's a way to recover
> >> those records?
> >> >
> >> > Thanks.
> >> > David
> >>
> >>
> >>
> >.
> >
>|||Hi,
This will work only if you have the database in FULL recovery model, must
have a FULL database Backup ,
all the subsequent trasnaction log backup files after the full backup.
I feel that in your case you cant revert back with out a backup.
Note:-
Based on the criticality of data please choose a recovery model and define
necessory backup procedures
for your SQL Server databases.
Thanks
Hari
MCDBA
"David" <David@.discussions.microsoft.com> wrote in message
news:EEEAE0E7-BA74-40BF-BC32-840649B457DA@.microsoft.com...
> Hmmm... My problem is that I've never done a backup for that database.
>
> "anonymous@.discussions.microsoft.com" wrote:
> > YOu will need to take your full backups and apply the
> > trans logs up to the point in time you deleted the table
> > and you can then retrieve the records.
> >
> > Here's an example from BOL
> > -- Restore the database backup.
> > RESTORE DATABASE MyNwind
> > FROM MyNwind_1, MyNwind_2
> > WITH NORECOVERY
> > GO
> > RESTORE LOG MyNwind
> > FROM MyNwind_log1
> > WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> > GO
> > RESTORE LOG MyNwind
> > FROM MyNwind_log2
> > WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
> >
> > >--Original Message--
> > >Lyle,
> > >
> > >How do I do that? Could you please share some hint?
> > >
> > >
> > >"Lyle Spencer" wrote:
> > >
> > >> You may be able to use your transaction logs to do a
> > point in time restore,
> > >> I'm not sure, but there is hope.
> > >>
> > >> "David" <David@.discussions.microsoft.com> wrote in
> > message
> > >> news:37E4E743-B412-4BF9-BFE2-
> > 69D4AA671891@.microsoft.com...
> > >> > Hello All,
> > >> >
> > >> > I'm need help!
> > >> > I have accidently deleted all record from a table. I
> > don't have a backup
> > >> at the time I deleted. Could you all tell me if
> > there's a way to recover
> > >> those records?
> > >> >
> > >> > Thanks.
> > >> > David
> > >>
> > >>
> > >>
> > >.
> > >
> >|||> Hmmm... My problem is that I've never done a backup for that database.
Then you must not consider the data very important!
http://www.aspfaq.com/2449|||I've never use Lumigent, but they claim to go through the internal free list
(as well as syslogs) to recover what might never be recoverable otherwise.
"David" <David@.discussions.microsoft.com> wrote in message
news:37E4E743-B412-4BF9-BFE2-69D4AA671891@.microsoft.com...
> Hello All,
> I'm need help!
> I have accidently deleted all record from a table. I don't have a backup
at the time I deleted. Could you all tell me if there's a way to recover
those records?
> Thanks.
> David

Accessing SQL Server temp table for Access Project

Hi,
I am trying to use a temp table as the record source for a listbox on a access project form.
I have no problem creating the temp table and inserting data to it, but I can't access it from MS Access (every thing works on Query Analyzer).
I know that local temp tables are deleted when the connection is lost, but I'm on the same form that create the temp table, why can't I access the data??
(It works when I use global temp tables like ##Test, but I can't use global temp tabels for my application)

Here is my code, any idea what the problem might be?

Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset

Dim SQL As String

SQL = "exec sp_dropMListSource "
SQL = SQL & "SELECT distinct dbo.tblContact.ContactID,ISNULL(dbo.tblContact.Fir stName, '') "
SQL = SQL & "+ ' ' + ISNULL(dbo.tblContact.LastName, '') AS [Contact Name]"
SQL = SQL & "INTO #MListSource "
SQL = SQL & " FROM dbo.tblContact INNER JOIN dbo.tblProperty ON "
SQL = SQL & "dbo.tblContact.ContactID = dbo.tblProperty.ContactID "
SQL = SQL & "WHERE dbo.tblProperty.Zip in (" & Me!zips & ")Order By [Contact Name]"

Rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Me.MListSource.RowSource = "Select ContactID, [Contact Name] From #MListSource"

Set Rs = Nothing>> I know that local temp tables are deleted when the connection is lost
Nope - it is dropped when the batch completes.
In your case the batch is the create statement.|||nigelrivett Not exactly try the following, works just fine.

create table #Tmp(f1 int)
go
insert into #Tmp values(1)
go
select * from #Tmp
go

Sia Okay, this is just a guess but what happens if you run profiler as you step through your code? I am thinking that the temp table is alive until you execute the "Set Rs = Nothing". I think this implicitly (sp?) closes the connection and as a result your temp table goes away.

Here is a blerb from BOL:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Can you run your code in debug and check this out?|||I think that Nigel is right. Once the batch completes the local temp table is dropped. If you want to return the entries from your temp it would be better to put all the SQL statements in a SP that returns your recordset (use ADODB command object)

Originally posted by Paul Young
nigelrivett Not exactly try the following, works just fine.

create table #Tmp(f1 int)
go
insert into #Tmp values(1)
go
select * from #Tmp
go

Sia Okay, this is just a guess but what happens if you run profiler as you step through your code? I am thinking that the temp table is alive until you execute the "Set Rs = Nothing". I think this implicitly (sp?) closes the connection and as a result your temp table goes away.

Here is a blerb from BOL:

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Can you run your code in debug and check this out?|||The problem is that the local temp table is accessible only through the SP and not from outside, however the global temp table is accissble from outside.|||Unless a temporary table is created within a stored procedure it persists until it is explicitly dropped or it's connection ends.

The problem is that your code is using a second connection to populate the list box than the one that created the table. If you have an adp project, you shouldn't have to make a new connection or even create a temporary table. Just set your listbox's source directly, and remember to requery it to show the results:

SQL = SQL & "SELECT distinct dbo.tblContact.ContactID,ISNULL(dbo.tblContact.Fir stName, '') "
SQL = SQL & "+ ' ' + ISNULL(dbo.tblContact.LastName, '') AS [Contact Name]"
SQL = SQL & " FROM dbo.tblContact INNER JOIN dbo.tblProperty ON "
SQL = SQL & "dbo.tblContact.ContactID = dbo.tblProperty.ContactID "
SQL = SQL & "WHERE dbo.tblProperty.Zip in (" & Me!zips & ")Order By [Contact Name]"

Me.MListSource.RowSource = SQL
Me.MListSource.Requery

blindman

Accessing Second Record Within Query

Hello,
I'm attempting to use a query to add up the charges in a record from a
table and two corresponding records in another table and then return a
result if there is a difference between the known total and these
calculations. My query below works fine for adding up charges from a
record in the H_MAIN table and one record from the H_Main_Extra table.
What would I add into this query to access the second corresponding
record in
the H_Main_Extra table?
Thanks in advance for any advice.
Jon
SELECT [TotalSub].Tif, [TotalSub].c1, [TotalSub].c2, [TotalSub].c3,
[TotalSub].c4, [TotalSub].CalcTotal, [TotalSub].ExtractedTotal,
[TotalSub].AmtDiff FROM (SELECT H_MAIN.Tif, H_MAIN.Changes,
IIf(IsNull(H_MAIN.[24f_Charges1]),"0",H_MAIN.[24f_Charges1]) AS c1,
IIf(IsNull(H_MAIN.[24f_Charges2]),"0",H_MAIN.[24f_Charges2]) AS c2,
IIf(IsNull(H_MAIN_Extra.[24f_Charges1]),"0",H_MAIN_Extra.[24f_Charges1])
AS c3,
IIf(IsNull(H_MAIN_Extra.[24f_Charges2]),"0",H_MAIN_Extra.[24f_Charges2])
AS c4,Val([c1])+Val([c2])+Val([c3])+Val([c4
]) AS CalcTotal,
IIf(IsNull(H_MAIN.[28_Total_Charges]),"0",Val(H_MAIN.[28_Total_Charges]))
AS ExtractedTotal, Abs([CalcTotal]-[ExtractedTotal]) AS AmtDiff
FROM H_MAIN LEFT JOIN H_Main_Extra ON H_MAIN.Id =
H_Main_Extra.ParentId) AS TotalSub WHERE ((([TotalSub].[AmtDiff])>0));On 5 Dec 2005 12:12:51 -0800, jon.tjemsland@.gmail.com wrote:

>Hello,
>I'm attempting to use a query to add up the charges in a record from a
>table and two corresponding records in another table and then return a
>result if there is a difference between the known total and these
>calculations. My query below works fine for adding up charges from a
>record in the H_MAIN table and one record from the H_Main_Extra table.
>What would I add into this query to access the second corresponding
>record in
>the H_Main_Extra table?
>Thanks in advance for any advice.
Hi Jon,
The code you posted uses the IIf function. This function does not exist
in SQL Server. I suspect that you're using Access. If I'm right, then
you'll probably get better help in an Access group. (This group is for
SQL Server).
Your question is also not completely clear. You say you want to add a
"second corresponding record", but you don't indicate how I would know
which rows are "corresponding". And if there are more than 2, then how
to determine which are first and seceond?
If the IIf's were an error and this question does concern SQL Server,
then please check www.aspfaq.com/5006 to get more information on the
details you need to include in your post to enable us to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Accessing Second Record Set

hello all,
i had two stored procedures,where the 1st SP is calling 2nd SP(which returns 2 record sets).
how can i access the second recordset from the 1st SP
it would a great help if anyone help me out!!
thanx in advance,
ravi.WHY? I mean, you could rig up some hackneyed process that loads the data into a temp table and parses out the recordset you want, or whatever, but there is just never a good reason for building applications this way. One stored procedure should return one recordset. One function should return one recordset.|||I agree with Blindman 100% on this issue. Stored procedures can return more than one result set. This is to allow for nested procedures that each return their own result sets (like sp_help does). I can't think of any reason that a procedure should be designed to intentionally return more than one result set, other than as a user convenience "wrapper" for a group of other procedures.

-PatP