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...
>
Showing posts with label possibility. Show all posts
Showing posts with label possibility. Show all posts
Thursday, March 22, 2012
Classic error and question how do restore?
Hi all,
Well, I´m 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,
LuizHi
It is not clear if you followed the correct procedure to restore to point in
time from:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_5a61.asp
you have only done the steps two and four.
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, I´m 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, I´m 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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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
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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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
>>
>|||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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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
>>
>>
>|||>
> 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 Brazão" <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
>> 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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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 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 Brazão" <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
>> 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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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 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 Brazão" <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...
>> 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 Brazão" <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
>> 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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>>> Hi all,
>>>
>>> Well, I´m 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
>>>
>>
>>
>>
>>
>>
>
Well, I´m 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,
LuizHi
It is not clear if you followed the correct procedure to restore to point in
time from:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_5a61.asp
you have only done the steps two and four.
John
"Luiz Carlos Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, I´m 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Well, I´m 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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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
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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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
>>
>|||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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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
>>
>>
>|||>
> 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 Brazão" <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
>> 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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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 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 Brazão" <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
>> 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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>> Hi all,
>> Well, I´m 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 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 Brazão" <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...
>> 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 Brazão" <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
>> 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 Brazão" <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 don´t 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 Brazão" <luiz@.brasvalor.com.br> wrote in message
>> news:%23kZF6f45EHA.824@.TK2MSFTNGP11.phx.gbl...
>>> Hi all,
>>>
>>> Well, I´m 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
>>>
>>
>>
>>
>>
>>
>
Saturday, February 25, 2012
Checking User & Expire of Update Possibility (by Trigger), How to?
Hi,
I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:
RateID, Service, Rate, DateTime, User
Want I want is a code (trigger) in the database can do the following:
If user perform an Update request the code will check:
- if this recored inserted by the same user update command will be execute.
- if this recored inserted by other user: update command will not execute and return message.
- if more than 5 minutes passed the update command will not be execute and return message.
Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.
Change the update statement from something like:
update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
to something like
|||update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())
Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?
and what if I want to put this code in Trigger?
thnx again,,,
|||Any help?|||Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.
The code idea below relies upon [User] being captured with the system_user system function (domain/username).
Code Snippet
CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
DECLARE @.User varchar(50)
IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END
GO
Hi,
I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:
CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)
END
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
Checking User & Expire of Update Possibility (by Trigger), How to?
Hi,
I have Table (RatesTable) every user can insert records to this table, and all users can see this records, this table contain the following columns:
RateID, Service, Rate, DateTime, User
Want I want is a code (trigger) in the database can do the following:
If user perform an Update request the code will check:
- if this recored inserted by the same user update command will be execute.
- if this recored inserted by other user: update command will not execute and return message.
- if more than 5 minutes passed the update command will not be execute and return message.
Yes, this can be done with a trigger but it really would be better for the update statement itself to decide whether or not the update is allowed by adding either a WHERE condition or an AND condition to the update statement to decide whether or not to allow the update.
Change the update statement from something like:
update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
to something like
|||update rate
set service = @.serviceChange,
rate = @.rateChange
where rateId = @.rateRecordId
and user = @.currentUser
and dateTime >= dateadd (mi, -5, getdate())
Thanks Kent Waldrop Ap07, but the problem is my all program use Datasets created by Data Source Configration Wizzard, is it possible to to add your code to the Dataset Designer?
and what if I want to put this code in Trigger?
thnx again,,,
|||Any help?|||Here is a TRIGGER idea. While I agree with Kent that changing the UPDATE statement is a better option, I know from expereince that it is not always the solution that works.
The code idea below relies upon [User] being captured with the system_user system function (domain/username).
Code Snippet
CREATE TRIGGER tr_RatesTable_U_UserOnly
ON RatesTable
FOR UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
DECLARE @.User varchar(50)
IF EXISTS
( SELECT *
FROM inserted
WHERE User <> system_user
)
BEGIN
ROLLBACK
RAISERROR('Cannot UpDate This Record', 16, 1)
RETURN
END
GO
Hi,
I am not familiar with the Wizard stuff, but I would expect that it does not cover holding the logic for that. But should have a look on the resulting queries the wizard produces, maybe you are able to tweak the Update statement to cover your logic. Anyway, using a trigger could be another option:
CREATE TRIGGER TRG_UPD_SomeTable
ON SomeTable
FOR UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT * From INSERTED WHERE User = SUSER_NAME AND DateTime <= DATEADD(s,-5,GETDATE()))
RAISERROR('Update not allowed',16,1)
END
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Comments (Atom)