You can use this SQL script to backup particular database to a specific location,
-----------------------------------------------------------------
-- ***** Backup Database Script - by Nitin Sawant nitin@nitinsawant.com *****
--
-- Modify only two variables
-- 1. @sourcedatabaseName
-- 2. @restoreDirectory
--
-- Date Created: Jul 05, 2022
-----------------------------------------------------------------
use master
go
declare @backupFileName varchar(100), @restoreDirectory varchar(100),
@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
@databaseDataFile varchar(100), @databaseLogFile varchar(100),
@databaseName varchar(100), @execSql nvarchar(1000), @sourcedatabaseName nvarchar(1000)
-----------------------------------------------------------------
-- Set the name of the database to restore
set @sourcedatabaseName = 'Gitea'--Source DB
-----------------------------------------------------------------
-- Set the path to the directory containing the database backup
set @restoreDirectory = 'C:\Softwares\gitea db backup\' -- such as 'c:\temp\', the directory where the .bak of source DB will be created
--Take a Fresh DB backup
DECLARE @Dt DATETIME
SET @Dt = GETDATE()
DECLARE @BkAddress NVARCHAR(1000)
--the database name and today's date
set @backupFileName = @restoreDirectory + @sourcedatabaseName + '_'+DATENAME ( day , @Dt )+''+LEFT(DATENAME ( month , @Dt ),3)+''+DATENAME ( year , @Dt )+''+REPLACE(RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7),':','.')+'-'+DATENAME ( s , @Dt )+'.bak'
PRINT 'Creating backup of the database at:' + @BkAddress
EXEC('Backup Database '+@sourcedatabaseName+' To Disk ='''+ @backupFileName+'''')
go
using SQL Server Agent you can schedule execution of this script to periodically take backup of SQL DB.
