<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> 共享目录路径(如:\\DannyPc\ShareFolder)</span>
<span>@shareFolderAccount</span> <span>varchar</span>(<span>50</span>), <span>--</span><span> 共享目录读写权限帐户名(如:domain\danny)</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>