Recovering deleted Items

I am sure this has been asked and most likely will be asked again in the
future (but not by me I hope) but when a record is deleted from a table is
it really deleted? Or is it just marked deleted and when the database is
compacted is when it is really flushed? If that is the case is there a way
to query for the deleted records and recover the information? And by the way
Transaction logging was not turned on.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company> And by the way
> Transaction logging was not turned on.
There is no way to turn off transaction logging. Perhaps you meant to say that the database is in
simple recovery mode?
When you DELETE, the row is not physically removed immediately, but the ghost record left on the
page will be removed as soon as SQL Server get time to do so. And there is no facility to get data
back from ghost records. You could try DBCC PAGE, but most probably the ghost record is already gone
when you try the command. So this isn't even worth investigating further for data recovery purposes.
So you are left with the transaction log and your backups. If you are lucky, the log records that
reflect your modifications are still in the transaction log (probably not as you probably are in
simple recovery mode). You can use a log reader to see if you are lucky. I've listed three of the
available tools on my web site.
Or go the restore route.
See http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp for an elaboration.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:eiGec5LIFHA.3628@.TK2MSFTNGP15.phx.gbl...
>I am sure this has been asked and most likely will be asked again in the
> future (but not by me I hope) but when a record is deleted from a table is
> it really deleted? Or is it just marked deleted and when the database is
> compacted is when it is really flushed? If that is the case is there a way
> to query for the deleted records and recover the information? And by the way
> Transaction logging was not turned on.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
>|||In addition to ghost records mentioned, there are also cases when records
are removed from page immediately.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O4Sqb$LIFHA.2704@.tk2msftngp13.phx.gbl...
>> And by the way
>> Transaction logging was not turned on.
> There is no way to turn off transaction logging. Perhaps you meant to say
> that the database is in simple recovery mode?
> When you DELETE, the row is not physically removed immediately, but the
> ghost record left on the page will be removed as soon as SQL Server get
> time to do so. And there is no facility to get data back from ghost
> records. You could try DBCC PAGE, but most probably the ghost record is
> already gone when you try the command. So this isn't even worth
> investigating further for data recovery purposes.
> So you are left with the transaction log and your backups. If you are
> lucky, the log records that reflect your modifications are still in the
> transaction log (probably not as you probably are in simple recovery
> mode). You can use a log reader to see if you are lucky. I've listed three
> of the available tools on my web site.
> Or go the restore route.
> See http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
> for an elaboration.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:eiGec5LIFHA.3628@.TK2MSFTNGP15.phx.gbl...
>>I am sure this has been asked and most likely will be asked again in the
>> future (but not by me I hope) but when a record is deleted from a table
>> is
>> it really deleted? Or is it just marked deleted and when the database is
>> compacted is when it is really flushed? If that is the case is there a
>> way
>> to query for the deleted records and recover the information? And by the
>> way
>> Transaction logging was not turned on.
>> --
>> Andrew C. Madsen
>> Network Specialist
>> Harley-Davidson Motor Company
>>
>sql