MS SQL Server - Restoring records in a particular table

Author

Restoring records in a particular table

Srik

2006-10-24, 6:37 pm

Hi,

I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records...

Thanks in advance...

Warren Brunk

2006-10-24, 6:37 pm

Did you take a backup prior to doing the delete?

Last night or something?

You could restore it with a different DB name and import the records...

thanks,

--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/

"Srik" <Srik@discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA- BBA62368148E@microso
ft.com...
> Hi,
>
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
>
> Thanks in advance...

Hilary Cotter

2006-10-24, 6:37 pm

If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.

You can also look at tools like Log Explorer which may help.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"Srik" <Srik@discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA- BBA62368148E@microso
ft.com...
> Hi,
>
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
>
> Thanks in advance...

Srik

2006-10-24, 6:37 pm

There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.

See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'

"Warren Brunk" wrote:

> Did you take a backup prior to doing the delete?
>
> Last night or something?
>
> You could restore it with a different DB name and import the records...
>
> thanks,
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
>
> "Srik" <Srik@discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA- BBA62368148E@microso
ft.com...
>
>
>

Warren Brunk

2006-10-24, 6:37 pm

Back up your transaction log, and do a point in time recovery.

Or you can take your latest full back up and restore it to a new DB and copy
the data in.

thanks,

--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/

"Srik" <Srik@discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1- 853FC2F7ACA2@microso
ft.com...[color=darkred]
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
>
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
>
> "Warren Brunk" wrote:
>

2006-10-24, 6:37 pm

If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.

MC

"Srik" <Srik@discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA- BBA62368148E@microso
ft.com...
> Hi,
>
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
>
> Thanks in advance...

Sponsored Links