SQL Server DBA Tutorial 41-How to Recover Suspect or Recovery Pending Databases in SQL Server

In this video you will learn how to recover suspect or recovery pending databases using SQL Server Management studio, it illustrates how to find the reason why SQL Server database went in suspect or recovery pending state, how to resolve File related issues. How to find why SQL Server database went in suspect mode using DBCC commands, also talk about last options to restore database incase it is corrupted and can't be brought online using normal ways.

Blog post link for the video with script

Visit our website to check out SQL Server DBA Tutorial Step by Step

[coolabhi]

Nice lesson !!

Few corrections-
1) 'State_desc' stands for state-description not state-descending.

2) you don't need to restart whole SQL Server, You may bring DB to Offline then correct the file and bring it Online back.

[sridharramalingam]

That is just one casue and will be quite rare compare to database curruption causing it to go in susuopect mode, the more frequent solutuion will be to repair database in emergency and single user mode.

[ManoloVivar]

thank you, what I ve seen several times is that sql server needs the data folder to be accesible to "anyone", then after re starting the server, everything was fine again!

[sridharramalingam]

That is just one cause and will be quite rare compared to database curruption causing it to go in suspect mode, the more frequently applied solution will be to repair database in emergency and single user mode.

[morampudistorage]

i seen all your vedio sessions except alwayson, good explanation&very easy to understand.
can you please upload sessions related to general issues&solutions for those issues in high availability concepts, its really helpful to me.

[AkiraH]

Can you post like if your trn logs is full in one of the harddisk what is the workaround add/delete/transaclogs or extend database?

[alnahari]

Can you help me I have same problem Recovery pending database?

[ermiastefera]

Thank you so much, this was very helpful for me.

[kenbee]

Thank you so much for this TechBrothersIT

[krishnegowdakv]

Can you please let me know,
Whether we can fix without restarting the sql instance.

[anthonygarcia]

can you teach me how to repair .ndf file?

[theundefeatedclub]

few steps missing … like DBCC CHECKDB ( DB, repair….)

[eltransformadomusic]

Pasé el susto de mi vida. Trabajo para un hospital, pensé que había perdido todo porque tampoco aparecía nada. Le di ver objetos escondidos y ataché y boom todo lo más bien. No se qué pasó

[costechtdpa]

EXEC sp_resetstatus [YourDatabase];



ALTER DATABASE [YourDatabase] SET EMERGENCY



DBCC checkdb([YourDatabase])



ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE



DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)



ALTER DATABASE [YourDatabase] SET MULTI_USER