SQL Server Performance Forum – Threads Archive

Transaction logs restore problem!

Hi everyone. I have a problem and I appreciate if I can get some help on this from this forum…I have been using this forum and prepared my logshipping server..so I’m sure that I can get some help here once again. The following is the problem I’m getting when I restored my transaction logs. The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
Server: Msg 3013, Level 16, State 1, Procedure mn_Restore_Log_Backup, Line 85
RESTORE LOG is terminating abnormally. The cause of the problem is the unexpected power failure and now database is in suspected mode. I was wonder if there is a way I can restore my transaction logs from where the power went off. I don’t want to restore the whole database, it’s just the transaction logs I want to restore. I appreciate your time. Thanx gladiatr.

Try to follow the procedure listed in books online to recover the suspect database: Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database. Free disk space and rerun recovery. Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure: USE master
GO
sp_configure ‘allow updates’, 1
GO
RECONFIGURE WITH OVERRIDE
GO After the procedure is created, immediately disable updates to the system tables: sp_configure ‘allow updates’, 0
GO
RECONFIGURE WITH OVERRIDE
GO sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

In my opinion you need to run DBCC command. It will show the exact error why the database is suspected.
Once you know the error you can take action depending on it.
Regards
-Anup

Follow as suggested by Luis to reset the suspect status and then restore full backup from primary to secondary server, lastly log shipping to function properly. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Try to Detach and Reattach . using following SP.. sp_Detach_db
sp_attach_db
Sandy (DB Developer)

If the database is in suspect mode you cannot detach, you must reset the status in order to perform any action on that database. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>