Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
delpiero
Yak Posting Veteran
98 Posts |
 Posted - 2005-06-07 : 13:28:26
|
| Hi all, I understand that there is no simple and direct way to restore a single table from a full backup file in SQL Server 2000, other than restoring the whole database to a different place and copy back the table.1. Are there any third-party tools which can perform similar functions as the Flash-back thing in oracle 9i? restoring a transaction 2-3 days ago. 2. If we put that table in a separate filegroup and restore only that filegroup, is there any method which can limit the restore to only certain rows in the table?3. Any good suggestion of 3rd party tools which allow us to read the contents of transaction log without disturbing service?Thanks,delpiero |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-07 : 13:39:27
|
| Lumigent has Log Explorer:http://www.lumigent.com/It can do pretty much everything you're asking, but it depends entirely on your recovery model and backup schedule. If you back up your transaction logs regularly, or your recovery model is Simple, then it won't help you.An ounce of prevention is worth a pound of cure: if this is happening frequently then your time would be better spent in preventing these types of accidents from occurring. If rows are being deleted or updated inappropriately then you need to restrict the ability for users to perform these operations. |
 |
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-06-07 : 23:46:19
|
| Thanks. I am using full recovery model and I backup the transaction log every 15 minutes, with TRUNCATE. Do you mean that I cannot use the Lumigent's Log Explorer to view the contents of the transactions 2 days ago? Can we use Lumigent's Log Explorer with the transaction log backups instead of the active transsaction log itself? Or we must restore the log to another database and view it using Log explorer?Thank you,delpiero |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-07 : 23:56:11
|
| I don't know off the top of my head if Log Explorer can read backup files (a few years ago it did not) but their web site should have a spec sheet that can confirm that. Their Entegra product CAN read log backups, but it's more of a full auditing package than a recovery/log reading program.It's still important to recognize that the tool only works when used properly and in a timely manner. You set yourself up for disaster by assuming it can recover anything at any time; it can't. And by no means should you use this tool for anything other than emergencies. It should not be part of a regular process for restoring or recovering data...that's what backups are for. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-08 : 00:14:10
|
Warning, big red flag!Does this mean you are running this kind of backup command?BACKUP LOG database_name WITH NO_LOGorBACKUP LOG database_name WITH TRUNCATE_ONLYIf you are, it means you are not getting a transaction log backup, but are only truncating the transaction log, and you won't be able to recover anything that happened since the last full backup.From SQL Server BOL BACKUP Topic:"NO_LOG | TRUNCATE_ONLYRemoves the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE"quote:
Originally posted by delpiero...I am using full recovery model and I backup the transaction log every 15 minutes, with TRUNCATE...
CODO ERGO SUM |
|
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-06-08 : 02:03:15
|
| Thanks Michael for the reminder, What I mean WITH TRUNCATE is that I didn't set the NO TRUNCATE option. So actually I am not setting any options (leaving the default) in my backup transaction log command. It should backup the log and truncate it after backing up . Thanks,delpiero |
|
|
|
leynakarthik
Starting Member
7 Posts |
Posted - 2005-06-08 : 02:41:05
|
| Hi,I met same issue two months back that time I created one database and restored the database to newly creted database and moved table using DTS. This way only for restored particular table from backup file.leynakarthik |
|
|
|
|
|
|
|
|
Restore a table - again