Data corrupted after server crash ?

Our SQL Server 2000 crashed a while a go and i am wondering if this
could cause
data to be corrupted ?
Could someone tell me what the procedure of SQL Server 2000 is after
and or during a server crash ?
Dennis Velders
Dennis
If you do an access to the database , just run BACKUP LOG WITH NOTRUNCATE
option
Then RESTORE the last FULL database and apply all LOGs with NORECOVERY
option except the last one that you did just after the crash but this time
WITH RECOVERY option
For more details please refer to the BOL
"Dennis Velders" <d.velders@.catalpa.nl> wrote in message
news:1141288312.167931.162580@.z34g2000cwc.googlegr oups.com...
> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>
|||Hi
This will depend on what damage has been caused by a crash. SQL Server will
roll back any outstanding transactions when it starts, therefore you database
should be consistent if it comes back online.
If your database does not come online it will depend on what is
corrupted/missing as to what you can recover. The worst case being that you
will have to resort to a backup.
Have you run a DBCC CHECKDB on the database?
John
"Dennis Velders" wrote:

> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>
|||Yes, i did all the checks.
But after a normal shutdown a while ago the data was fine.
So, i have two options for the maybe corrupted data:
1. The Query mechanism of SQL Server is wrong after the crash
2. The Views display the wrong data, vew design problem
Someone is blaming the server for the corruption...
I want to be sure.
|||Hi
If you run checkdb and it indicates not errors, then you may also want to
reindex and update the statistics. If it continues to give different results,
then try running the query in Query analyser and verify that the results are
matching the query and the underlying data.
John
"Dennis Velders" wrote:

> Yes, i did all the checks.
> But after a normal shutdown a while ago the data was fine.
> So, i have two options for the maybe corrupted data:
> 1. The Query mechanism of SQL Server is wrong after the crash
> 2. The Views display the wrong data, vew design problem
> Someone is blaming the server for the corruption...
> I want to be sure.
>

Data corrupted after server crash ?

Our SQL Server 2000 crashed a while a go and i am wondering if this
could cause
data to be corrupted ?
Could someone tell me what the procedure of SQL Server 2000 is after
and or during a server crash ?
Dennis VeldersDennis
If you do an access to the database , just run BACKUP LOG WITH NOTRUNCATE
option
Then RESTORE the last FULL database and apply all LOGs with NORECOVERY
option except the last one that you did just after the crash but this time
WITH RECOVERY option
For more details please refer to the BOL
"Dennis Velders" <d.velders@.catalpa.nl> wrote in message
news:1141288312.167931.162580@.z34g2000cwc.googlegroups.com...
> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>|||Hi
This will depend on what damage has been caused by a crash. SQL Server will
roll back any outstanding transactions when it starts, therefore you database
should be consistent if it comes back online.
If your database does not come online it will depend on what is
corrupted/missing as to what you can recover. The worst case being that you
will have to resort to a backup.
Have you run a DBCC CHECKDB on the database?
John
"Dennis Velders" wrote:
> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>|||Yes, i did all the checks.
But after a normal shutdown a while ago the data was fine.
So, i have two options for the maybe corrupted data:
1. The Query mechanism of SQL Server is wrong after the crash
2. The Views display the wrong data, vew design problem
Someone is blaming the server for the corruption...
I want to be sure.|||Hi
If you run checkdb and it indicates not errors, then you may also want to
reindex and update the statistics. If it continues to give different results,
then try running the query in Query analyser and verify that the results are
matching the query and the underlying data.
John
"Dennis Velders" wrote:
> Yes, i did all the checks.
> But after a normal shutdown a while ago the data was fine.
> So, i have two options for the maybe corrupted data:
> 1. The Query mechanism of SQL Server is wrong after the crash
> 2. The Views display the wrong data, vew design problem
> Someone is blaming the server for the corruption...
> I want to be sure.
>

Data corrupted after server crash ?

Our SQL Server 2000 crashed a while a go and i am wondering if this
could cause
data to be corrupted ?
Could someone tell me what the procedure of SQL Server 2000 is after
and or during a server crash ?
Dennis VeldersDennis
If you do an access to the database , just run BACKUP LOG WITH NOTRUNCATE
option
Then RESTORE the last FULL database and apply all LOGs with NORECOVERY
option except the last one that you did just after the crash but this time
WITH RECOVERY option
For more details please refer to the BOL
"Dennis Velders" <d.velders@.catalpa.nl> wrote in message
news:1141288312.167931.162580@.z34g2000cwc.googlegroups.com...
> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>|||Hi
This will depend on what damage has been caused by a crash. SQL Server will
roll back any outstanding transactions when it starts, therefore you databas
e
should be consistent if it comes back online.
If your database does not come online it will depend on what is
corrupted/missing as to what you can recover. The worst case being that you
will have to resort to a backup.
Have you run a DBCC CHECKDB on the database?
John
"Dennis Velders" wrote:

> Our SQL Server 2000 crashed a while a go and i am wondering if this
> could cause
> data to be corrupted ?
> Could someone tell me what the procedure of SQL Server 2000 is after
> and or during a server crash ?
>
> Dennis Velders
>|||Yes, i did all the checks.
But after a normal shutdown a while ago the data was fine.
So, i have two options for the maybe corrupted data:
1. The Query mechanism of SQL Server is wrong after the crash
2. The Views display the wrong data, vew design problem
Someone is blaming the server for the corruption...
I want to be sure.|||Hi
If you run checkdb and it indicates not errors, then you may also want to
reindex and update the statistics. If it continues to give different results
,
then try running the query in Query analyser and verify that the results are
matching the query and the underlying data.
John
"Dennis Velders" wrote:

> Yes, i did all the checks.
> But after a normal shutdown a while ago the data was fine.
> So, i have two options for the maybe corrupted data:
> 1. The Query mechanism of SQL Server is wrong after the crash
> 2. The Views display the wrong data, vew design problem
> Someone is blaming the server for the corruption...
> I want to be sure.
>

Data base File is suspect

My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
TanweerHi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
.
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer|||Have a look here:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer

Data base File is suspect

My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
Tanweer
Hi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
..
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer
|||Have a look here:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer

Data base File is suspect

My server crashed and when it came back it has database marked as suspect, I
detached the database , delete the log file and try to attach it again using
enterprise manager however it saying it can't do it.
I have no backup of log or database for the day our backup was also not
working.
What I can do to recover the database.
Thanks for the help
TanweerHi,
Why did you delete the transaction log file before analyzing the cause for
suspect? I feel that cause for the suspect is because some
file (MDF or LDF) was using by some other process (backup or anti virus)
during startup. This would have been easily resolved by
running the system proc sp_resetstatus. Now since you deleted the LDF file
from query analyzer you could try sp_attach_single_file_db (see books online
for usage). If this fails then:-
You could try the below steps to make your database online using MDF file
only. Since in this processes the LDF file willnot be used on startup
(Emergency mode) , the data integrity might be an issue. (This step can be
used if you do not have any backups). Once the database become online move
the objects to a new database using DTS.
Steps:
1. Create a new database with the same name and same MDF and LDF files
2. Stop sql server and rename the existing MDF to a new one and copy the
original MDF to this location and delete the LDF files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
6. Restart sql server. now the database will be in emergency mode
7. Create a new database and use DTS to copy the objects and data to new
database.
You can use this new database.
Note:
If you have the backup file, it is always recommended to use the backup file
to restore the database. SO that data integrity will be maintained.
.
Thanks
Hari
SQL Server MVP
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer|||Have a look here:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Tanweer" <Tanweer@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F86755A1-3C2A-4CC8-9463-F2E4E5E5F7D6@.microsoft.com...
> My server crashed and when it came back it has database marked as suspect,
> I
> detached the database , delete the log file and try to attach it again
> using
> enterprise manager however it saying it can't do it.
> I have no backup of log or database for the day our backup was also not
> working.
> What I can do to recover the database.
>
> Thanks for the help
> Tanweer