Find Backup History from SQL Server Database Server

The below scripts which allow you to find SQL Server Database Last night backup history. If you want see more files in this list please change date.

SELECT 

 [Server_name],

 [Database_name],

 [Type] AS Backup_type,

 case

    when [type]= 'D' THEN 'Full Backup'

when [type]= 'I' THEN 'Incremental Backup'

when [type]= 'L' THEN 'Log Backup'

 end  as Backup_types ,

 CAST(backup_size /1048576 AS DECIMAL (10,2))  AS [Backup_Size (MB)],

 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))  

AS  [Compressed_Backup_Size (MB)],

 CAST(compressed_backup_size/1048576 AS DECIMAL (10,2))/1024  AS  [Compressed_Backup_Size (GB)],

 100- ((compressed_backup_size/backup_size)*100)   AS 'Compressed%',   

 [backup_start_date],

 [backup_finish_date]

FROM msdb.dbo.backupset

WHERE [Type]='D'

  and backup_finish_date> getdate()-1 -- '2025-04-01 00:00:00.000'

ORDER BY backup_set_id desc

Could not find the Database Engine startup handle. Error code: 0x851A0019

Installing SQL Server 2022 on Azure Virtual machine I have encountered an issue

Action required:
Use the following information to resolve the error, uninstall this feature, and then run the setup process again.

Feature failure reason:
An error occurred during the setup process of the feature.

Error details:
§ Error installing SQL Server Database Engine Services Instance Features

Could not find the Database Engine startup handle.
Error code: 0x851A0019

Solution:

Run cmd  prompt on this below command check Drive sectorinfo

C:\Fsutil fsinfo sectorinfo E:

Make sure to have 512 bytes to 4096 bytes allocated for the Drive which master.mdf file is installed

Shrining Database Transaction Log file is not working Transaction Log file size from 42 GB to 100MB

It is interesting to fix one of major database issue which causing a problem to reduce Transaction Log file size from 42 GB to 100MB.

Database file (MDF) 760MB

Database Log file (LDF) 42GB

 -- STEP 1

DBCC Shrinkfile ('Adventuredb_Log', 100)

GO

-- not working above statement as we expected

I took database backup followed by Transaction Log backup then again tried not working neither T-SQL nor SSMS.

What a day

 DBCC Shrinkfile ('Adventuredb_Log', 100)

GO

 -- STEP 2

  DBCC OPENTRAN()

Transaction information for database 'Adventuredb'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (73283:414:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- STEP 3

select log_reuse_wait_desc,* from sys.databases  where name = 'Adventuredb'

log_reuse_wait_desc 

REPLICATION

EXEC sp_removedbreplication  'Adventuredb' 

--Commands completed successfully.

select log_reuse_wait_desc from sys.databases where name = 'Adventuredb'

log_reuse_wait_desc

NOTHING

Finally i have managed shrink the log file to 100MB

Microsoft SQL Server 2025

 Announcing Microsoft SQL Server 2025: Enterprise AI-ready database from ground to cloud

Announcing Microsoft SQL Server 2025: Enterprise AI-ready database from ground to cloud - Microsoft SQL Server Blog

Scripting All Agent Jobs Using SQL Server Management Studio

You have to Press the F7 key so the Object Explorer Details window appears like below. Select jobs you want to scripts and Right click to Generate scripts.

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 @sqlStm NVARCHAR(500)

CREATE TABLE #dbTable

(

ServerName  varchar(200) ,

DBName varchar(255) ,

UserName varchar(100) ,

RoleName varchar(100) 

)

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 @sqlStm = 'USE ' + @DBName  

--Print @DBName

--EXEC (@sqlStm)

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

EXECUTE('USE ' + @DBName + '; INSERT INTO  #dbTable (ServerName , DBName, [UserName], RoleName ) SELECT @@ServerName , db_name(), u.[name] , r.[name]

     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') 

FETCH NEXT FROM dbCursor INTO @DBName

END

CLOSE dbCursor

DEALLOCATE dbCursor

SELECT * FROM #dbTable

 DROP TABLE #dbTable

GO