SQL Server数据库备份（异机）-mysql教程-PHP中文网

SQL Server数据库备份（异机）

php中文网

发布： 2016-06-07 14:59:22

原创

1937人浏览过

简单的远程异机备份数据库功能，通过这个存储过程，讲远程其他机器上的数据库备份到本地。其主要原理为： 1.通过XP_CMDSHELL执行Windows命令，将本机的共享目录映射为远程机器的网络驱动器。 2.通过SQL脚本将数据库备份到网络驱动器中。 3.通过XP_CMDSHELL执

简单的远程异机备份数据库功能，通过这个存储过程，讲远程其他机器上的数据库备份到本地。其主要原理为：

1.通过XP_CMDSHELL执行Windows命令，将本机的共享目录映射为远程机器的网络驱动器。

2.通过SQL脚本将数据库备份到网络驱动器中。

3.通过XP_CMDSHELL执行命令删除映射的网络驱动器。

使用的时候注意开启XP_CMDSHELL配置（见存储过程注释部分）。

<span>/*</span><span>*****************************************************************************************************
**  功能：远程异机备份数据库
**  作者：Danny,Li
**  日期：2013-09-04
**    ---------------------------------------------------------------------------------------------------
**    eg：EXEC [proc_RemoteBackupDB] 'DataBaseName','\DannyPcShareFolder','domaindanny','123456'
**    
******************************************************************************************************</span><span>*/</span>
<span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>proc_RemoteBackupDB</span><span>]</span>
    <span>@databaseName</span> <span>varchar</span>(<span>50</span>),            <span>--</span><span> 需要备份的数据库名称</span>
    <span>@shareFolderPath</span> <span>varchar</span>(<span>250</span>),        <span>--</span><span> 共享目录路径（如：\DannyPcShareFolder）</span>
    <span>@shareFolderAccount</span> <span>varchar</span>(<span>50</span>),    <span>--</span><span> 共享目录读写权限帐户名（如：domaindanny）</span>
    <span>@shareFolderPassword</span> <span>varchar</span>(<span>50</span>)    <span>--</span><span> 共享目录读写权限帐户密码</span>
<span>AS</span>
<span>BEGIN</span>
    <span>--</span><span> 启用 XP_CMDSHELL 配置</span>
    <span>--</span><span>SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1</span>
    <span>--</span><span>RECONFIGURE</span>
    <span>--</span><span>GO</span>
    <span>--</span><span>SP_CONFIGURE 'XP_CMDSHELL', 1</span>
    <span>--</span><span>RECONFIGURE</span>
    <span>--</span><span>GO</span>
    
    <span>DECLARE</span> <span>@sqlStr</span> <span>VARCHAR</span>(<span>1000</span><span>);
    
    </span><span>--</span><span> 映射网络驱动器</span>
    <span>SET</span> <span>@sqlStr</span> <span>=</span> <span>'</span><span>EXEC MASTER..XP_CMDSHELL </span><span>''</span><span>NET USE L: </span><span>'</span><span>+</span> <span>@shareFolderPath</span> <span>+</span><span>'</span><span> "</span><span>'</span><span>+</span> <span>@shareFolderPassword</span> <span>+</span><span>'</span><span>" /USER:</span><span>'</span><span>+</span> <span>@shareFolderAccount</span> <span>+</span><span>'''</span><span>;</span><span>'</span><span>;
    
    </span><span>--</span><span> 备份数据库</span>
    <span>DECLARE</span> <span>@BackupFile</span> <span>VARCHAR</span>(<span>200</span><span>);
    </span><span>SET</span> <span>@BackupFile</span> <span>=</span> <span>@databaseName</span> <span>+</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>100</span>), <span>GETDATE</span>(), <span>12</span>) <span>+</span> <span>'</span><span>.BAK</span><span>'</span><span>;
    </span><span>SET</span> <span>@sqlStr</span> <span>=</span> <span>@sqlStr</span> <span>+</span> <span>'</span><span> BACKUP DATABASE </span><span>'</span><span>+</span> <span>@databaseName</span> <span>+</span><span>'</span><span> TO DISK = </span><span>''</span><span>L:</span><span>'</span><span>+</span> <span>@BackupFile</span> <span>+</span><span>'''</span><span> WITH INIT;</span><span>'</span><span>;
    
    </span><span>--</span><span> 删除网络驱动器映射</span>
    <span>SET</span> <span>@sqlStr</span> <span>=</span> <span>@sqlStr</span> <span>+</span> <span>'</span><span> EXEC MASTER..XP_CMDSHELL </span><span>''</span><span>NET USE L: /DELETE</span><span>''</span><span>;</span><span>'</span><span>;
    
    </span><span>--</span><span> 执行SQL语句</span>
    <span>PRINT</span>(<span>@sqlStr</span><span>);
    </span><span>EXEC</span>(<span>@sqlStr</span><span>);
</span><span>END</span>

登录后复制