' maintainwsus.bat
' Backs up and cleans the WSUS server. Brings down IIS to do this so must be ran on off hours.
' the wsusdebugtool must be downloaded from micrsoft and put in c:\program files\Update Services\tools
'6-1-2006  Ch®is B from http://www.wsus.info/forums/lofiversion/index.php?t6144.html
'6-2-2006  Modified to throw away the backup of the WSUS server and added title bar.
' 

SET LogPath=C:\TEMP
SET BackupPath=c:\temp

SET WSUS_DB=%ComputerName%\WSUS
SET ProgPath=%ProgramFiles%\Update Services\Tools

if not exist "%LogPath%\wsus_clean.log" echo Log Created > "%LogPath%\wsus_clean.log"

echo :---------------Begin WSUS Cleanup-------------------: >> %LogPath%\wsus_clean.log
if not exist "%ProgPath%\wsusdebugtool.exe" echo Please Download the Debug tool from http://download.microsoft.com/download/7/7...ebug%20Tool.EXE
if not exist "%ProgPath%\wsusdebugtool.exe" echo ERROR Please Download the Debug tool from http://download.microsoft.com/download/7/7...ebug%20Tool.EXE >> "%LogPath%\wsus_clean.log"
if exist "%ProgPath%\wsusdebugtool.exe" echo %ProgPath%\wsusdebugtool.exe Found. >> "%LogPath%\wsus_clean.log"

echo Starting WSUS Cleanup.... >> "%LogPath%\wsus_clean.log"
date /t >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

echo Stopping Web Service >> "%LogPath%\wsus_clean.log"
NET STOP w3svc >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

echo Begin Deletion of Unneededrevisions >> %LogPath%\wsus_clean.log
echo :: >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
"%ProgPath%\wsusutil.exe" deleteunneededrevisions >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

echo Begin Purge of Unneeded Files >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
"%ProgPath%\wsusdebugtool.exe" /tool:purgeunneededfiles >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

echo Begin OSQL Commands to backup the DB >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
del "%BackupPath%\backup_wsus.bak.old"
ren "%BackupPath%\backup_wsus.bak" backup_wsus.bak.old
Osql -S %WSUS_DB% -E /Q "Backup Database SUSDB to disk = '%BackupPath%\backup_wsus.bak'" >> "%LogPath%\wsus_clean.log"
Osql -S %WSUS_DB% -E /Q "backup log SUSDB with TRUNCATE_ONLY" >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

echo Begin OSQL Commands to Add Indexes if missing >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
Osql -S %WSUS_DB% -E /Q "USE SUSDB BEGIN TRAN IF NOT EXISTS (SELECT * FROM sysindexes where name='nc7DeploymentRevision') BEGIN CREATE NONCLUSTERED INDEX nc7DeploymentRevision ON dbo.tbDeployment(RevisionID, TargetGroupID, ActionID) END COMMIT TRAN" >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

echo Begin OSQL Commands to Truncate tbEventInstance >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
Osql -S %WSUS_DB% -E /Q "USE SUSDB declare @RS as int select @RS=(select count(*) from tbEventInstance) select @RS as tbEventInstanceRowCount if @RS > '3000' truncate table tbEventInstance" >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

echo Begin OSQL Commands to compress the DB >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
Osql -S %WSUS_DB% -E /Q "use SUSDB dbcc shrinkdatabase ('SUSDB')" >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

echo Starting Web Services >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
net start w3svc >> "%LogPath%\wsus_clean.log"
echo :: >> "%LogPath%\wsus_clean.log"

date /t >> "%LogPath%\wsus_clean.log"
time /t >> "%LogPath%\wsus_clean.log"
echo :---------------Cleanup Finished -------------------: >> "%LogPath%\wsus_clean.log"

SET ScriptPath=
SET BackupPath=
SET WSUS_DB=

:END