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...
>

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
>>>
>>
>>
>>
>>
>>
>

choose from SQLDMO /SMO

in my classic ASP 3.0 application I would like to back up and restore SQL Express database. Should I use SQLDMO or SMO? Whats the difference?In classic ASP you'll have to stay with DMO, but remember that using DMO you won't be able to manage any of the new SQL Server 2005 features. If you expect to do that you'll have to upgrade your code to ASP.Net 2.0 and use SMO. I'd recommend the latter as DMO will not be supported in future releases of SQL Server.|||Thanks for your answer.I am using SQLDMO for backing up and restoring Sql Express database. While Restoring if i have any users connected to db ,restore fails.. Whats the solution for the problem? Do i need to Stop SQL Server ,restore and start again?|||You don't need to stop the server, just kill any connection to the database you're attempting to restore.|||

Allen,

I am using SQLDMO in my C# application for backing up and restoring MSDE
2000 database. Backup operations run fine, but Restoring fails. I've tried to KILL all the connections from the application but get the "cannot use KILL to kill your own process" error. Any ideas to solve this problem?
Thank you in advance for your help.
JC

|||Why I cannot use SMO in VBScript? Is there any limitation? I tried to use SMO in VBScript to connect to SQLExpress and got error :Object not found/object required. Is that the reason?|||VBScript uses COM for its object support and SMO uses the .Net Framework. The only server you can use VBScript to communicate with SMO is the default local instance of SQL Server, and SQL Express usually installs itself in a named instance called SQLExpress.|||Hello Everyone

i have one question please answer if you know, what is my requirement is if i supply the SQL server name i want all the available instances in that sql server for that i m writing code in C#

way no:
1).
ManagedComputer mc = new ManagedComputer(SystemName);
foreach (ServerInstance si in mc.ServerInstances)
{
lstInstanceNames.Items.Add(si.Name);
}

2).
DataTable dtSQLServers = SmoApplication.EnumAvailableSqlServers(servername);
foreach (DataRow drServer in dtSQLServers.Rows)
{
InstanceName = drServer["Instance"].ToString();
}

none of these are working please help me solve this problem

check time stamp on a *.bak file against the DB I have in SQL server and restore

I have a *.bak file generated by other guys using their own SQL server.
My task is to restore the *.bak to my SQL server if what i have in my
SQL server is an older version that the *.bak file.
ThanksHi
Look at
RESTORE HEADERONLY
FROM <backup_device>
in BOL. Will tell you when the DB was backed up.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||Try for instance RESTORE HEADERONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||RESTORE HEADERONLY gives you the backup time, BUT... the backup time doesn't
necisarrily mean your database is older. The data in your current database
could be newer than the backup.
It depends what your trying to do here? Restore the backup simply because
it's a later backup or restore the DB if the data is newer?
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||Doesn't Resore Headeronly...affect my database?
Let me rephrase my question.
I have a SQL server running in my machine. Another guy, Mr. X, does all
the data entry, table creation and all that on a separate server. The
bak file is constantly updated by X and he puts the latest in some
shared folder.
In my machine when I run a certain code, I check to see if a database I
have in my sql server (i.e which is restored from a *bak file from Mr.
X) is up to date with what X has put in his shared folder. If what I
have is old, I do a restore if not restore is skipped.
Thanks All|||> Doesn't Resore Headeronly...affect my database?
No. It doesn't do a restore, it only give you information about the backup. See the documentation in
Books Online.
One way you can do this is to read the backup history tables in your msdb database and see when you
last restored that database and compare that to the output from RESTORE HEADERONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133826384.413091.91120@.o13g2000cwo.googlegroups.com...
> Doesn't Resore Headeronly...affect my database?
> Let me rephrase my question.
> I have a SQL server running in my machine. Another guy, Mr. X, does all
> the data entry, table creation and all that on a separate server. The
> bak file is constantly updated by X and he puts the latest in some
> shared folder.
> In my machine when I run a certain code, I check to see if a database I
> have in my sql server (i.e which is restored from a *bak file from Mr.
> X) is up to date with what X has put in his shared folder. If what I
> have is old, I do a restore if not restore is skipped.
>
> Thanks All
>|||xela wrote:
> Doesn't Resore Headeronly...affect my database?
> Let me rephrase my question.
> I have a SQL server running in my machine. Another guy, Mr. X, does all
> the data entry, table creation and all that on a separate server. The
> bak file is constantly updated by X and he puts the latest in some
> shared folder.
> In my machine when I run a certain code, I check to see if a database I
> have in my sql server (i.e which is restored from a *bak file from Mr.
> X) is up to date with what X has put in his shared folder. If what I
> have is old, I do a restore if not restore is skipped.
>
> Thanks All
>
Hi
What you try to do is not absolutely "fool proof" since you're trying to
compare a database to a backup. I think your best option, is to store
the backupcreate date from the .bak file everytime you restore it. Then
you can compare this date with the date from the "new" backup file and
restore it if it's newer. I think you'll have to do some coding your
self to achieve the function you want.
You should also keep in mind that the backup creation time/date doesn't
tell anything about the data in the file as such- it only tells you when
the backup was done.
Regards
Steen|||Hey guys, thanks for all the support.
I have more questions though.I was trying to follow Steens' steps.
how can select the db property "Date created" from the live database.
and also how do i select the "backupstartdate" from the *.file?
Thanks|||xela wrote:
> Hey guys, thanks for all the support.
> I have more questions though.I was trying to follow Steens' steps.
> how can select the db property "Date created" from the live database.
> and also how do i select the "backupstartdate" from the *.file?
> Thanks
>
The database create date you can get from sysdatabases (SELECT crdate
FROM master.dbo.sysdatabases WHERE name = 'YourDatabaseName') but that
might not always be what you want.
It would require that you drop the database before you restore the new
database to get this field updated with the new date. If you just
restore a new backup on top of the existing database, the create date
will stays the same.
The Backupstart and -stop date, you can get from running a RESTORE
HEADERONLY on your backup file as already mentioned in this thread.
Regards
Steen|||here is one bulky way i tried doing it...it does get the job done but
its ugly
set @.dbexist = 1
--create a temporary table for the bak file header result set
CREATE TABLE #mytemptable (BackupName nvarchar(128),BackupDescription
nvarchar(255),
BackupType smallint,ExpirationDate datetime,Compressed
tinyint,Position smallint,DeviceType tinyint,
UserName nvarchar(128),ServerName nvarchar(128),DatabaseName
nvarchar(128),DatabaseVersion int,
DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN
numeric(25,0),LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),DatabaseBackupLSN
numeric(25,0),BackupStartDate datetime,
BackupFinishDate datetime,SortOrder smallint,CodePage
smallint,UnicodeLocaleId int,
UnicodeComparisonStyle int,CompatibilityLevel
tinyint,SoftwareVendorId int,SoftwareVersionMajor int,
SoftwareVersionMinor int,SoftwareVersionBuild int,MachineName
nvarchar(128),Flags int,
BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation
nvarchar(128),
FamilyGUID uniqueidentifier,HasBulkLoggedData bit,IsSnapshot
bit,IsReadOnly bit,
IsSingleUser bit,HasBackupChecksums bit,IsDamaged bit,BeginsLogChain
bit,HasIncompleteMetaData bit,
IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID
uniqueidentifier,ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),DifferentialBaseLSN numeric(25,0)
NULL,DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier
NULL)
declare @.mycommand nvarchar(500),
@.guid_bak uniqueidentifier,
@.guid_db uniqueidentifier
set @.mycommand = 'Restore HEADERONLY FROM DISK =''$(TempPath)\AdventureWorks\AdventureWorksStaging.bak'' WITH NOUNLOAD'
insert #mytemptable exec(@.mycommand)
--get the GUIDs
set @.guid_bak = (select TOP 1 BackupSetGUID from #mytemptable order
by BackupStartDate desc)
set @.guid_db = (select TOP 1 backup_set_uuid from msdb.dbo.backupset
where [name] like '%AdventureWorksStaging%' order by
database_creation_date desc)
IF (@.guid_bak <> @.guid_db)
begin
set @.versionIntact = -1
end
else
set @.versionIntact = 1
drop table #mytemptable|||xela wrote:
> here is one bulky way i tried doing it...it does get the job done but
> its ugly
> set @.dbexist = 1
> --create a temporary table for the bak file header result set
> CREATE TABLE #mytemptable (BackupName nvarchar(128),BackupDescription
> nvarchar(255),
> BackupType smallint,ExpirationDate datetime,Compressed
> tinyint,Position smallint,DeviceType tinyint,
> UserName nvarchar(128),ServerName nvarchar(128),DatabaseName
> nvarchar(128),DatabaseVersion int,
> DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN
> numeric(25,0),LastLSN numeric(25,0),
> CheckpointLSN numeric(25,0),DatabaseBackupLSN
> numeric(25,0),BackupStartDate datetime,
> BackupFinishDate datetime,SortOrder smallint,CodePage
> smallint,UnicodeLocaleId int,
> UnicodeComparisonStyle int,CompatibilityLevel
> tinyint,SoftwareVendorId int,SoftwareVersionMajor int,
> SoftwareVersionMinor int,SoftwareVersionBuild int,MachineName
> nvarchar(128),Flags int,
> BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation
> nvarchar(128),
> FamilyGUID uniqueidentifier,HasBulkLoggedData bit,IsSnapshot
> bit,IsReadOnly bit,
> IsSingleUser bit,HasBackupChecksums bit,IsDamaged bit,BeginsLogChain
> bit,HasIncompleteMetaData bit,
> IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID
> uniqueidentifier,ForkPointLSN numeric(25,0) NULL,
> RecoveryModel nvarchar(60),DifferentialBaseLSN numeric(25,0)
> NULL,DifferentialBaseGUID uniqueidentifier,
> BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier
> NULL)
> declare @.mycommand nvarchar(500),
> @.guid_bak uniqueidentifier,
> @.guid_db uniqueidentifier
> set @.mycommand = 'Restore HEADERONLY FROM DISK => ''$(TempPath)\AdventureWorks\AdventureWorksStaging.bak'' WITH NOUNLOAD'
> insert #mytemptable exec(@.mycommand)
> --get the GUIDs
> set @.guid_bak = (select TOP 1 BackupSetGUID from #mytemptable order
> by BackupStartDate desc)
> set @.guid_db = (select TOP 1 backup_set_uuid from msdb.dbo.backupset
> where [name] like '%AdventureWorksStaging%' order by
> database_creation_date desc)
> IF (@.guid_bak <> @.guid_db)
> begin
> set @.versionIntact = -1
> end
> else
> set @.versionIntact = 1
> drop table #mytemptable
>
It's more or less the same way I'm doing and I don't know of any smarter
way to get the info from the RESTORE HEADERONLY command.
Regards
Steen

check time stamp on a *.bak file against the DB I have in SQL server and restore

I have a *.bak file generated by other guys using their own SQL server.
My task is to restore the *.bak to my SQL server if what i have in my
SQL server is an older version that the *.bak file.
ThanksTry for instance RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||Hi
Look at
RESTORE HEADERONLY
FROM <backup_device>
in BOL. Will tell you when the DB was backed up.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||RESTORE HEADERONLY gives you the backup time, BUT... the backup time doesn't
necisarrily mean your database is older. The data in your current database
could be newer than the backup.
It depends what your trying to do here? Restore the backup simply because
it's a later backup or restore the DB if the data is newer?
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegroups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>|||Doesn't Resore Headeronly...affect my database?
Let me rephrase my question.
I have a SQL server running in my machine. Another guy, Mr. X, does all
the data entry, table creation and all that on a separate server. The
bak file is constantly updated by X and he puts the latest in some
shared folder.
In my machine when I run a certain code, I check to see if a database I
have in my sql server (i.e which is restored from a *bak file from Mr.
X) is up to date with what X has put in his shared folder. If what I
have is old, I do a restore if not restore is skipped.
Thanks All|||> Doesn't Resore Headeronly...affect my database?
No. It doesn't do a restore, it only give you information about the backup.
See the documentation in
Books Online.
One way you can do this is to read the backup history tables in your msdb da
tabase and see when you
last restored that database and compare that to the output from RESTORE HEAD
ERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133826384.413091.91120@.o13g2000cwo.googlegroups.com...
> Doesn't Resore Headeronly...affect my database?
> Let me rephrase my question.
> I have a SQL server running in my machine. Another guy, Mr. X, does all
> the data entry, table creation and all that on a separate server. The
> bak file is constantly updated by X and he puts the latest in some
> shared folder.
> In my machine when I run a certain code, I check to see if a database I
> have in my sql server (i.e which is restored from a *bak file from Mr.
> X) is up to date with what X has put in his shared folder. If what I
> have is old, I do a restore if not restore is skipped.
>
> Thanks All
>|||Hey guys, thanks for all the support.
I have more questions though.I was trying to follow Steens' steps.
how can select the db property "Date created" from the live database.
and also how do i select the "backupstartdate" from the *.file?
Thanks|||here is one bulky way i tried doing it...it does get the job done but
its ugly
set @.dbexist = 1
--create a temporary table for the bak file header result set
CREATE TABLE #mytemptable (BackupName nvarchar(128),BackupDescription
nvarchar(255),
BackupType smallint,ExpirationDate datetime,Compressed
tinyint,Position smallint,DeviceType tinyint,
UserName nvarchar(128),ServerName nvarchar(128),DatabaseName
nvarchar(128),DatabaseVersion int,
DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN
numeric(25,0),LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),DatabaseBackupLSN
numeric(25,0),BackupStartDate datetime,
BackupFinishDate datetime,SortOrder smallint,CodePage
smallint,UnicodeLocaleId int,
UnicodeComparisonStyle int,CompatibilityLevel
tinyint,SoftwareVendorId int,SoftwareVersionMajor int,
SoftwareVersionMinor int,SoftwareVersionBuild int,MachineName
nvarchar(128),Flags int,
BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation
nvarchar(128),
FamilyGUID uniqueidentifier,HasBulkLoggedData bit,IsSnapshot
bit,IsReadOnly bit,
IsSingleUser bit,HasBackupChecksums bit,IsDamaged bit,BeginsLogChain
bit,HasIncompleteMetaData bit,
IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID
uniqueidentifier,ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),DifferentialBaseLSN numeric(25,0)
NULL,DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier
NULL)
declare @.mycommand nvarchar(500),
@.guid_bak uniqueidentifier,
@.guid_db uniqueidentifier
set @.mycommand = 'Restore HEADERONLY FROM DISK =
''$(TempPath)\AdventureWorks\AdventureWo
rksStaging.bak'' WITH NOUNLOAD'
insert #mytemptable exec(@.mycommand)
--get the GUIDs
set @.guid_bak = (select TOP 1 BackupSetGUID from #mytemptable order
by BackupStartDate desc)
set @.guid_db = (select TOP 1 backup_set_uuid from msdb.dbo.backupset
where [name] like '%AdventureWorksStaging%' order by
database_creation_date desc)
IF (@.guid_bak <> @.guid_db)
begin
set @.versionIntact = -1
end
else
set @.versionIntact = 1
drop table #mytemptable

check time stamp on a *.bak file against the DB I have in SQL server and restore

I have a *.bak file generated by other guys using their own SQL server.
My task is to restore the *.bak to my SQL server if what i have in my
SQL server is an older version that the *.bak file.
Thanks
Try for instance RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegro ups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>
|||Hi
Look at
RESTORE HEADERONLY
FROM <backup_device>
in BOL. Will tell you when the DB was backed up.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegro ups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>
|||RESTORE HEADERONLY gives you the backup time, BUT... the backup time doesn't
necisarrily mean your database is older. The data in your current database
could be newer than the backup.
It depends what your trying to do here? Restore the backup simply because
it's a later backup or restore the DB if the data is newer?
"xela" <alemyis@.gmail.com> wrote in message
news:1133818374.006327.35390@.z14g2000cwz.googlegro ups.com...
>I have a *.bak file generated by other guys using their own SQL server.
> My task is to restore the *.bak to my SQL server if what i have in my
> SQL server is an older version that the *.bak file.
> Thanks
>
|||Doesn't Resore Headeronly...affect my database?
Let me rephrase my question.
I have a SQL server running in my machine. Another guy, Mr. X, does all
the data entry, table creation and all that on a separate server. The
bak file is constantly updated by X and he puts the latest in some
shared folder.
In my machine when I run a certain code, I check to see if a database I
have in my sql server (i.e which is restored from a *bak file from Mr.
X) is up to date with what X has put in his shared folder. If what I
have is old, I do a restore if not restore is skipped.
Thanks All
|||> Doesn't Resore Headeronly...affect my database?
No. It doesn't do a restore, it only give you information about the backup. See the documentation in
Books Online.
One way you can do this is to read the backup history tables in your msdb database and see when you
last restored that database and compare that to the output from RESTORE HEADERONLY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"xela" <alemyis@.gmail.com> wrote in message
news:1133826384.413091.91120@.o13g2000cwo.googlegro ups.com...
> Doesn't Resore Headeronly...affect my database?
> Let me rephrase my question.
> I have a SQL server running in my machine. Another guy, Mr. X, does all
> the data entry, table creation and all that on a separate server. The
> bak file is constantly updated by X and he puts the latest in some
> shared folder.
> In my machine when I run a certain code, I check to see if a database I
> have in my sql server (i.e which is restored from a *bak file from Mr.
> X) is up to date with what X has put in his shared folder. If what I
> have is old, I do a restore if not restore is skipped.
>
> Thanks All
>
|||Hey guys, thanks for all the support.
I have more questions though.I was trying to follow Steens' steps.
how can select the db property "Date created" from the live database.
and also how do i select the "backupstartdate" from the *.file?
Thanks
|||here is one bulky way i tried doing it...it does get the job done but
its ugly
set @.dbexist = 1
--create a temporary table for the bak file header result set
CREATE TABLE #mytemptable (BackupName nvarchar(128),BackupDescription
nvarchar(255),
BackupType smallint,ExpirationDate datetime,Compressed
tinyint,Position smallint,DeviceType tinyint,
UserName nvarchar(128),ServerName nvarchar(128),DatabaseName
nvarchar(128),DatabaseVersion int,
DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN
numeric(25,0),LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),DatabaseBackupLSN
numeric(25,0),BackupStartDate datetime,
BackupFinishDate datetime,SortOrder smallint,CodePage
smallint,UnicodeLocaleId int,
UnicodeComparisonStyle int,CompatibilityLevel
tinyint,SoftwareVendorId int,SoftwareVersionMajor int,
SoftwareVersionMinor int,SoftwareVersionBuild int,MachineName
nvarchar(128),Flags int,
BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation
nvarchar(128),
FamilyGUID uniqueidentifier,HasBulkLoggedData bit,IsSnapshot
bit,IsReadOnly bit,
IsSingleUser bit,HasBackupChecksums bit,IsDamaged bit,BeginsLogChain
bit,HasIncompleteMetaData bit,
IsForceOffline bit,IsCopyOnly bit,FirstRecoveryForkID
uniqueidentifier,ForkPointLSN numeric(25,0) NULL,
RecoveryModel nvarchar(60),DifferentialBaseLSN numeric(25,0)
NULL,DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),BackupSetGUID uniqueidentifier
NULL)
declare @.mycommand nvarchar(500),
@.guid_bak uniqueidentifier,
@.guid_db uniqueidentifier
set @.mycommand = 'Restore HEADERONLY FROM DISK =
''$(TempPath)\AdventureWorks\AdventureWorksStaging .bak'' WITH NOUNLOAD'
insert #mytemptable exec(@.mycommand)
--get the GUIDs
set @.guid_bak = (select TOP 1 BackupSetGUID from #mytemptable order
by BackupStartDate desc)
set @.guid_db = (select TOP 1 backup_set_uuid from msdb.dbo.backupset
where [name] like '%AdventureWorksStaging%' order by
database_creation_date desc)
IF (@.guid_bak <> @.guid_db)
begin
set @.versionIntact = -1
end
else
set @.versionIntact = 1
drop table #mytemptable