Скачать или смотреть Automatically Backup your server using SQL Server Jobs

Automatically Backup your server using SQL Server Jobs

-- Step 1 : Execute without the job.

DECLARE @Directory NVARCHAR(255) = N'/var/opt/mssql/data/' ;
DECLARE @BackUpFullName NVARCHAR(255) = @Directory + N'Full-' + FORMAT(GETDATE(), 'yyyy-MM-dd-HH-mm-ss') + '.bak';

BACKUP DATABASE [dbTestJobs]
TO DISK = @BackUpFullName
WITH
NOFORMAT,
INIT,
NAME = N'dbTestJobs-Full Database Backup'
GO

-- Step 2 : Create the job.

USE [msdb]
GO

DECLARE @jobId BINARY(16);

EXEC msdb.dbo.sp_add_job
@job_name=N'BackupWeekly',
@enabled=1,
@owner_login_name=N'sa',
@job_id = @jobId OUTPUT;

EXEC msdb.dbo.sp_add_jobstep
@job_id=@jobId,
@step_name=N'InsertEvery1Second',
@step_id=1,
@retry_attempts=5,
@retry_interval=5,
@subsystem=N'TSQL',
@command=N'
USE dbTestJobs;
GO
DECLARE @SchoolId INT = (SELECT MAX(SchoolId) + 1 FROM dbo.Schools);
DECLARE @SchoolName VARCHAR(255) = ''Full-'' + FORMAT(GETDATE(), ''yyyy-MM-dd-HH-mm-ss'') + ''.bak'';
insert into dbo.Schools
(
SchoolId,
SchoolName,
CraetedDate
)
values
(
@SchoolId,
@SchoolName,
GETDATE()
);
',
@database_name=N'dbTestJobs';

EXEC msdb.dbo.sp_update_job
@job_id = @jobId,
@start_step_id = 1;

EXEC msdb.dbo.sp_add_jobschedule
@job_id=@jobId,
@name=N'EverySeconds',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=2,
@freq_subday_interval=10;

EXEC msdb.dbo.sp_add_jobserver
@job_id = @jobId,
@server_name = N'(local)';