MSSQL中的热备实现：不断保护数据安全

什么是热备？

热备是数据库管理中经常使用的一种备份方式，其目的是在主数据库发生故障时，通过备用数据库尽量快速地恢复数据，确保系统的可用性，从而保障数据安全。

为什么需要热备？

随着数据量的不断增加，数据安全成为了企业信息化建设的重要问题。数据备份和恢复是一种常用的数据安全手段，其中热备是实现数据备份和恢复的重要手段。它可以尽可能快地把备用数据库恢复到主数据库状态，保障系统的可用性。

如何实现热备？

第一步：创建备用数据库

要实现热备，首先需要在不同的服务器上创建主数据库和备用数据库。

CREATE DATABASE main_database 
ON 
(   
    NAME = main_data,        
    FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\main_database.mdf',  
    SIZE = 50MB,        
    MAXSIZE = 300MB,  
    FILEGROWTH = 20MB   
)
LOG ON 
(   
    NAME = main_log,         
    FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\main_log.ldf',        
    SIZE = 20MB,         
    MAXSIZE = 100MB,         
    FILEGROWTH = 10MB        
);

以上代码创建了名为main_database的主数据库，包括数据文件和日志文件。

CREATE DATABASE backup_database 
ON 
(   
    NAME = backup_data,        
    FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\backup_database.mdf',  
    SIZE = 50MB,        
    MAXSIZE = 300MB,  
    FILEGROWTH = 20MB   
)
LOG ON 
(   
    NAME = backup_log,         
    FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\backup_log.ldf',        
    SIZE = 20MB,         
    MAXSIZE = 100MB,         
    FILEGROWTH = 10MB        
);

以上代码创建了名为backup_database的备用数据库，也包括数据文件和日志文件。

第二步：启用日志备份

为了实现热备，需要启用日志备份功能。 SQL Server支持使用备用数据库的日志文件来还原主数据库。

-- 首先，打开数据库中的备份选项。
USE [master]  
GO  
EXEC sp_configure 'show advanced options', 1  
GO  
RECONFIGURE WITH OverRide  
GO  
EXEC sp_configure 'backup compression default', 1  
GO  
RECONFIGURE WITH OverRide  
GO  
-- 如果通过备份数据库向备用数据库恢复，则需要启用 WITH STANDBY，这将自动还原事务日志并保持还原所需的所有打开文件的状态。  
-- 否则，可以使用 WITH NORECOVERY，其中使用的恢复操作将终止，而不恢复用户事务。
BACKUP DATABASE [main_database] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\main_database.bak' WITH  RETAINDAYS = 1, NOFORMAT, NOINIT,  NAME = N'main_database-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10  
GO  

以上代码开启备份选项和启用日志备份功能。

第三步：创建热备机制

在创建了备用数据库和启用了日志备份功能后，就需要设置热备机制，确保数据在主数据库崩溃后尽可能快地恢复到备用数据库中。

可以使用 SQL Server的 Log Shipping 功能来完成热备机制的创建。Log Shipping 允许将事务日志从主数据库传送到备份服务器上，并在备份服务器上还原事务日志。

将备份复制到备用服务器上：

-- 将备份复制到备用服务器上   
exec msdb.dbo.sp_add_log_shipping_secondary_database @secondary_database = N'backup_database',   
                                                        @primary_server = N'.',   
                                                        @primary_database = N'main_database',  
                                                        @restore_delay = 10,  
                                                        @overwrite = 0,  
                                                        @backup_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup',  
                                                        @backup_share = NULL,  
                                                        @backup_retention_period = 1440,  
                                                        @backup_job_name = N'server\Database NameBackupLSCopy/Move Job',  
                                                        @monitor_server = NULL,  
                                                        @monitor_server_security_mode = 1,  
                                                        @monitor_server_login = N'server login/logs',  
                                                        @monitor_server_password = N'monitor_password';

定义备份作业和还原作业：

-- 定义备份作业
USE [msdb]  
GO  
EXEC sp_add_job  
    @job_name = N'Main Database Backup Job',  
    @enabled = 1,  
    @description = N'Backup the Main Database',  
    @owner_login_name = N'sa',  
    @job_id = @jobId OUTPUT  
GO  
-- 将一个步骤添加到作业中   
USE [msdb]  
GO  
EXEC msdb.dbo.sp_add_jobstep  
    @job_id = @jobId,  
    @step_name = N'Backup the Main Database',  
    @step_id = 1,  
    @cmdexec_success_code = 0,  
    @on_success_action = 1,  
    @on_fail_action = 2,  
    @retry_attempts = 0,  
    @retry_interval = 0,  
    @os_run_priority = 0, @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE [main_database] TO DISK=''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\main_database.bak'' WITH NOFORMAT, NOINIT, NAME='+'N''main_database-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD',  
    @proxied_subsystem = NULL,   
    @database_name = N'msdb',  
    @flags = 0;
GO  
-- 定义还原作业
USE [msdb]  
GO  
EXEC sp_add_job  
    @job_name = N'Main Database Restore Job',  
    @enabled = 1,  
    @description = N'Restore the Main Database',  
    @owner_login_name = N'sa',  
    @job_id = @jobId OUTPUT  
GO  
-- 将一个步骤添加到作业中   
USE [msdb]  
GO  
EXEC msdb.dbo.sp_add_jobstep  
    @job_id = @jobId,  
    @step_name = N'Restore the Main Database',  
    @step_id = 1,  
    @cmdexec_success_code = 0,  
    @on_success_action = 1,  
    @on_fail_action = 2,  
    @retry_attempts = 0,  
    @retry_interval = 0,  
    @os_run_priority = 0, @subsystem = N'TSQL',  
    @command = N'RESTORE LOG main_database FROM DISK=''''C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\main_log.trn''''  WITH  FILE = 1, NORECOVERY, NOUNLOAD',  
    @proxied_subsystem = NULL,   
    @database_name = N'msdb',  
    @flags = 0;
GO 

以上代码定义了备份作业和还原作业，并指定了备份和还原的细节。

总结

热备是一种重要的数据备份和恢复手段，可以在主数据库发生故障时保障系统可用性，确保数据安全。在 SQL Server 中，可以通过创建备用数据库、启用日志备份功能和设置热备机制来实现热备。其中，Log Shipping 是 SQL Server 提供的一个重要功能，用于将事务日志从主数据库传送到备份服务器上，并在备份服务器上还原事务日志。