0

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

Shadow777
  • 9
  • 3

1 Answers1

-1

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.

Alberto Morillo
  • 351
  • 1
  • 6
  • Thank you for the response but I already using this daily basis . Now what I expecting I don't want mention db name again and again , instead how about we use a variable. Which stores my db name and does exactly what you wrote.now I just need to mention db name only once So is this possible if so how ? – Shadow777 Oct 25 '22 at 17:06
  • @Shadow777 Let me know if the changes I made to my initial answer is what you are looking for – Alberto Morillo Oct 26 '22 at 03:33