October 30, 2002 at 6:31 am
accidentally i have deleted data from a table. and i dont have any backup. can i recover data from log file?if so how do i do that?
Please help me?
Pratap
October 30, 2002 at 9:08 am
What version of SQL Server are you using?
In 7.0 you need a place to start from and you don't have that. I know of know way to restore back what was deleted?
I don't know if there's a tool in 2K for this- I know Oracle has one in 9i which goes against the redo log files, pretty cool actually.
John Zacharkan
John Zacharkan
October 30, 2002 at 10:20 pm
Thanks John Zacharkan,solart for the information.
i am using SQL Server 2000. Log Explorer trail version supports only pubs and northwind databases only. with out using tool can't we do this?
Pratap
Edited by - pratapkrg on 10/30/2002 10:28:40 PM
October 31, 2002 at 2:57 pm
No you can´t not. Maybe you should buy Log Explorer or check for another log tool.
November 1, 2002 at 2:45 pm
You always have to start from a backup i am afraid
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 1, 2002 at 3:42 pm
Unfortunately, Log Explorer is the only tool I have sen that can do this. The fact however that it can do this does mean a DBA should be able to do it without it somehow but I have not taken the time to figure it out. If anyone does have time and would like to compare notes on this, please email me. I am trying to find some old 6.5 documentation or info from MS but have not had luck yet.
November 4, 2002 at 12:38 am
Hi,
There is a way you can restore a database up to second BUT first your recovery model must be set in Full. If you had selected Full you can proceed
Fisrt you need a backup to start so you execute the backup with NO_TRUNCATE option as follows
BACKUP LOG MyNwind
TO MyNwind_log2
WITH NO_TRUNCATE
Now you can perform a restore as follows
stop at is the exact time up to you want to restore
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
November 4, 2002 at 12:39 am
Hi,
There is a way you can restore a database up to second BUT first your recovery model must be set in Full. If you had selected Full you can proceed
Fisrt you need a backup to start so you execute the backup with NO_TRUNCATE option as follows
BACKUP LOG MyNwind
TO MyNwind_log2
WITH NO_TRUNCATE
Now you can perform a restore as follows
stop at is the exact time up to you want to restore
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
November 5, 2002 at 10:28 am
quote:
Unfortunately, Log Explorer is the only tool I have sen that can do this. The fact however that it can do this does mean a DBA should be able to do it without it somehow but I have not taken the time to figure it out. If anyone does have time and would like to compare notes on this, please email me. I am trying to find some old 6.5 documentation or info from MS but have not had luck yet.
DBCC LOG is an undocumented command for reading from a log file. You can find some limited documentation on it in Ken Henderson's Guru's Guide to Transact SQL. The command has the following format:
DBCC LOG ('database'[, format])
"[D]atabase" is the name of the database whose log you wish to view. "[F]ormat" is an integral value between 0 (the default) and 4 that specifies different formats for the output; different formats provide different levels of detail, with higher numbered formats generally providing higher level of detail, and usually greater levels of readability. The results tend to be cryptic, but a saavy DBA can interpret them, and can even use them to do what Log Explorer does, including undo-ing transactions. In fact, I've traditionally viewed Log Explorer as little more than a wrapper for this command, but newer versions of the product have persuaded me that it may be a worthwhile buy, thanks to additional features it provides; and of course, while one can certainly use DBCC LOG to figure out how to undo a command, Log Explorer makes it much simpler. In any case, DBCC LOG is a great educational tool; it provides a lot of insight into how SQL Server performs various operations, how databases are physically structured, and how transactions work.
Matthew Burr
November 5, 2002 at 6:43 pm
That is what I remember was DBCC LOG, but if memory serves it is not very usefull or easy to figure out how to do what LogExplorer does with DBCC LOG.
Edited by - antares686 on 11/05/2002 6:43:44 PM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply