Note: If you want to use Fileshare, update the file path in the scripts. This will maintain the latest transaction log backup with the current state of the database. It is ideal to maintain at least 30 days of transaction data for highly changing databases. This gives us enough time to reconnect to the database in case of a complete hub failure or any similar issues.
The following lines of code is a sample script for a full backup:
DECLARE @backuppath varchar(250),@filename varchar(250);
--Change the path as per what's configured in the system for backpath
SET @backuppath = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\';
SET @filename = CONCAT(@backuppath,'SOADB_FULL.bak')
BACKUP DATABASE SOADB TO DISK = @filename
WITH FORMAT,
NAME = 'Full DATABASE Backup of SOADB';
The following lines of code is a sample script for a logs backup:
DECLARE @backuppath varchar(250),@filename varchar(250);
--Change the path as per what's configured in the system for backpath
SET @backuppath = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\';
-- Perform a Transaction Log Backup of the Database
SET @filename = CONCAT(@backuppath ,'SOADB_backup_' +CONVERT(varchar, GETUTCDATE(), 23)+N'.bak' )
BACKUP LOG SOADB TO DISK = @filename WITH RETAINDAYS = 30,
NOFORMAT, NOINIT,
NAME = 'SOADB Database LOG Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO