by joe.pesch
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"
1cb12b3f-6e23-477c-bc47-fecc5f973a10|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags:
PowerShell
by joe.pesch
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
11f8820b-b858-487d-bc5f-9ee601e8aeef|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags:
SQL Server