HELP : all records of one table have been deleted!

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...
> Not that I've heard.
>
> Possibly, but may not be entirely worth the effort.
>
> 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.
>
> 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:%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...
> Ok, that'll make this a bit tougher, but let's see what we can do.
>
> 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.
>
>
> --
> 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: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: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...
> 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.
>
> 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.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>