10.1 SqlServer backup

First, please refer to Chapter 4 Configuring Instances to configure the corresponding SqlServer instance information for the source Client. Just as a Client has multiple SqlServer instances, it can be configured multiple times.

You can also manually query the SqlServer parameters and later verify the automatic scan results.

Log in to SqlServer Managerment studio:

img

After entering the account password, connect and create a new query.

SqlServer has two authentication methods, namely Windows authentication and SQL Server authentication,

1. Windows authentication, that is, no need to use the account password.

2. SQL Server authentication is to log in with sa account and password;

There are two ways for you to choose.

-- 声明变量
DECLARE @port NVARCHAR(5);
DECLARE @instanceName NVARCHAR(128);
DECLARE @serverName NVARCHAR(128);
DECLARE @installPath SQL_VARIANT;
DECLARE @installPathNVARCHAR NVARCHAR(256);

-- 获取 SQL Server 实例名
SET @instanceName = @@SERVICENAME;

-- 获取服务器名
SET @serverName = @@SERVERNAME;

-- 获取监听端口号
EXEC xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IPAll', 
    N'TcpPort', 
    @port OUTPUT;

-- 获取安装路径
EXEC xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\Microsoft SQL Server\Setup', 
    N'SQLPath', 
    @installPath OUTPUT;

-- 将安装路径转换为 NVARCHAR
SET @installPathNVARCHAR = CONVERT(NVARCHAR(256), @installPath);

-- 查询所有数据库的相关参数
SELECT 
    @port AS TcpPort,
    @instanceName AS InstanceName,
    @serverName + '\' + @instanceName + ', ' + @port AS LoginParameters,
    @installPathNVARCHAR AS InstallPath,
    db.name AS DatabaseName,
    mf.physical_name AS FilePath,
    mf.type_desc AS FileType
FROM sys.databases db
JOIN sys.master_files mf ON db.database_id = mf.database_id;
GO

img

Note that this is only the query result of one SQL Server instance. If you have multiple instances, you need to log in to different instances and query multiple times.

Switch login to different instances, you can modify the server name in the Manage Ment studio login box, usually in the format of server name\ instance name, Port Number, etc. That is, the LoginParameters column in the above query results.

For example, WIN-UEA6VBOA92O\ MSSQLSERVER, 1433 and WIN-UEA6VBOA92O\ MYSQLSERVER, 1444 are two different instances on one Client.

You can also use sqlcmd to log in.

After the administrator opens cmd, execute:

sqlcmd -S localhost\MSSQLSERVER,1433 -U sa -P 123456

The above results can be compared with the automatic scanning results, and the query results should prevail.

After configuring the instance:

img

Start creating a new SQL Server plan from the left navigation bar [Plan] - [Database Backup] - [+], in 5 steps:

The first step is to select the data content.

img

Give the plan a name, select the data type SQL Server, select Client and instance, and the system will automatically scan all databases under that instance (note that the system databases of SQL Server such as master/model/msdb/tempdb have been hidden). The backup data list can be expanded later.

img

After selecting the database you need to backup, click [Next]:

Note that SQL Server data selection is also divided into two levels: instance-level and library-level. When selecting data content, select all or check individual libraries. Unlike MySql, SQL Server can perform full, differential, and log backups using both methods.

SQL Server instance-level backups can also automatically include newly added libraries in the backup plan.

Step two, select storage:

img

Please select the storage pre-configured in Chapter 5 Configuring Storage . Click [Next] to continue:

Step three, set a time plan.

img

Refer to Chapter 6, Section 1, Automatic Trigger of Backup Plan to configure the trigger time of the plan.

SQL Server can combine three planning methods: "Full Only", "Full + Differential", and "Full + Transaction Log".

SQL Server provides three main backup types: full backup, differential backup, and transaction log backup. Each backup type has its unique purpose and advantages, suitable for different backup strategies and recovery needs.

1. Full Backup

Definition

A full backup is a complete backup of the database, including all database files (data files and some log files).

Features

• Contains all data of the database and some transaction logs.

• It can be restored independently without the need for other backup files.

• Usually the largest in size, with the longest backup and recovery time.

Usage scenarios

• Periodic (e.g. weekly or monthly) backups are performed to ensure a complete snapshot of the database.

• When a separate backup file is required to restore the entire database.

• Differential Backup

Definition

Differential backups only contain data that has changed since the last full backup.

Features

• Smaller than a full backup, but will become larger over time.

• It relies on the most recent full backup to recover.

• Recovery requires the most recent full backup and the latest differential backup.

Usage scenarios

• You need to back up more frequently, but you don't want to do a full backup every time.

• Make smaller and faster backups between full backup and recovery.

• Transaction Log Backup

Definition

The transaction log backup contains all transaction log records that have occurred since the last transaction log backup.

Features

• Smallest size, fastest backup and recovery speed.

• There must be a continuous log chain without any interruption.

• It can achieve point-time recovery (PITR) of the database.

• A recent full backup and all transaction log backups are required for recovery.

Usage scenarios

• Frequent backups of the database are required to achieve smaller recovery point objectives (RPOs).

• We hope to recover to a specific point in time when a failure occurs.

Click [Next] to continue:

Fourth step, advanced options:

img

Please refer to Chapter 6, Section 4, Advanced options for backup plans and Section 5, before and after scripts to check.

Click [Submit] to save the policy in the fifth step.

img

You can then refer to Chapter 6, Section 2, Manual Trigger of Backup Plan to run the plan manually. You can also wait for the scheduled automatic trigger of the plan.

img

When using SQL Server for backup, running multiple backup strategies in parallel, such as using AvatarStor backup software and SQL Server's own backup task schedule, can lead to confusion in the backup chain, especially in the case of differential and transaction log backups.

Backup chain

1. Full Backup : Captures a complete snapshot of the database.

2. Differential Backup : Captures all changes since the last full backup.

3. Transaction Log Backup : Captures all transaction log records since the last transaction log backup.

Possible problems

If you have two backup strategies running in parallel (for example, AvatarStor backup software and SQL Server task schedules), and they are both performing full and differential backups, the following issues may arise:

1. Backup chain break : Differential and transaction log backups rely on previous full backups. If a full backup in the task schedule is inserted between the full and differential backups of AvatarStor backup software, it may cause the differential and transaction log backups to fail to recover correctly because they refer to the latest full backup instead of the backup you expected.

2. Recovery issues : When restoring a database, the correct backup chain must be followed. If the backup chain is interrupted by different full backups, recovery can become complicated or even impossible.

Backup chain example

Suppose you have the following backup chain:

1. AvatarStor backup software Full Backup 1

2. AvatarStor Backup Software Differential Backup 1

3. SQL Server Task Schedule Full Backup 2

4. AvatarStor Backup Software Differential Backup 2

5. AvatarStor Backup Software Transaction Log Backup (Log Backup 1)

In this case, if you need to recover the latest data, you must:

1. Restore Full Backup 2

2. Unable to use Differential Backup 2 (because it is based on Full Backup 1)

Solution

To avoid this situation, the following measures can be taken:

1. Use a single backup strategy : Avoid using multiple backup strategies in parallel. Choose a backup method (for example, using AvatarStor backup software or SQL Server task scheduling) and stick to it.

2. Coordinate backup schedules : If you must use multiple backup policies, make sure they are coordinated to avoid performing full backups at the same time. For example, set backup schedules so that AvatarStor backup software and SQL Server tasks schedule backups at different times.

3. Monitor and manage backups : Regularly check and verify backup files to ensure that the backup chain is complete and that backup files can be restored correctly.

4. After the conflict does occur: AvtarStor will automatically upgrade the relevant incremental backup to full backup to solve the problem of inaccurate benchmark. There is a prompt similar to this in the Client log:

You can also use the following SQL statements to detect the current backup status of the SQL Server system:

USE msdb;
GO

SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    CASE 
        WHEN bs.type = 'D' THEN 'Full'
        WHEN bs.type = 'I' THEN 'Differential'
        WHEN bs.type = 'L' THEN 'Log'
        ELSE bs.type
    END AS backup_type,
    bmf.physical_device_name
FROM
    backupset bs
INNER JOIN
    backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY
    bs.backup_start_date DESC;

Query explanation:

• MSDB Database: This is one of the SQL Server system databases that stores information about backup and maintenance schedules.

• Backupset table: Contains information about each backup operation.

• Backupmediafamily table: contains information about the backup media.

• Type column: Indicates the backup type (D = database (full) backup, I = differential backup, L = log backup).

• physical_device_name Column: Displays the path or device name of the backup file.

Steps:

1. Log in to SQL Server :

   1. Open SQL Server Management Studio (SSMS) or log in to SQL Server using a command-line tool such as sqlcmd.

2. Execute query :

   1. Paste the above SQL statement in the new query window of SSMS and execute it, or save the query as a .sql file and execute it using sqlcmd.

Example result:

database_name	backup_start_date	backup_finish_date	backup_type	physical_device_name
MyDatabase	2023-07-01 02:00:00.000	2023-07-01 02:15:00.000	Full	C:\Backups\MyDatabase_Full.bak
MyDatabase	2023-07-02 02:00:00.000	2023-07-02 02:05:00.000	Differential	C:\Backups\MyDatabase_Diff.bak
MyDatabase	2023-07-02 04:00:00.000	2023-07-02 04:01:00.000	Log	C:\Backups\MyDatabase_Log.trn
...

Check cross backup

• By looking at the backup_start_date and backup_finish_date , you can check if there are any crossovers.

• physical_device_name Can help you identify whether the backup file was generated by AvatarStor.