Database is Recovery Pending in sql server

If the Database LOG file is  missing in SQL Server then Database is going to be RECOVERY PENDING mode.

First run the sql statement below check to find the name of the database with status

ALTER DATABASE [AdventureWorks] SET MULTI_USER

Msg 5120, Level 16, State 101, Line 24

Unable to open the physical file "D:\MSSQL\DATA\AdventureWorks_log.ldf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5181, Level 16, State 5, Line 24

Could not restart database "AdventureWorks". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 24

ALTER DATABASE statement failed.

-- Database LOG file is  missing

AdventureWorks_log D:\MSSQL\DATA\AdventureWorks_log.ldf

Use master

go

ALTER DATABASE [AdventureWorks] REBUILD LOG ON(

NAME ='AdventureWorks_log' ,

FILENAME='D:\MSSQL\DATA\AdventureWorks_log.LDF' )

Warning: The log for database 'AdventureWorks' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

-- Now Database is in Restricted_user mode change to Multiuser

ALTER DATABASE [AdventureWorks] SET MULTI_USER

sp_helpdb [AdventureWorks]

Generate User Name and Role Name from all databases

 SET NOCOUNT ON

DECLARE @DBName VARCHAR(255)

DECLARE @sql NVARCHAR(500)

CREATE TABLE  #dbTable

(DBName  varchar(50) null ,

UserName varchar(100) null,

RoleName varchar(100) null)

DECLARE dbCursor CURSOR FOR

SELECT name AS DbName

FROM sys.databases

WHERE name not in( 'master', 'msdb' , 'tempdb', 'model'  )

        AND state=0 

 

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'USE ' + @DBName  

--Print @DBName

EXEC (@sql)

INSERT INTO  #dbTable (DBName, [UserName], RoleName )

SELECT @DBName as DBNames, u.[name] AS [UserName], r.[name] AS RoleName 

FROM sys.database_principals u 

JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id 

JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id

AND u.[name] NOT IN('dbo')

ORDER BY u.[name]

FETCH NEXT FROM dbCursor INTO @DBName

END

CLOSE dbCursor

DEALLOCATE dbCursor

SELECT * FROM #dbTable

--Drop Table #dbTable

GO