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)';
On this page of the site you can watch the video online Automatically Backup your server using SQL Server Jobs with a duration of hours minute second in good quality, which was uploaded by the user Eduardo Santana 09 October 2021, share the link with friends and acquaintances, this video has already been watched 78 times on youtube and it was liked by 2 viewers. Enjoy your viewing!
