Alter Database recovery model & Shrink log, DB dynamically

I normally do on daily basis these operations in GUI

• Alter database to simple recovery model
• shrink log file
• shrink database file
• Alter database to full recovery model

I can understand script but can't write one.

I just want to give DB name then script must execute above tasks and possible show log of it

Declare @DBname varchar(50) Set @DBname = 'AdventureWorks'

After these 2 lines

Please try below Dynamic T-SQL statements:

DECLARE @exec nvarchar(max);
DECLARE @db  nvarchar(max) = N'AdventureWorks';

SET @exec = N'ALTER DATABASE ' + QUOTENAME(@db) + N' SET RECOVERY SIMPLE';
EXEC sp_executesql @exec;

SET  @exec = N'DBCC SHRINKFILE (' + QUOTENAME(@db) + N')';
EXEC sp_executesql @exec;

SET  @exec = N'DBCC SHRINKFILE (' + QUOTENAME(@db) + N'_log)';
EXEC sp_executesql @exec;

SET @exec = N'ALTER DATABASE ' + QUOTENAME(@db) + N' SET RECOVERY FULL';
EXEC sp_executesql @exec;
    

Add this code to a SQL Server Agent job, that will keep a history of the executions. You can later make a right click on the job and see its execution history.

The database files will be shrinked to the size specified when the files were created.

Make sure AdventureWorks and AdventureWorks_log are the logical names of the database files.