For a reason that we don't know, one table consisting of about 1200 rows in
SQL Server 2005 Express SP2 shows to be empty at once. Since all records
were addded within this day, there is no suitable back-up of a couple of
hours ago, I don't know a way to restore the records.
1) Is this a known bug of SQL Server 2005 SP2?
1) Is there a way to find out the reason why this happened?
2) Is there a way to restore the deleted rows?
3) How can I prevent this to happen again?"Oscar" <oku@.xs4all.nl> wrote in message
news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
> For a reason that we don't know, one table consisting of about 1200 rows
> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
> records were addded within this day, there is no suitable back-up of a
> couple of hours ago, I don't know a way to restore the records.
> 1) Is this a known bug of SQL Server 2005 SP2?
> 1) Is there a way to find out the reason why this happened?
> 2) Is there a way to restore the deleted rows?
> 3) How can I prevent this to happen again?
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
> For a reason that we don't know, one table consisting of about 1200 rows
> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
> records were addded within this day, there is no suitable back-up of a
> couple of hours ago, I don't know a way to restore the records.
> 1) Is this a known bug of SQL Server 2005 SP2?
Not that I've heard.
> 1) Is there a way to find out the reason why this happened?
Possibly, but may not be entirely worth the effort.
> 2) Is there a way to restore the deleted rows?
MAYBE!
If you are in full-logging and not simple logging, you may be able to do the
following:
I assume you have a full backup from say last night?
Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option so
that you don't truncate the log, just in case).
Anyway, do a RESTORE from the full backup to a different database name WITH
NORECOVERY.
Then a RESTORE LOG using the backup you just made and then WITH STOPATMARK =<time>, STANDBY=filename.
This will restore the log to whatever time you want. The STANDBY will allow
you to put the database into read-only mode while you then do a select on
the table.
If the data is there, you can then select it back into your live database.
If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
luck.
If you need more help, let me know via email or here.
> 3) How can I prevent this to happen again?
Security.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
I am not that familiar with these items.
a) how can I find whether the configuration is full-logging or simple
logging?
b) what do you mean with 'Take a Transaction Log backup now'? How can I find
this?
c) how can I do a RESTORE LOG?
thanks for your help.
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:%23qklnHLcHHA.4032@.TK2MSFTNGP02.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
>> For a reason that we don't know, one table consisting of about 1200 rows
>> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
>> records were addded within this day, there is no suitable back-up of a
>> couple of hours ago, I don't know a way to restore the records.
>> 1) Is this a known bug of SQL Server 2005 SP2?
> Not that I've heard.
>
>> 1) Is there a way to find out the reason why this happened?
> Possibly, but may not be entirely worth the effort.
>> 2) Is there a way to restore the deleted rows?
> MAYBE!
> If you are in full-logging and not simple logging, you may be able to do
> the following:
> I assume you have a full backup from say last night?
> Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option
> so that you don't truncate the log, just in case).
> Anyway, do a RESTORE from the full backup to a different database name
> WITH NORECOVERY.
> Then a RESTORE LOG using the backup you just made and then WITH STOPATMARK
> = <time>, STANDBY=filename.
> This will restore the log to whatever time you want. The STANDBY will
> allow you to put the database into read-only mode while you then do a
> select on the table.
> If the data is there, you can then select it back into your live database.
> If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
> HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
> luck.
> If you need more help, let me know via email or here.
>> 3) How can I prevent this to happen again?
> Security.
>>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi Greg,
> I am not that familiar with these items.
> a) how can I find whether the configuration is full-logging or simple
> logging?
> b) what do you mean with 'Take a Transaction Log backup now'? How can I
> find this?
> c) how can I do a RESTORE LOG?
> thanks for your help.
> Oscar
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:%23qklnHLcHHA.4032@.TK2MSFTNGP02.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:udXxjALcHHA.5052@.TK2MSFTNGP06.phx.gbl...
>> For a reason that we don't know, one table consisting of about 1200 rows
>> in SQL Server 2005 Express SP2 shows to be empty at once. Since all
>> records were addded within this day, there is no suitable back-up of a
>> couple of hours ago, I don't know a way to restore the records.
>> 1) Is this a known bug of SQL Server 2005 SP2?
>> Not that I've heard.
>>
>> 1) Is there a way to find out the reason why this happened?
>> Possibly, but may not be entirely worth the effort.
>> 2) Is there a way to restore the deleted rows?
>> MAYBE!
>> If you are in full-logging and not simple logging, you may be able to do
>> the following:
>> I assume you have a full backup from say last night?
>> Take a Transaction Log backup now. (I'd probably add a NO_TRUNCATE option
>> so that you don't truncate the log, just in case).
>> Anyway, do a RESTORE from the full backup to a different database name
>> WITH NORECOVERY.
>> Then a RESTORE LOG using the backup you just made and then WITH
>> STOPATMARK = <time>, STANDBY=filename.
>> This will restore the log to whatever time you want. The STANDBY will
>> allow you to put the database into read-only mode while you then do a
>> select on the table.
>> If the data is there, you can then select it back into your live
>> database.
>> If not, do a new RESTORE LOG with an earlier STOPATMARK, etc.
>> HOWEVER, if you don't have FULL Recovery enabled, you're probably out of
>> luck.
>> If you need more help, let me know via email or here.
>> 3) How can I prevent this to happen again?
>> Security.
>>
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi Greg,
> I am not that familiar with these items.
>
Ok, that'll make this a bit tougher, but let's see what we can do.
> a) how can I find whether the configuration is full-logging or simple
> logging?
Hmm, since you're running Express, you'll have to use SQLCMD to connect to
the database.
So, connect to your database and then type:
SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
GO
Let me know if this returns a 0 or a 1.
(You want a 0).
If this is 1, then there's very little if anything that can be done.
If it's 0, we might be able to help.
> b) what do you mean with 'Take a Transaction Log backup now'? How can I
> find this?
> c) how can I do a RESTORE LOG?
> thanks for your help.
> Oscar
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Meanwhile I've already started the first step to do a transaction log
backup.
However it shows now '(Restoring)' after the database name instead of the
back up.
It has run for almost an hour now and I don't know how long this will last.
I've also looked into another SQL server where almost the same database
resides for testing purposes.
After typing your selection query it shows one record with two fields. In
the first field it shows a '1' and in the second field it shows a 'NULL'
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
> Ok, that'll make this a bit tougher, but let's see what we can do.
>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
> Hmm, since you're running Express, you'll have to use SQLCMD to connect to
> the database.
> So, connect to your database and then type:
> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
> GO
> Let me know if this returns a 0 or a 1.
> (You want a 0).
> If this is 1, then there's very little if anything that can be done.
> If it's 0, we might be able to help.
>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
>>
>> Ok, that'll make this a bit tougher, but let's see what we can do.
>>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
>> Hmm, since you're running Express, you'll have to use SQLCMD to connect
>> to the database.
>> So, connect to your database and then type:
>> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
>> GO
>> Let me know if this returns a 0 or a 1.
>> (You want a 0).
>> If this is 1, then there's very little if anything that can be done.
>> If it's 0, we might be able to help.
>>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
> bericht news:umc6evLcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> "Oscar" <oku@.xs4all.nl> wrote in message
>> news:%23YSytULcHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi Greg,
>> I am not that familiar with these items.
>>
>> Ok, that'll make this a bit tougher, but let's see what we can do.
>>
>> a) how can I find whether the configuration is full-logging or simple
>> logging?
>> Hmm, since you're running Express, you'll have to use SQLCMD to connect
>> to the database.
>> So, connect to your database and then type:
>> SELECT DATABASEPROPERTY('databasename', 'IsTruncLog');
>> GO
>> Let me know if this returns a 0 or a 1.
>> (You want a 0).
>> If this is 1, then there's very little if anything that can be done.
>> If it's 0, we might be able to help.
>>
>> b) what do you mean with 'Take a Transaction Log backup now'? How can I
>> find this?
>> c) how can I do a RESTORE LOG?
>> thanks for your help.
>> Oscar
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Meanwhile I've already started the first step to do a transaction log
> backup.
If it's showing a 1, then it's doing SIMPLE RECOVERY mode and the log gets
truncated at each checkpoint so you're probably out of luck.
> However it shows now '(Restoring)' after the database name instead of the
> back up.
Can you type exactly what commands you used.
A BACKUP should NOT in any way change a database name to "restoring" so I
suspect you're doing something else here.
> It has run for almost an hour now and I don't know how long this will
> last.
> I've also looked into another SQL server where almost the same database
> resides for testing purposes.
> After typing your selection query it shows one record with two fields. In
> the first field it shows a '1' and in the second field it shows a 'NULL'
> Oscar
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||These are the steps :
Tasks -> Back-up
Configured : Backup type : Transaction Log
Options : Transaction Log : Back up the tail of the log and leave the
database in the restoring state (in order to deselect the option 'Trancate
the transaction log' as you advised me.
Then pressed OK after which is shows now 'Restoring'.
Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
think the process takes too much time (about three hours now), it could have
been crashed. Task Manager doesn't show any noticeable activity. Can/should
I stop this proces now?
with respect to the logging setting, the results of the query look like the
'1' responds to a record number and the second field is the value we're
looking for and it's 'null'.
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:eQ8B2gMcHHA.1508@.TK2MSFTNGP06.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:uNfZAOMcHHA.1244@.TK2MSFTNGP04.phx.gbl...
>> Meanwhile I've already started the first step to do a transaction log
>> backup.
> If it's showing a 1, then it's doing SIMPLE RECOVERY mode and the log gets
> truncated at each checkpoint so you're probably out of luck.
>
>> However it shows now '(Restoring)' after the database name instead of the
>> back up.
> Can you type exactly what commands you used.
> A BACKUP should NOT in any way change a database name to "restoring" so I
> suspect you're doing something else here.
>
>> It has run for almost an hour now and I don't know how long this will
>> last.
>> I've also looked into another SQL server where almost the same database
>> resides for testing purposes.
>> After typing your selection query it shows one record with two fields. In
>> the first field it shows a '1' and in the second field it shows a 'NULL'
>> Oscar
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:OY2Xv6McHHA.4488@.TK2MSFTNGP03.phx.gbl...
> These are the steps :
> Tasks -> Back-up
> Configured : Backup type : Transaction Log
> Options : Transaction Log : Back up the tail of the log and leave the
> database in the restoring state (in order to deselect the option 'Trancate
> the transaction log' as you advised me.
> Then pressed OK after which is shows now 'Restoring'.
> Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
> think the process takes too much time (about three hours now), it could
> have been crashed. Task Manager doesn't show any noticeable activity.
> Can/should I stop this proces now?
Ok. Sounds like you're using the GUI. In cases like this I REALLY REALLY
recommend you don't and you execute the commands by hand. I'm not 100% sure
what GUI is doing under the covers. (Though I was able to replicate what you
did with the GUI and cause the same issue. And telling it to generate the
script, I can see what it was doing.)
Anyway, my guess is it completed the backup and you've covered the dialog
box with the OK button. It should take only a minute or two to do that
backup (or error out if it can't.)
> with respect to the logging setting, the results of the query look like
> the '1' responds to a record number and the second field is the value
> we're looking for and it's 'null'.
I'm not sure why you're getting two columns. You should be getting one.
Anyway, before we go further, did you have an existing full backup from last
night (or rather, what is your last full backup?)
> Oscar
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Greg,
my last full back up is of 2 days ago.
Any idea how I can stop or kill the (Restoring)?
Oscar
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> schreef in
bericht news:OwwZnONcHHA.4216@.TK2MSFTNGP02.phx.gbl...
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:OY2Xv6McHHA.4488@.TK2MSFTNGP03.phx.gbl...
>> These are the steps :
>> Tasks -> Back-up
>> Configured : Backup type : Transaction Log
>> Options : Transaction Log : Back up the tail of the log and leave the
>> database in the restoring state (in order to deselect the option
>> 'Trancate the transaction log' as you advised me.
>> Then pressed OK after which is shows now 'Restoring'.
>> Since the mdf file is about 50 MB and the ldf file is about 23 MB only, I
>> think the process takes too much time (about three hours now), it could
>> have been crashed. Task Manager doesn't show any noticeable activity.
>> Can/should I stop this proces now?
> Ok. Sounds like you're using the GUI. In cases like this I REALLY REALLY
> recommend you don't and you execute the commands by hand. I'm not 100%
> sure what GUI is doing under the covers. (Though I was able to replicate
> what you did with the GUI and cause the same issue. And telling it to
> generate the script, I can see what it was doing.)
> Anyway, my guess is it completed the backup and you've covered the dialog
> box with the OK button. It should take only a minute or two to do that
> backup (or error out if it can't.)
>
>> with respect to the logging setting, the results of the query look like
>> the '1' responds to a record number and the second field is the value
>> we're looking for and it's 'null'.
> I'm not sure why you're getting two columns. You should be getting one.
> Anyway, before we go further, did you have an existing full backup from
> last night (or rather, what is your last full backup?)
>
>
>> Oscar
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||"Oscar" <oku@.xs4all.nl> wrote in message
news:e1kYIhNcHHA.4656@.TK2MSFTNGP06.phx.gbl...
> Hi Greg,
> my last full back up is of 2 days ago.
> Any idea how I can stop or kill the (Restoring)?
> Oscar
>
Can you email me at mooregr (at) greenms.com We can probably do this faster
off line.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
2326ac wrote:
Caddre wrote: