Joseph Michael Pesch
VP Programming

Windows Task Scheduler Create Web Request (Web Site Keep Alive)

by 26. March 2016 18:09

Add Windows Schedule and from the "Actions" tab, select "Start a program" dropdown and enter "powershell" (without the quotes) in the "Program/script:" text box then enter a command like the one below in the "arguments" text box:

-Command "Get-Date > c:\pathToLogFile.txt; $req = [System.Net.WebRequest]::Create(\"http://url\"); $res = $req.GetResponse(); $str = $res.GetResponseStream(); $rdr = new-object System.IO.StreamReader $str; $dat = $rdr.ReadToEnd(); $dat >> c:\pathToLogFile.txt; Get-Date >> c:\pathToLogFile.txt;"

Powershell HTTP Request

$r = [System.Net.WebRequest]::Create("http://url/")
$resp = $r.GetResponse()
$reqstream = $resp.GetResponseStream()
$sr = new-object System.IO.StreamReader $reqstream
$result = $sr.ReadToEnd()
write-host $result

Username and passwords
$creds = new-object System.Net.NetworkCredential "username", "password"
$uri = mew-object System.Uri "http://url/"
$credcache = new-object System.Net.CredentialCache
$credcache.Add($uri, "Basic", $creds)
$webrequestobject.Credentials = $credcache

One Liner Version
Powershell -Command "$r = [System.Net.WebRequest]::Create('http://url/'); $resp = $r.GetResponse(); $respstream = $resp.GetResponseStream(); $sr =
new-object System.IO.StreamReader $respstream; $result = $sr.ReadToEnd(); write
-host $result"

Tags:

PowerShell

SQL Backup All User Databases via Windows Task Scheduler

by 22. March 2016 18:14

1) Create the SQL procedure below
2) Add this sqlcmd to a CMD file:
    sqlcmd -S localhost -E -Q "exec master.dbo.prc_BackupUserDatabases"
3) Add this command to the CMD file (to delete files older that X days), sample deletes *.bak files older than 5 days:
    forfiles -p "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup" -s -m *.bak -d -5 -c "cmd /c del @path"
4) Add to windows task scheduler with Action = Start a Program (pointed to the CMD file)

SQL Procedure

USE [master]
GO
create procedure [dbo].[prc_BackupUserDatabases] as
begin
  declare @DatabaseName varchar(50)
        , @BackupFolder sysname = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\';
  declare cur cursor for select name from sys.databases where len(owner_sid) > 1
  open cur
  while 1=1 begin
    fetch next from cur into @DatabaseName
    if @@FETCH_STATUS <> 0 break
    declare @BackupFilePath sysname = @BackupFolder + @DatabaseName + N'_' + CONVERT(char(8), GETDATE(), 112) + '.bak';
    backup database @DatabaseName to disk = @BackupFilePath with init, STATS=10;
  end
  close cur
  deallocate cur
end

Tags:

SQL Server