T-SQL script to create SQL Job for daily database backup

This script will create an SQL Server Agent job, which will take daily database backup to a folder with date appended in the filename.


DECLARE @job varchar(100) = 'Backup_testdb_daily' -- Name of Job
DECLARE @db varchar(100) = 'testdb' -- DB to backup
DECLARE @bakfile varchar(100) = 'd:\_temp\' + @db -- Backup file path

DECLARE @date varchar(8) = '20180720' -- Job Start date
DECLARE @time varchar(8) = '135400' -- Job run time. Eg: Run At 23rd hour

--------------------------------------------------------------------

DECLARE @cmd varchar(200) = CONCAT ('DECLARE @bakfile varchar(200) = ''' , @bakfile , ''' + ''_'' + convert(varchar(100),GetDate(),112) + ''.bak'';') +

CONCAT('BACKUP DATABASE ', @db, ' TO DISK = @bakfile');

USE msdb

EXEC dbo.sp_add_job
@job_name = @job;

EXEC sp_add_jobstep
@job_name = @job,
@step_name = 'Backup database',
@subsystem = 'TSQL',
@command = @cmd

EXEC sp_add_jobschedule
@job_name = @job,
@name = 'DB Backup Schedule',
@freq_type = 4, -- daily
@freq_interval = 1,
@active_start_date = @date,
@active_start_time = @time

EXEC dbo.sp_add_jobserver
@job_name = @job,
@server_name = @@SERVERNAME

Get Last Modified Date without downloading the file in PowerShell

Compatibility: v5.x


$zipfile = "https://www..../xyz/test.zip"

add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
"@

$AllProtocols = [System.Net.SecurityProtocolType]'Ssl3,Tls,Tls11,Tls12'
[System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

#$res = Invoke-WebRequest -Uri $zipfile

$webRequest = [System.Net.HttpWebRequest]::Create($zipfile)
$webRequest.Method = "GET"
[System.Net.HttpWebResponse]$webResponse = $webRequest.GetResponse()

Write-Output $webResponse.LastModified

Getting SQL Server result set in PowerShell

Compatibility: v5.x
If you are a .NET developer then you are already familiar with this code Smile


$connectionString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorks2016CTP3"
$dt = New-Object System.Data.DataTable
$da = New-Object System.Data.SqlClient.SqlDataAdapter("SELECT TOP 10 * FROM Person.Address", $connectionString)
$da.Fill($dt)

Write-Output $dt.Rows[0]["AddressLine1"]

# or

ForEach ($dr in $dt.Rows) {
Write-Output $dr["AddressLine1"]
}

Send an Office365 SMTP mail in .NET/C#

Sending a mail using Office365 is no big deal. It is just similar to how you send a mail via any SMTP server. But you will have to mind the SMTP host, port number and remember to enable SSL in the code. Here goes the code snippet:


using (MailMessage message = new MailMessage())
{
message.From = new MailAddress("praveen@YourO365Domain.com");
message.To.Add("praveen@yourmail.com");
message.Subject = "test mail";
message.Body = "<h1>test</h1>";
message.IsBodyHtml = true;

SmtpClient client = new SmtpClient("outlook.office365.com", 587);

client.Credentials = new NetworkCredential("praveen@YourO365Domain.com", "passwordhere");
client.EnableSsl = true;
client.Send(message);
}