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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy