10.2 SqlServer recovery

Launch a recovery wizard and give the recovery job a name:

img

Select local recovery or different machine recovery , select the target instance to be restored, and expand the content to be restored:

img

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 library-level backup , it can be expanded to a specific database here.

Note For SQL Server cross-machine recovery, the target cross-machine must also be instance configured first.

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

Please do not have a database with the same name on the target instance!!! Or choose a new database name to restore during recovery.

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

1. Length limit

The length of the database name cannot exceed 128 characters.

1. First character requirement

Database names cannot start with a number. The first character must be a letter, _, or @.

1. Allowed characters

Database names can contain letters, numbers, underscores (_), @signs, dollar signs ($), and well signs (#).

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

1. Prohibited characters

Database names cannot contain the following special characters: slashes (/), backslashes (), colons (:), asterisks (*), question marks (?), double quotes ("), less than (<), greater than (>), vertical bars (|), commas (,), semicolons (;), single quotes ('), and spaces.

1. Other notes

If the database name contains spaces or other special characters (such as dashes -), it can be enclosed in square brackets ([]), such as [My Database].

Summary: Although SQL Server allows the use of many symbols in database names, it is recommended to use simple and non-special character names to avoid potential problems and maintain good readability.

SQL Server supports cross-version database recovery, but some specific requirements and limitations need to be noted. Here are some key points:

Main principles

1. From old version to new version : You can restore an old (lower version) database to a new (higher version) SQL Server instance. For example, you can restore a SQL Server 2012 database to SQL Server 2019.

2. From New to Old Version : You cannot directly restore a new (higher) version of a database to an older (lower) SQL Server instance. For example, you cannot restore a SQL Server 2019 database directly to SQL Server 2012.

Specific requirements and precautions for cross-version recovery

1. Backup and restore version compatibility :

   1. Backup files can be restored in higher versions of SQL Server, but not in lower versions. For example, a backup of SQL Server 2014 can be restored to SQL Server 2016, but not to SQL Server 2012.

2. Database compatibility level :

   1. After recovery, the compatibility level of the database may need to be adjusted to accommodate the features and performance improvements in the new version of SQL Server.

3. Functional and performance differences :

   1. Newer versions of SQL Server may contain new features and performance improvements, and compatibility of applications with the new version needs to be tested and verified after recovery.

4. Database repair :

   1. After restoring to a newer version of SQL Server, you may need to run DBCC CHECKDB to verify database consistency and fix any problems that may exist.

5. Upgrade scripts and stored procedures :

   1. During the recovery process, it may be necessary to upgrade scripts and stored procedures to ensure that they work properly in the new version of SQL Server.

You can use the following command to check and change the database compatibility level:

-- 检查数据库兼容级别
SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

-- 更改数据库兼容级别（例如设置为SQL Server 2017兼容级别）
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 140;
--不同版本的 SQL Server 对应的兼容级别如下：
--SQL Server 2000 (8.x): 80
--SQL Server 2005 (9.x): 90
--SQL Server 2008 (10.0.x) 和 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

Use DBCC to check the database.

DBCC CHECKDB('YourDatabaseName');

Click [Next] to continue:

img

Restore to the specified path: If left blank here, the path will be restored to the original backup environment by default.

For example, if the original data file path is: C:\ Program Files\ Microsoft SQL Server\ MSSQL10_50. MSSQLSERVER\ MSSQL\ DATA, then the recovery will also be restored to this path.

Note that if it is a local recovery, then there is no problem, and often this path exists; if it is a different machine recovery, then it is not necessarily the case.

Therefore, for cross-machine recovery, it is recommended to specify the path here.

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

Coverage refers to overwriting an existing database with the same name. All access links to the database with the same name must be interrupted before overwriting can be performed. After overwriting, all previous data in the database with the same name will be lost. One way to ensure that access links are interrupted is to put the database in the offline state.

--将MyDatabase置于offline状态：
ALTER DATABASE [MyDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE;
--查询当前数据库状态：
SELECT name, state_desc FROM sys.databases WHERE name = 'MyDatabase';
--重新上线：
ALTER DATABASE [MyDatabase] SET ONLINE;

Rollback: Refers to whether to apply transaction logs. There are two options: rollback and no rollback. Usually, if a backup of a SQL server is as follows:

1、FullBackup 1

2、IncmentalBackup 1

3、LogBackup 1

4、LogBackup 2

If you want to restore to the latest state, you should first restore 1, then restore 2, then restore 3 and choose not to roll back, and finally restore 4 and choose to roll back.

It can be seen that after selecting the rollback operation, any subsequent recovery operations cannot be continued, that is, rollback is the end point of the recovery operation.

Usually "no rollback" is applied to the case of continuous recovery of transaction logs, but for AvatarStor backup software, these recovery operations are fully automated. You can choose to start recovery from any backup job of transaction logs/differential backups, and the program will automatically complete all recovery operations for you, without the need to operate in the order of full backup, differential backup, and last log.

Although less commonly used, recovery jobs do support pre- and post-script .

Click [Submit] to save the recovery job.

img

Click [View Recovery Job List] to go to [Job] - [Recovery Job] - [Running] to view the jobs being restored.

img