AvatarStor Cloud Backup User Manual

Concept

Explanation

Tenants

Users who use AvatarStor backup cloud.

Client

It is installed on the tenant's server that requires backup. It also refers to each server that needs to be backed up. This represents the "From" in the entire backup process of "From-What-To-Where-and-When."

Server level

Provides SaaS services for AvatarStor Backup Cloud.

Page

Front-end page for tenant registration, backup and recovery management.

Storage

The destination for writing tenant data. This represents the "Where" in the entire backup process of "From-What-To-Where-and-When."

Instance

The database name installed on the client, along with its related path configuration and local login credentials, are essential parameters for the backup and recovery process. These parameters, combined, are referred to as an instance. The backup system needs the instance to identify which databases are present on the client. This represents the "What" in the entire backup process of "From-What-To-Where-and-When."

Backup policy

The specific data content of the backup, its running schedule, data retention period, and other parameters are collectively referred to as the backup strategy. This strategy encompasses all the parameters of the backup process "From-What-To-Where-and-When," along with the method for managing data expiration.

Scheduled time

The moment when the strategy is triggered. This represents the "When" in the entire backup process of "From-What-To-Where-and-When."

Tip

You can also directly click the Google logo at the bottom of the login homepage to sign in with your Google account. Your Google account will serve as your tenant account.

After unsubscribing, you will not be able to resubscribe for two weeks.

Tip

Please compare the MD5 values of the downloaded installation package.

The installation path is limited to a combination of English letters, numbers, underscores, and spaces. Installation in directories with Chinese or other language characters will fail and roll back.

AvatarStor Backup Cloud consists of five services: AvsDB, AvsAgent, AvsMaster, AvsClient, and AvsStorage. During the startup or shutdown process, the cloud icon will briefly turn red. It will turn blue once all five services have fully started or stopped. If the cloud icon remains red for an extended period, it indicates that one or more of these services are in an abnormal state.

PowerShell
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

Install the latest PowerShell for new features and improvements! https://aka.ms/PSWindows

PS C:\Users\Administrator> avsmgt status
2024-09-15 11:57:30 - INFO - Avsdb is running and listening on port 50000.
2024-09-15 11:57:30 - INFO - Avsagent is running and listening on port 59888.
2024-09-15 11:57:30 - INFO - Avsmaster is running and listening on port 59881.
2024-09-15 11:57:30 - INFO - Avsclient is running and listening on port 59882.
2024-09-15 11:57:30 - INFO - Avsstorage is running and listening on port 59883.
PS C:\Users\Administrator> avsmgt stop
2024-09-15 11:57:37 - INFO - AvsStorage stoped successfully.
2024-09-15 11:57:38 - INFO - AvsMaster stoped successfully.
2024-09-15 11:57:39 - INFO - AvsClient stoped successfully.
2024-09-15 11:57:46 - INFO - AvsAgent stoped successfully.
2024-09-15 11:57:49 - INFO - AvsDB stoped successfully.
PS C:\Users\Administrator> avsmgt start
2024-09-15 11:57:55 - INFO - Parsed host: proxyserver.cloud9.avatarstor.com
2024-09-15 11:57:55 - INFO - Parsed port: 19001
2024-09-15 11:57:55 - INFO - Network is available on proxyserver.cloud9.avatarstor.com:19001.
2024-09-15 11:57:59 - INFO - AvsDB started successfully.
2024-09-15 11:57:59 - INFO - AvsAgent started successfully.
2024-09-15 11:58:00 - INFO - AvsClient started successfully.
2024-09-15 11:58:01 - INFO - AvsMaster started successfully.
2024-09-15 11:58:01 - INFO - AvsStorage started successfully.
PS C:\Users\Administrator>

PowerShell
PS C:\Users\Administrator> avsmgt
Usage: avsmgt <action> [<service_name>]
PS C:\Users\Administrator> avsmgt stop avsclient
2024-09-15 12:02:34 - INFO - avsclient stoped successfully.
PS C:\Users\Administrator> avsmgt start avsclient
2024-09-15 12:02:43 - INFO - avsclient started successfully.

Note that if you start or stop services individually, AvsDB must be started first, followed by AvsAgent, and then the other three services. The service_name is not case-sensitive.

Bash
[root@localhost soft]# wget https://clientdownloadcloud9.avatarstor.com/linux/avs_linux.tar.gz
--2024-09-02 16:25:33-- https://clientdownloadcloud9.avatarstor.com/linux/avs_linux.tar.gz
Resolving clientdownloadcloud9.avatarstor.com (clientdownloadcloud9.avatarstor.com)... 2606:4700:3034::6815:24fa, 2606:4700:3034::ac43:c99d, 172.67.201.157, ...
Connecting to clientdownloadcloud9.avatarstor.com (clientdownloadcloud9.avatarstor.com)|2606:4700:3034::6815:24fa|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 240721990 (230M) [application/x-gzip]
Saving to: ‘avs_linux.tar.gz’

avs_linux.tar.gz 100%[====================================================================================================================================================>] 229.57M 69.4MB/s in 3.4s

2024-09-02 16:25:36 (68.4 MB/s) - ‘avs_linux.tar.gz’ saved [240721990/240721990]
 

Bash
[root@localhost soft]# tar zxvf avs_linux.tar.gz
avs_client_server_linux_x64_install_V6.0.8.1236.sh

Bash
[root@localhost soft]# ./avs_client_server_linux_x64_install_V6.0.8.1236.sh
----------------------------------------------------------------------------------
Welcome to Install AvatarStor Client Server for Linux-x64 System
----------------------------------------------------------------------------------
Do you want to Install Client to the default path(/usr/local/).[Y/N]?
y
Client Service will be Installed To The Directory:"/usr/local/"
Unpacking the AvatarStor Client Server Install Packet...
----------------------------------------------------------------------------
Installing...
Created symlink from /etc/systemd/system/multi-user.target.wants/avsbackup.service to /usr/lib/systemd/system/avsbackup.service.
Do you want to register this client now?.[Y/N]?
y
Please Enter The Authorization Code:eyJyZG0iOjY2NzYzNiwidXNlcm5hbWUiOiJqYWNraWUubGlmZW5nQGdtYWlsLmNvbSJ9
Successfully login.
Starting MySQL.. SUCCESS!
Starting the Avatar Client daemon
Starting the Avatar Storage daemon
Starting the Avatar Master daemon
Starting the Avatar Agent daemon
----------------------------------------------------------------------------
AvatarStor Client Service has been installed!
 

Bash
Do you want to register this client now?.[Y/N]?
n
After the installation is complete, please execute the following script to register this client.
### /usr/local/avs/agent/scripts/login.sh authorization_code ###
----------------------------------------------------------------------------
AvatarStor Client Service has been installed!
[root@localhost avs]# ls
agent client db distro-release master scripts storage uninstall.sh
[root@localhost avs]# /usr/local/avs/agent/scripts/login.sh
Please Enter The Authorization Code:eyJyZG0iOjY2NzYzNiwidXNlcm5hbWUiOiJqYWNraWUubGlmZW5nQGdtYWlsLmNvbSJ9
Login Successful.
Starting MySQL... SUCCESS!
Starting the Avatar Client daemon
Starting the Avatar Storage daemon
Starting the Avatar Master daemon
Starting the Avatar Agent daemon
 

Tip

To download the installation package via the command line on Linux, you will need wget. If your Linux system does not have the wget package installed, follow the instructions below:

Debian/Ubuntu:

apt install wget

CentOS/RHEL:

For CentOS 7/8:

yum install wget

For CentOS 8/9:

dnf install wget

Other Linux Distributions:

Generally, use:

sudo install wget

Replace <package-manager> with the package manager used by your distribution, such as apt, yum, dnf, pacman, zypper, etc.

After installation, you can run the following command to check if the installation was successful:

wget --version

Bash
[root@localhost ~]# cd /usr/local/avs/
[root@localhost avs]# ls
agent client db distro-release master scripts storage uninstall.sh
[root@localhost avs]# ./scripts/avs.sh stop
Stopping the Avatar Client daemon
Stopping the Avatar Storage daemon
Stopping the Avatar Master daemon
Stopping the Avatar Agent daemon
Shutting down MySQL. SUCCESS!
[root@localhost avs]# ./scripts/avs.sh start
Starting MySQL... SUCCESS!
Starting the Avatar Client daemon
Starting the Avatar Storage daemon
Starting the Avatar Master daemon
Starting the Avatar Agent daemon

The default installation directory is:/usr/local/avs/

Bash
[root@localhost ~]# cd /usr/local/avs/
[root@localhost avs]# ls
agent client db distro-release master scripts storage uninstall.sh
[root@localhost avs]# ./uninstall.sh
Do you want to Uninstall AvatarStor Client Service [Y/N]?
y
Stopping the Avatar Client daemon
Stopping the Avatar Storage daemon
Stopping the Avatar Master daemon
Stopping the avataragent Server daemon
Shutting down MySQL. SUCCESS!
Removed symlink /etc/systemd/system/multi-user.target.wants/avsbackup.service.
AvatarStor Client Uninstall succeeded!
[root@localhost avs]#

Tip

If the client has multiple MySQL instances or different types of databases installed, you can select from the drop-down menu under [Instance Name].

Tip

If you're not satisfied with the scan results, you can manually input the values you believe are correct. Currently, the scan results have over 90% accuracy. If you find any inaccuracies, please email us at [email protected] so we can continue to improve our service.

Note that in the MySQL account password system, the account is formatted as “user”@"host". The accounts root@localhost and root@"ip" are two different accounts. Here, we need the root@localhost account and its password.

Tip

If an instance is modified, the policies related to that instance must also be modified.

Note that some cloud storage accounts do not have permission to access all buckets and may only have access to a specific bucket. In such cases, please enable the "Specify Bucket" option and enter the name of the designated bucket before validating.

Here are the typical naming requirements for object storage buckets, which may vary slightly between cloud providers but generally follow similar rules:

Global Uniqueness:
Each bucket name must be globally unique, as it serves as the identifier for accessing that storage space.

Naming Length:
Bucket names are usually limited to between 3 and 63 characters (some platforms may have different limits).

Character Restrictions:

Allowed Characters: Lowercase letters (a-z), numbers (0-9), and hyphens (-).

Disallowed Characters: Uppercase letters, underscores (_), and special characters (such as @, #, $, &, etc.).

Hyphen Usage: Hyphens cannot be at the beginning or end of the name and cannot be used consecutively.

Naming Structure:

The name cannot resemble an IP address format (e.g., 192.168.0.1).

Overly generic names like test or dev are usually discouraged to avoid conflicts with other users.

DNS Compatibility:
Some object storage systems (e.g., Amazon S3) use bucket names as part of the URL, so the name must comply with DNS-compatible standards:

No uppercase letters.

Hyphens can only be used in the middle of the name.

Region-Specific Naming:
Certain cloud platforms may require bucket names to be unique within a specific region. It's recommended to use application-specific prefixes or suffixes in naming to avoid conflicts with other users.

The configured storage can be reused by other clients.

Tip

Please set the backup interval according to your needs.

Setting an excessively short backup interval may place pressure on both your business system and storage.

A common backup plan typically includes a full backup once a week, usually on the weekend, and daily incremental backups.

Tip

Running a backup immediately is generally used for testing purposes. Please avoid configuring this schedule for formal backup plans.

Note that only online clients with related backup strategies will have the [Run Immediately] option available.

The "incremental" levels associated with different plugins have slight variations:

Sqlserver does not support incremental backups, only differential backups and transaction log backups.

Mysql supports incremental backups at the instance level, while at the database level, only full backups are available.

Incremental Backup and Differential Backup are two common backup strategies used to optimize the backup process by reducing storage space usage and backup time. Their main differences lie in the scope of the data being backed up and the complexity of the recovery process.

Incremental Backup

Definition: Incremental backup only backs up data that has changed since the last backup (whether it was a full or incremental backup).

Advantages:

Saves storage space: Only changes since the last backup are saved, so the backup size is smaller.

Saves time: Since only a small amount of changed data is backed up each time, the process is quicker.

Disadvantages:

Complex recovery process: To restore data, you first need to restore the last full backup, followed by each incremental backup in sequence until the latest data is restored.

Example:

Sunday: Full backup

Monday: Backup changes since Sunday

Tuesday: Backup changes since Monday

Continue like this each day

Differential Backup

Definition: Differential backup backs up all the data that has changed since the last full backup.

Advantages:

Simpler recovery process: You only need to restore the last full backup and the most recent differential backup to recover the data.

Saves time: The recovery process is faster compared to incremental backups because fewer files need to be restored.

Disadvantages:

More storage space needed: Each differential backup includes all changes since the last full backup, potentially consuming more space than incremental backups.

Backup time increases: As time goes on, the size of each differential backup increases because it captures all changes since the last full backup.

Example:

Sunday: Full backup

Monday: Backup changes since Sunday

Tuesday: Backup changes since Sunday

Continue like this each day

Note that AvatarStor has improved the recovery process for incremental backups by supporting synthetic recovery. This means you can initiate a recovery from the results of any incremental or differential backup at any point in time. The system will automatically locate and combine all related incremental, differential, and full backups to complete the recovery.

Tip

If a specific backup plan is manually triggered and is still running when its scheduled automatic trigger time arrives, the automatically triggered job will be canceled. Similarly, if you manually trigger the same backup plan multiple times, all subsequent runs will be canceled as long as the first triggered job is still in progress. In other words, only one instance of a particular backup plan can run at the same time.

Note that jobs can have two statuses: "Successful" and "Partially Successful." A successful job is marked with a green checkmark, while a partially successful job is marked with a yellow exclamation point. A partially successful job means that some data encountered issues or failed during the backup process. It is still possible to restore the backup, but some data may be incomplete. Please refer to the client logs to analyze and review the specific data that failed.

Only backup plans related to online clients can be viewed, edited, modified, or deleted. For backup plans associated with offline clients, you can only view the plan but cannot edit, modify, or delete it.

Bash
#!/bin/bash
# Send notification email
echo "Backup is about to start" | mail -s "Backup Notification" [email protected]

# Check storage space
REQUIRED_SPACE=1000000
# Required space size (in KB)
AVAILABLE_SPACE=$(df /backup | tail -1 | awk '{print $4}')

if [ "$AVAILABLE_SPACE" -lt "$REQUIRED_SPACE" ]; then
echo "Not enough space for backup" | mail -s "Backup Warning" [email protected]
exit 1
fi

# Stop relevant services
service myapp stop

# Copy the target directory to the backup directory
\cp -r /myapp/datadir /backup && cd /backup && tar --exclude='./backupfile.tar.gz' -czvf backupfile.tar.gz .
 

Bash
#!/bin/bash
# Send notification email
echo "Backup has completed" | mail -s "Backup Notification" [email protected]

# Clean up temporary files
rm -rf /backup/tmp/*

# Restart relevant services
service myapp start

# Validate backup file
if [ -f /backup/backupfile.tar.gz ]; then
echo "Backup file exists" | mail -s "Backup Success" [email protected]
else
echo "Backup file is missing" | mail -s "Backup Failure" [email protected]
fi
 

Please note that pre- and post-scripts can be used not only during the backup process but also during the recovery process.

Local Restore is generally not recommended, especially for instance-level backups of plugins like MySQL or Oracle. This method can lead to actual data overwriting and corruption on the source system. It is crucial to exercise caution and consider alternative restore methods to avoid potential data loss or damage.

Note that when performing a remote restore, the remote machine must be configured with instances of the same version as the original backup set. For example, if the original backup set is for MySQL 8.4.1, the remote client must also be MySQL 8.4.1.

Note that the destination client must have access to the storage where the original backup set is located in order to perform index rebuilding and subsequent recovery tasks.

The filtering methods for folders and files are different.

Windows: filter excluding folders C:\ some\ need\ excluding\

Filter excluding files: C:\ some\ need\ excluding

Linux: filter excluding folders:/some/need/excluding/

Filter excluding files:/some/need/excluding

Only directories ending with "\" or "/" will be recognized as directories by the system.

In the C drive of Windows system, there are some common system files and folders that are not recommended to be backed up through backup software, because these files are either temporary, locked during system operation, or may cause problems during recovery. Therefore, AvatarStor has already filtered out these files and folders by default..

Here are some common examples:

C:\ Windows folder :

Backup strategies should focus on user data, configuration files, and important application data, such as:

The user's home directory /home .

System and application configuration file /etc .

Database files (such as /var/lib/mysql or /var/lib/postgresql ).

Data directory for specific applications.

If it is the latter two, be sure to ensure that the relevant application is in a stopped state when backing up. You can use scripts before and after to start and stop the application.

Tip

VSS (Volume Shadow Copy Service) is a feature in the Microsoft Windows operating system that allows snapshots of volumes to be created while the system is running. These snapshots can be used for backup, file recovery, and other operations. VSS is mainly used to create consistent snapshots, and Microsoft's SqlServer backup defaults to using VSS technology. However, in the practice of file backup, VSS is generally ineffective. In addition, VSS can also be affected by other storage management or security software. If you enable VSS for file backup but the backup fails, you may want to turn off VSS and try again.

If only individual files need to be restored, you can also continue to expand the original backup directory and select individual files to be restored separately.

Pay extra attention to whether the selected new path space is sufficient. If the compression option is enabled when backing up the original backup job, the actual data volume needs to be divided by the compression ratio.

Bash
mysql -u root -p
 

SQL
SHOW VARIABLES LIKE 'port';
SHOW VARIABLES LIKE 'socket';
SHOW VARIABLES LIKE 'basedir';
SHOW VARIABLES LIKE 'datadir';
 

SQL
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+

+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| socket | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+

+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| basedir | /usr/ |
+---------------+------------------+

+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /var/lib/mysql/ |
+---------------+------------------------+
 

Note that MySQL data selection involves two scenarios that will affect the subsequent backup plan options and recovery outcomes.

The first scenario is when you directly click the checkbox in front of the instance name. This action will select all databases under the instance, including MySQL's system libraries (mysql and sys databases), resulting in a full instance-level backup. There are three important points to note about instance-level backups:

Incremental backups are possible, as MySQL incremental backups are based on binlog files, and MySQL binlogs are shared across all databases.

Restoration can only be done to an empty MySQL instance. Since the entire instance is backed up, recovery will be done at the instance level, resulting in full overwrites. This will delete and recreate databases, which will update the target machine’s accounts and passwords (matching them to the source and requiring a restart). Therefore, restoring on the original machine is not advisable (data loss may occur), and it's also not recommended to restore in environments with existing production data.

Newly added databases will automatically be included in the backup plan.

The second scenario is when you select individual databases for recovery, i.e., performing a database-level backup. In this case, keep the following in mind:

Only full backups can be performed, and incremental backups are not possible. This is because it is difficult to extract logs specific to individual databases from MySQL’s shared binlog.

Restoration can be done on either the original machine or a different machine, but you must ensure that no databases with the same name exist on the target instance.

Particular Attention for MySQL Master-Master and Master-Slave Cluster Backups

For MySQL Master-Master or Master-Slave cluster backups, it’s crucial to check whether GTID (Global Transaction Identifier) has been enabled on the data source. If GTID is enabled, we recommend selecting the --set-gtid-purged=OFF option. If you don't choose this option, the restore process may fail due to GTID issues. To check if GTID is enabled, you can use the following command:

Bash
mysql -u username -p -e "
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';
SHOW STATUS LIKE 'Gtid%';
"

mysqldump is a utility provided by MySQL for database backups. When using mysqldump, you can add various parameters to control the details of the backup. Below is a detailed explanation of some commonly used parameters:

--hex-blob

Function: Dumps BLOB fields (such as binary data, images, etc.) in hexadecimal format.

Usage: Ensures that binary data is not corrupted during the dump and restore process due to character set issues.

--opt

Function: Enables various optimization options to improve the speed of backups and restores. Specifically includes --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, and --set-charset.

Usage: Enabled by default and suitable for efficient backups in most cases.

Note: From MySQL 5.6.6 onwards, --opt is enabled by default. It can be disabled using --skip-opt.

--single-transaction

Function: Dumps data within a single transaction to ensure data consistency.

Usage: Suitable for InnoDB tables, allowing a consistent backup without locking the tables. This is especially useful for large databases.

Note: Not applicable for MyISAM tables that require table locks.

--routines

Function: Includes routines such as stored procedures and functions in the dump.

Usage: Ensures that all database logic, including stored procedures and functions, is included in the backup.

--events

Function: Includes events from the event scheduler in the dump.

Usage: Ensures that all scheduled tasks and events are included in the backup.

--set-gtid-purged=OFF

Function: Controls whether GTID (Global Transaction Identifiers) information is included during export.

Usage: For GTID replication clusters, if set to OFF, the SET @@GLOBAL.GTID_PURGED statement is not included. If set to ON or AUTO, it is either included or automatically determined based on the situation.

Note that if the original backup job is an instance-level backup , the recovery content can only be expanded to the instance; if the original backup job is a database-level backup , it can be expanded to a specific database here.

Note that for MySQL remote restore, the target client must also be instance configured first.

There is a risk of data loss with instance-level original client local restore!!

Ensure the target instance does not have a database with the same name!!! Alternatively, select a new database name during recovery. When renaming the database, note that MySQL has specific requirements for database names:

MySQL Database Naming Requirements:

Length Limit: The maximum length for a database name is 64 characters.

Allowed Characters: Database names can contain letters (a-z, A-Z), numbers (0-9), and underscores (_). MySQL distinguishes between uppercase and lowercase characters.

Forbidden Characters: Database names cannot include special characters such as:

Slash (/), backslash (), colon (:), asterisk (*), question mark (?), double quotes ("), less-than (<), greater-than (>), pipe (|), comma (,), semicolon (;), single quote ('), space ( ), exclamation mark (!), and the "@" symbol.

Starting Character: Database names can start with either a letter or a number, but it is recommended to start with a letter to avoid potential compatibility issues.

Case Sensitivity: Whether MySQL distinguishes between uppercase and lowercase in database names depends on the underlying file system:

In most Linux systems, database names are case-sensitive.

On Windows, database names are typically case-insensitive.

**Use of Backticks ()**: If you need to use special characters that don't meet the naming rules (e.g., spaces), you can enclose the database name in backticks ().

For example, to create a database with a space in the name: CREATE DATABASE My Database;.

However, it is not recommended to use such complex naming conventions as it complicates database management.

Summary:

It is recommended to use simple names consisting of letters, numbers, and underscores to ensure compatibility and ease of use. Avoid using special characters and reserved keywords.

The version of the recovery instance must match the major version of the target instance. For example, restoring from "5.7" to "5.7" is allowed, but "5.7" to "8.4" is not. However, restoring between minor versions like "8.4.1" to "8.4.2" is acceptable.

If the instances are not compatible, the recovery log will show an error similar to the following, causing the recovery job to fail immediately: "The version is incompatible and cannot be restored (57 != 84)."

There are two authentication methods in SQL Server: Windows Authentication and SQL Server Authentication.

Windows Authentication: This method does not require a username and password.

SQL Server Authentication: This method requires logging in with the sa account and password.

You can choose either method as you prefer.

SQL
-- Declare variables
DECLARE @port NVARCHAR(5);
DECLARE @instanceName NVARCHAR(128);
DECLARE @serverName NVARCHAR(128);
DECLARE @installPath SQL_VARIANT;
DECLARE @installPathNVARCHAR NVARCHAR(256);

-- Get SQL Server instance name
SET @instanceName = @@SERVICENAME;

-- Get server name
SET @serverName = @@SERVERNAME;

-- Get the listening port number
EXEC xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
N'TcpPort',
@port OUTPUT;

-- Get the installation path
EXEC xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\Setup',
N'SQLPath',
@installPath OUTPUT;

-- Convert the installation path to NVARCHAR
SET @installPathNVARCHAR = CONVERT(NVARCHAR(256), @installPath);

-- Query relevant parameters for all databases
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

 

To switch between different instances, you can modify the server name in the Management Studio login box. Typically, the format is server_name\instance_name, port_number, which corresponds to the LoginParameters column in the query results.

For example, WIN-UEA6VBOA92O\MSSQLSERVER, 1433 and WIN-UEA6VBOA92O\MYSQLSERVER, 1444 represent two different instances on the same client.

You can also use sqlcmd to log in: Open Command Prompt as an administrator and execute:

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

Whether it's a default instance or a named instance, it's a good idea to check the TCP protocol settings to ensure that it's listening on the correct port. This helps avoid connection issues and ensures that the service is running properly. The following points are worth noting:

Default Instance:

The default instance typically listens on TCP port 1433, but you should still verify the TCP/IP settings in SQL Server Configuration Manager to ensure that the port is correctly configured and enabled.

Named Instance:

Named instances don't use a fixed port number and usually rely on dynamic ports. To ensure that clients can connect properly, you can configure a fixed TCP port and make sure it's enabled.

How to Check:

In SQL Server Configuration Manager, locate the TCP/IP protocol for the instance and ensure that the protocol is enabled.

Check the IP Addresses tab to confirm that the listening port is correctly configured (for named instances, you can manually set a static port).

In SQL Server, the login accounts for the two authentication modes are different. Windows authentication uses the NT AUTHORITY\SYSTEM account, while SQL Server authentication uses the sa account. These accounts have different server role properties: the NT account has only the public role and lacks the sysadmin role, whereas the sa account has both the public and sysadmin roles.

It is important to note that backups require the sysadmin role. Therefore, if you choose to log in using Windows authentication, be sure to grant the NT account the sysadmin role to ensure proper permissions for backup operations.

SQL Server Authentication Mode:

SQL Server can be configured to use Windows Authentication only or Mixed Mode Authentication (both SQL Server and Windows). If the server is configured to only allow Windows Authentication, sa (which is a SQL Server account) won't be able to log in.

Solution: Enable Mixed Mode Authentication:

Open SQL Server Management Studio (SSMS) as an administrator.

Right-click the server instance and select Properties.

Note that in SQL Server, data selection can be done at both the instance level and the database level. When selecting data content, you can either select all or choose individual databases. Unlike MySQL, both methods in SQL Server allow for full, differential, and log backups.

Additionally, instance-level backups in SQL Server can automatically include newly added databases in the backup plan.

SQL Server allows for the combination of three backup strategies: "Full only," "Full + Differential," and "Full + Transaction Log."

SQL Server provides three main types of backups: Full Backup, Differential Backup, and Transaction Log Backup. Each type has its unique purpose and advantages, catering to different backup strategies and recovery needs.

Full Backup

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

Characteristics

Contains all the data in the database and part of the transaction log.

Can be restored independently without needing other backup files.

Typically the largest in size, with the longest backup and restore times.

Use Cases

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

When an independent backup file is needed to restore the entire database.

Differential Backup

Definition
A differential backup contains only the data that has changed since the last full backup.

Characteristics

Smaller than a full backup, but grows over time.

Relies on the most recent full backup for restoration.

Requires the latest full backup and the most recent differential backup to restore.

Use Cases

When more frequent backups are needed but full backups are not desired every time.

For smaller, faster backups between full backups and restores.

Transaction Log Backup

Definition
A transaction log backup includes all transaction log records since the last transaction log backup.

Characteristics

The smallest in size, with the fastest backup and restore speeds.

Requires an unbroken chain of logs, with no gaps.

Allows for point-in-time recovery (PITR).

Requires the latest full backup and all transaction log backups to restore.

Use Cases

When frequent backups are needed to achieve a shorter recovery point objective (RPO).

When you need the ability to restore the database to a specific point in time after a failure.

When using SQL Server for backups, if multiple backup strategies (such as using AvatarStor backup software and SQL Server's own backup task schedule) run concurrently, it may lead to confusion in the backup chain, especially in the case of differential and transaction log backups.

Backup Chain

Full Backup: Captures a complete snapshot of the database.

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

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

Potential Issues

If you have two backup strategies running concurrently (e.g., AvatarStor backup software and SQL Server task scheduling), both performing full and differential backups, the following issues may arise:

Backup Chain Breakage: Differential and transaction log backups depend on the previous full backup. If a scheduled full backup is inserted between AvatarStor's full and differential backups, it may lead to issues with differential and transaction log backups, as they will refer to the latest full backup, which may not be the one you expect.

Restore Problems: When restoring a database, you must follow the correct backup chain. If the chain is interrupted by different full backups, the restoration process may become complicated or even fail.

Backup Chain Example

Assume the following backup chain:

AvatarStor Full Backup (Full Backup 1)

AvatarStor Differential Backup (Differential Backup 1)

SQL Server Scheduled Full Backup (Full Backup 2)

AvatarStor Differential Backup (Differential Backup 2)

AvatarStor Transaction Log Backup (Log Backup 1)

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

Restore Full Backup 2

You cannot use Differential Backup 2 (as it's based on Full Backup 1)

Solutions

To avoid such issues, you can take the following actions:

Use a Single Backup Strategy: Avoid running multiple backup strategies in parallel. Choose one method (e.g., AvatarStor backup software or SQL Server's task scheduler) and stick to it.

Coordinate Backup Schedules: If multiple strategies must be used, ensure they are properly coordinated to avoid simultaneous full backups. For example, schedule AvatarStor backups and SQL Server task schedules at different times.

Monitor and Manage Backups: Regularly check and verify backup files to ensure the backup chain is intact and the files can be restored properly.

If Conflict Occurs: AvatarStor automatically upgrades relevant incremental backups to full backups to resolve baseline discrepancies.

SQL
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.typeEND 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;
 

Note that if the original backup job is an instance-level backup, the restore can only be expanded to the instance level. If the original backup job is a database-level backup, it can be expanded to a specific database during the restore process.

Note For SQL Server remote restore, the target client must also be instance configured first.

There is a risk of data loss when restoring on the original client!!

Be sure that no databases with the same name exist on the target instance! Alternatively, choose a new database name during the restoration process.

In SQL Server, user-defined database names must adhere to specific rules and restrictions. Here are some key requirements:

Length Limit

The database name cannot exceed 128 characters.

First Character Requirement

The name must not start with a number. The first character must be a letter, underscore (_), or @.

Allowed Characters

The name can include letters, numbers, underscores (_), the @ symbol, dollar sign ($), and hash (#).

It’s recommended not to use SQL Server keywords as database names.

Forbidden Characters

The following special characters are not allowed in database names: slash (/), backslash (), colon (:), asterisk (*), question mark (?), double quotes ("), less-than (<), greater-than (>), vertical bar (|), comma (,), semicolon (;), single quotes ('), and spaces.

Other Considerations

If the database name contains spaces or special characters like hyphens (-), you can enclose the name in square brackets ([]), for example, [My Database].

Summary

Although SQL Server allows the use of many symbols in database names, it's recommended to use simple names without special characters to avoid potential issues and ensure readability.

SQL Server supports cross-version database restoration, but certain requirements and limitations must be considered. Below are some key points:

Main Principles

Restoring from an older version to a newer version: You can restore a database from an older (lower) version of SQL Server to a newer (higher) version. For example, you can restore a SQL Server 2012 database to a SQL Server 2019 instance.

Restoring from a newer version to an older version: You cannot directly restore a database from a newer (higher) version to an older (lower) version of SQL Server. For example, you cannot directly restore a SQL Server 2019 database to SQL Server 2012.

Specific Requirements and Considerations for Cross-Version Restoration

Backup and Restore Version Compatibility: Backup files from older versions can be restored on newer SQL Server versions, but they cannot be restored on lower versions. For example, a SQL Server 2014 backup can be restored on SQL Server 2016 but not on SQL Server 2012.

Database Compatibility Level: After restoration, the database’s compatibility level may need to be adjusted to align with the features and performance improvements of the newer SQL Server version.

Feature and Performance Differences: Newer versions of SQL Server may contain new features and performance enhancements. After restoration, applications need to be tested and validated for compatibility with the new version.

Database Integrity Check: After restoring to a newer SQL Server version, you may need to run DBCC CHECKDB to verify database consistency and address any issues that may arise.

Upgrade Scripts and Stored Procedures: During the restoration process, you might need to update scripts and stored procedures to ensure they work correctly in the new version of SQL Server.

SQL
-- Check database compatibility level
SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

-- Change database compatibility level (e.g. set to SQL Server 2017 compatibility level)
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 140;
-- The compatibility levels corresponding to different versions of SQL Server are as follows:
--SQL Server 2000 (8.x): 80
--SQL Server 2005 (9.x): 90
--SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x): 100
--SQL Server 2012 (11.x): 110
--SQL Server 2014 (12.x): 120
--SQL Server 2016 (13.x): 130
--SQL Server 2017 (14.x): 140
--SQL Server 2019 (15.x): 150
--SQL Server 2022 (16.x): 160

SQL
DBCC CHECKDB('YourDatabaseName');

Note that if it is a local restore, this usually isn't a major issue as the path often exists. However, for a remote restore, the path may not always be present.

Therefore, for remote restore, it is recommended to specify the path explicitly in this field.

Restore to a new database name: This option is only available when one database is selected for restore.

Overwrite refers to replacing an existing database with the same name. In order to proceed with an overwrite, all access connections to the database must be completely terminated. After the overwrite, all pre-existing data in the database with the same name will be lost.

One way to ensure that all access connections are interrupted is to set the database to the offline state:

SQL
-- Put MyDatabase offline:
ALTER DATABASE [MyDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE;
-- Query the current database status.
SELECT name, state_desc FROM sys.databases WHERE name = 'MyDatabase';
-- Online again:
ALTER DATABASE [MyDatabase] SET ONLINE;
 

Rollback refers to whether or not to apply the transaction logs. There are two options: rollback or no rollback. Typically, if a SQL Server backup follows this sequence:

FullBackup 1

IncrementalBackup 1

LogBackup 1

LogBackup 2

To restore to the latest state, you should first restore 1, then restore 2, followed by 3 without rolling back, and finally restore 4 with rollback.

Once the rollback operation is performed, no further restore operations can be carried out. In other words, rollback marks the end of the restore process.

Generally, the "no rollback" option is used when restoring transaction logs in sequence. However, with AvatarStor backup software, these restoration processes are fully automated. You can choose to start the restoration from any transaction log or differential backup, and the software will automatically complete all necessary restore operations, eliminating the need to follow the traditional sequence of full backup, differential backup, and then transaction log restore.

Bash
sqlplus / as sysdba

Bash
sqlplus username/password@service_name

SQL
SELECT instance_name FROM v$instance;

SQL
SELECT value FROM v$parameter WHERE name = 'oracle_home';

Bash
sqlplus / as sysdba

SQL
SELECT log_mode FROM v$database;

SQL
SHUTDOWN IMMEDIATE;

SQL
STARTUP MOUNT;

SQL
ALTER DATABASE ARCHIVELOG;

SQL
ALTER DATABASE OPEN;

SQL
SELECT log_mode FROM v$database;

SQL
SHOW PARAMETER log_archive_dest;

SQL
ALTER SYSTEM SET log_archive_dest='/your/archive/destination/path';

SQL
ARCHIVE LOG LIST;

In RMAN (Recovery Manager) backups for Oracle, it is generally recommended to include SPFILE, Controlfile, Datafiles, and Archived Logfiles for a comprehensive backup. Here's why each is important:

The target instance for Oracle recovery must have the same instance name as the original backup instance.

PowerShell
Microsoft Windows [Version 10.0.20348.2700]
(c) Microsoft Corporation. All rights reserved.

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Sep 17 10:06:30 2024
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle. All rights reserved.


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 5167381232 bytes
Fixed Size 9865968 bytes
Variable Size 973078528 bytes
Database Buffers 4177526784 bytes
Redo Buffers 6909952 bytes
SQL>

Note that the specified point in time must fall between the end of the last full backup and the end of the current incremental backup.

Auto run will automatically execute the RMAN recovery process for you, provided your target instance is in the startup nomount state. Manual mode, on the other hand, will generate all the recovery scripts for you, which you can execute manually via RMAN.

SQL
Step 1. Restore Initialization File (spfile)

     1: Stop the target database and reboot it to nomount state.

     Reference command (refer to the Oracle manual for details):


     su - oracle
     rman target /
     shutdown
     startup nomount


     2: Execute the following scripts:

     Restore spfile to the specified location:


     RUN {
     ALLOCATE CHANNEL ch1 DEVICE TYPE 'sbt_tape' PARMS="ENV=(PORT=59882,RESTORE_UUID='dc003fec1ba8431382d52df0d4a7d850')";
     RESTORE SPFILE TO '/tmp/spfile.ora' FROM 'oracle_spfile_11_1_1179913545';
     RELEASE CHANNEL ch1;
     }

     Restore spfile to the original location:


     RUN {
     ALLOCATE CHANNEL ch1 DEVICE TYPE 'sbt_tape' PARMS="ENV=(PORT=59882,RESTORE_UUID='dc003fec1ba8431382d52df0d4a7d850')";
     RESTORE SPFILE FROM 'oracle_spfile_11_1_1179913545';
     RELEASE CHANNEL ch1;
     }


Step 2. Restore Control File

     1: Stop the target database and reboot it to nomount state.

     Reference command(refer to the Oracle manual for details):


     su - oracle
     rman target /
     shutdown
     startup nomount


2: Execute the following script:


     RUN {
     ALLOCATE CHANNEL ch1 DEVICE TYPE 'sbt_tape' PARMS="ENV=(PORT=59882,RESTORE_UUID='dc003fec1ba8431382d52df0d4a7d850')";
     RESTORE CONTROLFILE FROM 'oracle_controlfile_13_1_1179913548';
     RELEASE CHANNEL ch1;
     }


Step 3. Restore Database Files and Automatically Restore Necessary Archive Logs

     1: Alter the database to mount state.

     Reference command(refer to the Oracle manual for details):


     alter database mount;


     2: Execute the following scripts:


     RUN {
     ALLOCATE CHANNEL ch1 DEVICE TYPE 'sbt_tape' PARMS="ENV=(PORT=59882,RESTORE_UUID='dc003fec1ba8431382d52df0d4a7d850')";
     ALTER SESSION SET NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss";
     SET UNTIL TIME "TO_DATE('','yyyy-mm-dd hh24:mi:ss')";
     RESTORE DATABASE;
     RECOVER DATABASE UNTIL TIME "TO_DATE('','yyyy-mm-dd hh24:mi:ss')";
     RELEASE CHANNEL ch1;
     }


Step 4. Restore the Archive Logs.

     1: Alter the database to mount state.

     Reference command(refer to the Oracle manual for details):


     alter database mount;


     2: Execute the following script:


     RUN {
     ALLOCATE CHANNEL ch1 DEVICE TYPE 'sbt_tape' PARMS="ENV=(PORT=59882,RESTORE_UUID='dc003fec1ba8431382d52df0d4a7d850')";
     RESTORE ARCHIVELOG FROM logseq=1 until logseq=2 thread 1;
     RELEASE CHANNEL ch1;
     }


Step 5. Open the Database.

     Reference command(refer to the Oracle manual for details):


     alter database open resetlogs;

Only empty storage without any backup sets can be deleted. This is to prevent you from accidentally deleting backup data.

Bash
bash
复制代码
sudo apt update
sudo apt install rinetd

Bash
bash
复制代码
sudo yum install epel-release
sudo yum install rinetd

Bash
sudo nano /etc/rinetd.conf

Plaintext
bindaddress bindport connectaddress connectport

Plain Text
0.0.0.0 8080 192.168.1.100 80

Bash
sudo systemctl start rinetd
sudo systemctl enable rinetd

Bash
sudo systemctl start rinetd
sudo systemctl enable rinetd

Bash
sudo netstat -tuln | grep 8080

Bash
sudo ss -tuln | grep 8080

Bash
logfile /var/log/rinetd.log

Bash
eth0 8080 192.168.1.100 80

Bash
sudo iptables -A INPUT -p tcp --dport 8080 -j ACCEPT

Bash
sudo firewall-cmd --add-port=8080/tcp --permanent
sudo firewall-cmd --reload

SQL
--Check and create login users
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'NT AUTHORITY\ SYSTEM')
BEGIN
CREATE LOGIN [NT AUTHORITY\ SYSTEM] FROM WINDOWS;
END;

--Check if it is already sysadmin.
IF NOT EXISTS (
SELECT 1
From sys. server_role_members srm
JOIN sys.server_principals sp ON srm.member_principal_id = sp. principal_id
JOIN sys.server_principals rl ON srm.role_principal_id = rl.principal_id
WHERE sp.name = 'NT AUTHORITY\ SYSTEM'
AND rl.name = 'sysadmin'
)
BEGIN
--Assign sysadmin role
EXEC sp_addsrvrolemember 'NT AUTHORITY\ SYSTEM', 'sysadmin';
END;

 

SQL
SELECT
SERVERPROPERTY('InstanceName') AS InstanceName,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('ProcessID') AS ProcessID,
local_net_address AS IPAddress,
local_tcp_port AS Port
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL;

SQL
EXEC xp_readerrorlog 0, 1, N'Server is listening on'

Tip

There are two different authentication modes for MySql account passwords, one is mysql_native_password and the other is caching_sha2_password.

Login to MySql and execute (using querying the root account as an example):

mysql> select user,host,plugin from mysql.user where user="root";

+------+-----------+-----------------------+

| user | host | plugin |

+------+-----------+-----------------------+

| root | localhost | caching_sha2_password |

+------+-----------+-----------------------+

1 row in set (0.03 sec)

The plugin column is the password authentication mode used by the current root.

Plain Text
[mysqld]
max_allowed_packet = 1G

Bash
sudo service mysql restart

Bash
net stop mysql
net start mysql