MSSQL实现远程数据备份到网络硬盘的实践

1. 引言

随着数据量越来越大，保护数据安全性问题变得越来越重要。而数据备份是保护数据的一个最基本且最有效的方法之一。在这篇文章中，我们将介绍如何使用MSSQL Server实现远程数据备份到网络硬盘的实践。这种方法可以帮助我们更好地保护我们的数据，并且可以提供更快的恢复速度和更好的应对灾难的能力。

2. 步骤

2.1 配置网络硬盘

首先，需要在网络上配置一台专门用于备份的计算机，并在其上挂载一个网络硬盘。然后，可以通过Windows资源管理器连接到网络硬盘，并访问其共享目录。

2.2 创建备份目录

为了使MSSQL Server能够将数据库备份文件存储在网络硬盘上，需要在共享目录中为备份创建一个目录。在访问网络硬盘的计算机上创建一个名为“Backup”的文件夹作为备份目录。

2.3 配置MSSQL Server

现在，我们需要配置MSSQL Server以允许将备份文件写入网络硬盘。首先，我们需要修改SQL Server的服务账户（SQL Server服务运行的用户帐户）的权限，以允许其访问网络驱动器。可以按以下步骤完成此操作：

USE master 

GRANT EXECUTE ON xp_cmdshell TO [domain\user]
GO 

然后，在SQL Server Management Studio (SSMS) 中，右键单击服务器的名称，选择“属性”，然后转到“高级”选项卡。在“配置文件”下找到“备份目录”选项。在这里，我们需要指定先前创建的备份目录的完整 UNC 路径。

接下来，我们需要创建一个备份任务，它将负责从数据库中输出备份文件，并将其写入网络硬盘。可以使用SQL Server的“维护计划向导”来创建此任务。使用向导创建的任务将在每天指定的时间自动运行，并将数据库备份文件写入先前指定的目录。创建下面代码中的过程，ValidateDatabaseBackupLocation和usp_Backup_Database。

USE [master]

GO
/****** Object:  StoredProcedure [dbo].[ValidateDatabaseBackupLocation]    Script Date: 11/11/2019 11:31:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ValidateDatabaseBackupLocation]
AS
/**************************************************************/
/* Author:    Brandon Williams
/* Name:      Validate Backup Directory
/* Purpose:   Checks to see if the backup directory exists.
/**************************************************************/
BEGIN
    IF (SELECT TOP 1 EXISTS(select 1 from sysobjects where name = N'ValidPath')) = 0
    BEGIN
        EXEC sp_executesql N'CREATE TABLE ValidPath ([IsDirectory] BIT)'
    END
    ELSE
    BEGIN
        TRUNCATE TABLE ValidPath
    END
    DECLARE @cmd VARCHAR(1000)
    SET @cmd = ''Dir "' + (SELECT backup_destination FROM msdb.dbo.backupset) + '"''
    INSERT INTO ValidPath
    EXEC master..xp_cmdshell @cmd
    IF (SELECT TOP 1 [IsDirectory] FROM ValidPath) <> 1
    BEGIN
        RAISERROR (''Invalid backup directory '', 16, 1) WITH LOG
    END
END
GO
/*************************************/
/* Author:    Brandon Williams
/* Name:      Backup_Database
/* Purpose:   Backup a single database.
/*************************************/
CREATE PROCEDURE [dbo].[usp_Backup_Database] 
    @database_name SYSNAME, 
    @local_backup_path NVARCHAR(512),
    @with_init BIT,
    @description NVARCHAR(255) = NULL,
    @log_stats BIT = 0
AS  
SET NOCOUNT ON
SET XACT_ABORT ON 
DECLARE @DOS_command NVARCHAR(1000)
DECLARE @backup_file NVARCHAR(512)
-- Create new backup file  
SET @backup_file = REPLACE(@local_backup_path + @database_name + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '_' + 
                         REPLACE(CONVERT(CHAR(8),GETDATE(),108),':','') + '.bak','''','''''')
DECLARE @expiry_date DATETIME
SELECT @expiry_date = backup.expiration_date
FROM msdb.dbo.backupset AS backup
WHERE (backup.database_name = @database_name AND backup.type = 'D'
AND      backup.is_copy_only = 0)
ORDER BY backup.backup_finish_date DESC
DECLARE @needs_compression BIT
DECLARE @old_database_compression_setting BIT
SET @needs_compression = CAST(SERVERPROPERTY('IsXTPSupported') AS BIT)
IF (@needs_compression = 0)
BEGIN
    SELECT TOP 1 @old_database_compression_setting = [compatibility_level] FROM sys.databases
    WHERE [name] = @database_name
    IF (@old_database_compression_setting >= 130)
    BEGIN
        SET @needs_compression = 1
    END
END
-- Backup database to disk 
IF (@needs_compression = 1)
BEGIN
    BACKUP DATABASE @database_name TO DISK = @backup_file WITH COMPRESSION, INIT
    IF (@description IS NOT NULL)
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = @description, 
            @level0type = N'Schema', @level0name = dbo, -- SCHEMA  
            @level1type = N'Table',  @level1name = @database_name + '_BACKUP_HISTORY' -- OBJECT NAME --(some random value just for a place holder)
    END
END
ELSE
BEGIN
    BACKUP DATABASE @database_name TO DISK = @backup_file WITH INIT
    IF (@description IS NOT NULL)
    BEGIN
        EXEC sp_addextendedproperty 
            @name = N'MS_Description', 
            @value = @description, 
            @level0type = N'Schema', @level0name = dbo, -- SCHEMA  
            @level1type = N'Table',  @level1name = @database_name + '_BACKUP_HISTORY' -- OBJECT NAME --(some random value just for a place holder)
    END
END
EXEC ValidateDatabaseBackupLocation
IF @log_stats = 1
BEGIN 
    SELECT 
        TOP 1 percentage_complete, 
        estimated_completion_time, 
        [command] 
    FROM sys.dm_exec_requests 
    WHERE command LIKE 'BACKUP DATABASE%'+@database_name+'%' 
END 
GO

2.4 部署维护任务

通过创建维护计划来使用创建的过程，向系统添加一个新任务。创建此任务时，可以设置其自动在每天指定的时间点运行，以便定期在网络硬盘上备份数据库。

2.5 测试备份和恢复

一旦备份任务正在运行，可以使用以下部分来测试备份文件是否正确生成并且可以恢复：

使用SSMS中的“还原数据库”向导来还原备份文件，并验证该过程是否成功。

在原始数据库上进行一些更改，然后重新运行备份任务。在使用还原向导将备份文件还原到一个其他位置之前，使用SSMS查看此文件以验证它包含最新的数据。

3. 结论

通过实现MSSQL Server远程备份到网络硬盘，可以更好地保护我们的数据并提供更好的应对灾难的能力。我们建议您使用此过程备份生产数据库并在需要时恢复其数据。备份文件可以通过网络传输或复制到其他地点进行多重备份，从而降低了数据丢失的风险。