Posted on

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
Posted on

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"]

}