Recovery

Dear Guys...
Let's assume I have a Database Name "Production" and I have two Harddisk C
and D. In C drive I have production.mdf file and in D Drive I have
production.ldf. After a month C drive has been corrupted but D drive is
still live so now I am in a position to recover whole data or not through
production.LDF file?
Thanks
NOOR
Only if you have a backup of the database. Then you make a backup of the
current transaction log, and then restore the database and the transaction
log in order.
Jacco Schalkwijk
SQL Server MVP
"Noor" <noor@.ngsol.com> wrote in message
news:eirYAKpYEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Dear Guys...
> Let's assume I have a Database Name "Production" and I have two Harddisk C
> and D. In C drive I have production.mdf file and in D Drive I have
> production.ldf. After a month C drive has been corrupted but D drive is
> still live so now I am in a position to recover whole data or not through
> production.LDF file?
> Thanks
> NOOR
>
|||Hi,
With LDF file you can not recover the database. LDF file contains only
active transactions.
In the otherway if you have the MDF file you can try recovering the
database using sp_attach_single_file_db (see books online).
In your case only way to recover the database is using the last full
database backup. After that you can apply the transaction log backups,
provided if you have taken transaction log backup.
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:eirYAKpYEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Dear Guys...
> Let's assume I have a Database Name "Production" and I have two Harddisk C
> and D. In C drive I have production.mdf file and in D Drive I have
> production.ldf. After a month C drive has been corrupted but D drive is
> still live so now I am in a position to recover whole data or not through
> production.LDF file?
> Thanks
> NOOR
>

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FHDid you read:
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
HTH, jens Suessmeyer,|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribi en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>|||Not sure if you fixed this yet but I just had a db marked to this status and
it simply means that the Log file is knackered. Unfortunately, without ba
ckups you will lose some uncommitted data, but at least the written data wil
l be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF - this can
then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily. I g
uess the problem with the log file is that you never clear it down as a resu
lt of a backup. It makes me sad to see you treating a databas in that way!
:-)
Good luck matey.
Mike

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FH
Did you read:
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
HTH, jens Suessmeyer,
|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribi en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>
|||Not sure if you fixed this yet but I just had a db marked to this status
and it simply means that the Log file is knackered. Unfortunately,
without backups you will lose some uncommitted data, but at least the
written data will be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF -
this can then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily.
I guess the problem with the log file is that you never clear it down
as a result of a backup. It makes me sad to see you treating a databas
in that way! :-)
Good luck matey.
Mike
mikeroberts23
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1201952.html

RECOVER DATABASE WITH CORRUPT LOG FILE

My database was marked 'SUSPECT' as a result of harddisk error and I don't
have any backup since my backup harddisk was broken
I tried using
Sp_resetstatus mydata
DBCC CHECKDB ('mydata', NO INDEX)
GO
It returned:
Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
staturs = 1077936397
(status suspect bit = 256)
For row in sysdatabase for datase ' mydata', the status bit 256 was forced
off and mode was force to 0
warning : you must recover this database prior to access
Server: Msg 926, Level 14, State 1, Line -1074284091
Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server Error log for more information.
When I checked the error log, part of the log was:
Opening file C:\mssql7\data\mydata_Data.MDF
Opening file C:\mssql7\data\mydata_Log.LDF
The log for database 'mydata' is corrupt
Error 9004, severity 23, state:2
Database 'mydata' (database ID 11) could not recover. Contact Technical
Support
Error 3414, Severity 21 state 1
I tried using the following command
ALTER DATABASE mydata
ADD LOG FILE
(NAME = test1log2,
FILENAME= 'C:\mssql7\data\test2log.ldf',
SIZE = 5MB,
MASXIZE = 100MB,
FILEFROWTH = 5MB)
GO
It returned
Database 'mydata' cannot be opened
When I tried detach/attach_single_file database, it returned:
EXEC sp_detach_db @.dbname = 'mydata'
EXEC sp_attach_single_file_db @.dbname = 'mydata',
@.physname = 'c:\mssql7\data\mydata_data.mdf'
Successfully detached database 'mydata'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
server: Msg 9004, Level 23, State 2, Line 1
The log for database 'mydata' is corrupt.
Connection Brokens
Please anyone could help me recover my database
Regards
FHDid you read:
http://www.karaszi.com/sqlserver/info_corrupt_suspect_db.asp
HTH, jens Suessmeyer,|||Hi
Try with sp_add_log_file_recover_suspect_db.
Else you're with problems if don't have recently backup.
Good luck.
FJCG
"FH" <FH@.discussions.microsoft.com> escribió en el mensaje
news:488835F7-3B5D-4278-9A51-121E7EFD2B8C@.microsoft.com...
> My database was marked 'SUSPECT' as a result of harddisk error and I
> don't
> have any backup since my backup harddisk was broken
>
> I tried using
> Sp_resetstatus mydata
> DBCC CHECKDB ('mydata', NO INDEX)
> GO
> It returned:
> Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
> staturs = 1077936397
> (status suspect bit = 256)
> For row in sysdatabase for datase ' mydata', the status bit 256 was forced
> off and mode was force to 0
> warning : you must recover this database prior to access
> Server: Msg 926, Level 14, State 1, Line -1074284091
> Database 'mydata' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server Error log for more information.
>
> When I checked the error log, part of the log was:
> Opening file C:\mssql7\data\mydata_Data.MDF
> Opening file C:\mssql7\data\mydata_Log.LDF
> The log for database 'mydata' is corrupt
> Error 9004, severity 23, state:2
> Database 'mydata' (database ID 11) could not recover. Contact Technical
> Support
> Error 3414, Severity 21 state 1
>
> I tried using the following command
> ALTER DATABASE mydata
> ADD LOG FILE
> (NAME = test1log2,
> FILENAME= 'C:\mssql7\data\test2log.ldf',
> SIZE = 5MB,
> MASXIZE = 100MB,
> FILEFROWTH = 5MB)
> GO
> It returned
> Database 'mydata' cannot be opened
>
> When I tried detach/attach_single_file database, it returned:
>
> EXEC sp_detach_db @.dbname = 'mydata'
> EXEC sp_attach_single_file_db @.dbname = 'mydata',
> @.physname = 'c:\mssql7\data\mydata_data.mdf'
> Successfully detached database 'mydata'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> server: Msg 9004, Level 23, State 2, Line 1
> The log for database 'mydata' is corrupt.
> Connection Brokens
> Please anyone could help me recover my database
> Regards
> FH
>|||Not sure if you fixed this yet but I just had a db marked to this statu
and it simply means that the Log file is knackered. Unfortunately
without backups you will lose some uncommitted data, but at least th
written data will be about. Do the following:
1) Stop the SQL server
2) Copy the database.MDF file to anywhere (just a safety check)
3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
4) Now Use explorer and manually delete the LDF file (LOG)
5) Now run sp_attach_single_file_db @.dbname = 'dbname',
@.physname = 'fullpath\dbname.MDF'
It will create a brand new LDF file in the Same folder as the MDF
this can then be moved to your folder structure using SP_RENAMEDB.
Word of advice though is to run a maintenance plan with a backup daily
I guess the problem with the log file is that you never clear it dow
as a result of a backup. It makes me sad to see you treating a databa
in that way! :-)
Good luck matey.
Mik
-
mikeroberts2
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message1201952.htm