JetSoft – Jeff Turner

If you select the option in windows event logs to archive old event logs it will create files of the format that event viewer can open.

An event ID 1105 will be created as the first item in the new log if it is the security log, other logs such as forwarded events will cause a 105 event id in the system log.

By selecting to trigger a task when this event is generated we can automatically run a script that moves the archive files to a backup location. The log files compress well and putting them into an NTFS compressed folder should save a lot of space.

A simple cmd script that will move the archive files out.

@set moveto=F:\ServerBackups\SERVERNAME\EventLogs
@date /t 
@time /t 
@%homedir%
@cd "%systemroot%\system32\winevt\logs"
@echo event log has been archived due to size limit - now move to archive storage. 
cd
move archive*.evtx %moveto%

Another option is to use this powershell script, this will move all the archived event log files from the default location to another. It will then “cleanup” the archive location and remove all files more than 30 days old. However it will only remove the files that have been backed up (archive bit cleared).

The location and number of days can be specified on the command line (options to the task), the first option is the name of the directory to move the files (the subfolders, computername and eventlogs need to be created manually first) and the second the number of days old a file must be before it is removed.

The default location is d:\serverbackups and the default number of days is 30. Remember that files will only be removed if they have been backed up or had their archive bit cleared.

param( [string]$archivefolder='D:\ServerBackups', [int32]$limit=30 )
#script to archive event logs from log collecting server. 
$computername=$env:COMPUTERNAME
$sysroot=$env:SystemRoot
$attribute = [io.fileattributes]::archive
$limitdate=(Get-Date).AddDays(-$limit)
$archivefolder="$archivefolder\$computername\EventLogs"
$logfile="$archivefolder\ArchiveEventLogs.Log"
$eventlogfolder="$sysroot\system32\winevt\logs"
$myname=$MyInvocation.InvocationName
# test for existence of target folders
if (( test-path $archivefolder ) -and (test-path $eventlogfolder ) ) 
    { 

    $now=get-date
    Add-Content -Path $logfile "Script $myname started $now"

    #$myeventlogs= Get-ChildItem -path "$eventlogfolder\Arch*.evtx"

    $resultofmove=move-item -passthru -path "$eventlogfolder\Arch*.evtx" -destination $archivefolder

    if ( $resultofmove ) 
        {
        $resultofmove_count=$resultofmove.count
        Add-Content -path $logfile "$resultofmove_count files have been moved to Archive" 
        $resultofmove |  Add-Content -path $logfile 
        }
    else
        {
        Add-Content -path $logfile "No files found to archive." 
        }

    # Delete files older than the $limit.
    $myfiles= Get-ChildItem -Path $archivefolder | Where-Object { !$_.PSIsContainer -and $_.LastWriteTime -lt $limitdate  -and !( (get-itemproperty -path  $_.fullname ).attributes -band $attribute ) } 

    $myfiles_count=$myfiles.count
    if ( $myfiles ) { 
        $resultofdel = $myfiles | Remove-Item -Force
        Add-Content -path $logfile "Delete $myfiles_count log files that have been backed up and are older than $limitdate" 
        $myfiles  |  Add-Content -path $logfile 
        }
    else
        {
        Add-Content -path $logfile "No event log archives found to delete. Needed to be backed up and older than $limitdate" 
        }

    $now=get-date
    Add-Content -Path $logfile "Script $myname finished $now"

    #type $logfile 

    }
else
    {
    write-host -ForegroundColor red "ERROR  $archivefolder or $eventlogfolder do not exist, debugging written to  $env:TEMP\archivecrashlog.txt"
    $myline= $MyInvocation.line
    add-content -path "$env:TEMP\archivecrashlog.txt" "$myline"
    }
   

A scheduled script such as this which has hard coded the name of the sql command file, set it to run in the folder containing the backups

echo %0 starting 
date /t 
time /t 
dir 
sqlcmd -S localhost\caserver -i "%programfiles%\custom\sql_express_database_name_backup.sql"

Which calls a SQL commands file containing this which contains the hard coded name of the database and location of backup files.

BACKUP DATABASE [My_Database] TO  DISK = N'E:\Backup\My_Database' WITH NOFORMAT, INIT,  NAME = N'My_Database-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Microsoft have a much more powerful script referenced here

Technet How to schedule and automate backups of SQL Server databases in SQL Server Express