Classic error and question how do restore?

Hi all,
Well, Im here to make a classic question (I guess).
I did droped a table today, there was no backup, exists any possibility to
recover the table?
Before to come here I make a research about this and find few options.
1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
2 use some commands to recover the database and log to a new database at a
certain point of transaction log.
I tried first download the Log Explorer and it restrict me to open only an
example database.
So i look on google and find a lot of command.. all confuse.
So I pressed F1 and read about the RECOVER command and tried this:
I made an backup of my database
and executed the following string in Query Analizer
RESTORE DATABASE NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH NORECOVERY, REPLACE
GO
RESTORE LOG NewDB
FROM disk='c:\Program Files\Microsoft SQL
Server\MSSQL\Backup\BrasValor.bak'
WITH RECOVERY, STOPAT = '21/12/2004'
GO
the restore occours successful, but the table is empty
after this I tried to restore the old database with the LumigentDemoDB name
to use the software, Nice, it is listed but it says tha there is no log for
the database.
And now? what to do?
If you think that u can help me, please, I beg to you.
Reguards,
Luiz
Hi
It is not clear if you followed the correct procedure to restore to point in
time from:
http://msdn.microsoft.com/library/de...ackpc_5a61.asp
you have only done the steps two and four.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, Im here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>
|||Luiz
It sounds from your description that you made the backup of the database
AFTER you dropped the table, since you said you had no backup at the time
the error occurred. If that is true, you backed up a copy of the database
with the table already missing, so there is no way that restoring that
database will bring anything back. You must have a backup made BEFORE you
dropped the table.
The Lumigent product might have been able to help before you did the backup
and restore. However, the sample that you downloaded was just a sample. If
you want a product that will save your skin, you really should not expect to
get it for free.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, Im here to make a classic question (I guess).
> I did droped a table today, there was no backup, exists any possibility to
> recover the table?
> Before to come here I make a research about this and find few options.
> 1 use the "Log Explorer" from Lumigent to "rollback" the transaction.
> 2 use some commands to recover the database and log to a new database at a
> certain point of transaction log.
> I tried first download the Log Explorer and it restrict me to open only an
> example database.
> So i look on google and find a lot of command.. all confuse.
> So I pressed F1 and read about the RECOVER command and tried this:
> I made an backup of my database
> and executed the following string in Query Analizer
> RESTORE DATABASE NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH NORECOVERY, REPLACE
> GO
> RESTORE LOG NewDB
> FROM disk='c:\Program Files\Microsoft SQL
> Server\MSSQL\Backup\BrasValor.bak'
> WITH RECOVERY, STOPAT = '21/12/2004'
> GO
> the restore occours successful, but the table is empty
> after this I tried to restore the old database with the LumigentDemoDB
> name to use the software, Nice, it is listed but it says tha there is no
> log for the database.
> And now? what to do?
> If you think that u can help me, please, I beg to you.
> Reguards,
> Luiz
>
|||Kalen,
Sorry, well, sorry everybody, my english is pretty bad.
Yes, I do not have any backup before the drop command
Looking all night long I discovered that I must make the transaction log
backup, so, I did it, trying to restore it dont let me to restore before
the date of
backup.
Opening the transaction log file (.ldf) in notepad I saw the data that has
been lost. So I still believe that I can recover it, but, how?
Now i'm going to try something, change back the date of computer, make the
backup and try to restore it.
Any other light?
Thank you Kalen and John
[]'s
Luiz
"Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Luiz
> It sounds from your description that you made the backup of the database
> AFTER you dropped the table, since you said you had no backup at the time
> the error occurred. If that is true, you backed up a copy of the database
> with the table already missing, so there is no way that restoring that
> database will bring anything back. You must have a backup made BEFORE you
> dropped the table.
> The Lumigent product might have been able to help before you did the
> backup and restore. However, the sample that you downloaded was just a
> sample. If you want a product that will save your skin, you really should
> not expect to get it for free.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>
|||Hi
You don't say how often the data changes in this table. If it is reasonably
static, you could restore the last full backup (before the problem) to a new
database and then just copy the table back into your live database.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
> Kalen,
> Sorry, well, sorry everybody, my english is pretty bad.
> Yes, I do not have any backup before the drop command
> Looking all night long I discovered that I must make the transaction log
> backup, so, I did it, trying to restore it dont let me to restore before
> the date of
> backup.
> Opening the transaction log file (.ldf) in notepad I saw the data that has
> been lost. So I still believe that I can recover it, but, how?
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
> Thank you Kalen and John
> []'s
> Luiz
>
> "Kalen Delaney" <replies@.public_newsgroups.com> escreveu na mensagem
> news:%23H%23syl65EHA.3708@.TK2MSFTNGP14.phx.gbl...
>
|||John,
Every day about 600 rows is inserted on this table, only insert, no delete.
I have no one backup before this happen (damn why?!)
I'll be more especific. I was trying to add a column in this table creating
a new temporary table,
inserting all the data, droping the old and renaming the new.
The error occour inserting all data in the new table.
The way that i commented (set back the date of computer) don't work
restoring the log it says that the STOPAT parameter is wrong (using the MMC
console)
Thank u one more time
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
> Hi
> You don't say how often the data changes in this table. If it is
> reasonably static, you could restore the last full backup (before the
> problem) to a new database and then just copy the table back into your
> live database.
> John
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:uhHOoNC6EHA.4004@.tk2msftngp13.phx.gbl...
>
|||>
> Now i'm going to try something, change back the date of computer, make the
> backup and try to restore it.
> Any other light?
>
Seems good to me, but when you restore full data base backup use with
NORECOVERY option, this will give you the chance to restore log, but this
time with RECOVERY option
Regards,
Daniel
|||Hi
It is not a good idea to do ad-hoc SQL on a production system, it may cause
unnecessary locking and excessive resource usage, performing ad-hoc DDL may
cause problems like yours. Keeping your code in a source code control system
will enable you to audit and test changes. It will also allow you to
re-create any version of your database from scratch.
You should also implement a structured backup and maintainance process.
I assume that you have lost your temporary table?
You should not need to change the date of the computer to do the recovery.
Have you tried an earlier time to see if you get some data back?
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
> John,
> Every day about 600 rows is inserted on this table, only insert, no
> delete. I have no one backup before this happen (damn why?!)
> I'll be more especific. I was trying to add a column in this table
> creating a new temporary table,
> inserting all the data, droping the old and renaming the new.
> The error occour inserting all data in the new table.
> The way that i commented (set back the date of computer) don't work
> restoring the log it says that the STOPAT parameter is wrong (using the
> MMC console)
> Thank u one more time
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:ePCLjnD6EHA.1188@.tk2msftngp13.phx.gbl...
>
|||Hi John,
are you still in this case?
Well, yes, i lost the temp table (wel, both tables). My script has executed
something like this:
EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
DROP TABLE Original_Table /*Here is the shit*/
bla bla bla
..
..
..
DROP TABLE Temp_Table /*Shit was not complete without this*/
I tried to recover with a date before, but sql says that the date is less
than the minimun date.
I guess i think all possibilities.
Thankyou,
Luiz
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> It is not a good idea to do ad-hoc SQL on a production system, it may
> cause unnecessary locking and excessive resource usage, performing ad-hoc
> DDL may cause problems like yours. Keeping your code in a source code
> control system will enable you to audit and test changes. It will also
> allow you to re-create any version of your database from scratch.
> You should also implement a structured backup and maintainance process.
> I assume that you have lost your temporary table?
> You should not need to change the date of the computer to do the recovery.
> Have you tried an earlier time to see if you get some data back?
> John
> "Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
> news:etUnKFE6EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Luiz
I think the best thing you can do is call Microsoft PSS
http://support.microsoft.com/default.aspx, they will charge you for the
incident, but if it is recoverable they will be able to get you back up and
running quickly.
John
"Luiz Carlos Brazo" <luiz@.brasvalor.com.br> wrote in message
news:%23MmR1hA7EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> are you still in this case?
> Well, yes, i lost the temp table (wel, both tables). My script has
> executed something like this:
> EXEC ('INSERT INTO Temp_Table (bla bla bla bla...') /*Error occours*/
> DROP TABLE Original_Table /*Here is the shit*/
> bla bla bla
> .
> .
> .
> DROP TABLE Temp_Table /*Shit was not complete without this*/
>
> I tried to recover with a date before, but sql says that the date is less
> than the minimun date.
> I guess i think all possibilities.
> Thankyou,
> Luiz
> "John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
> news:u7KVYAF6EHA.2156@.TK2MSFTNGP10.phx.gbl...
>