SQL server 数据库备份至服务器本地磁盘和其他服务器磁盘

前言

因工作需要将数据库每日备份至服务器本地磁盘和其他服务器磁盘，对网上的例子进行改造，解决了SQL server数据库备份的问题。

一、将指定数据库备份到本地

将数据库affair_stydy全备份至服务器H盘的AutoBackup文件夹下，实例如下：

DECLARE 
@NAME VARCHAR(250)
SET @NAME='H:\AutoBackup\affair_stydy_'+ CONVERT(VARCHAR(50),GETDATE(),112)+'.bak'
BACKUP DATABASE[affair_stydy]TO DISK=@NAME WITH NOFORMAT, NOINIT,  
NAME = N'affair_stydy_完整 数据库 备份', 
SKIP, NOREWIND, NOUNLOAD

 

二、将指定数据库备份至其他服务器

将数据库affair_stydy全备份至10.10.56.43服务器的任意盘的AutoBackup文件夹下，实例如下：

注意：在目标服务器任意盘符创建AutoBackup文件夹，开启共享并设置权限为Everyone 完全控制，可能会受360、防火墙、cmd.exe的运行账户 等因素的影响。

--如果xp_cmdshell没有启用，请先启用
sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'xp_cmdshell',1
RECONFIGURE
GO
--1.先创建映射
--EXEC MASTER.dbo.xp_cmdshell 'net use  \\服务器IP\共享文件夹  用户密码   /user:服务器IP\登录用户名'
EXEC MASTER.dbo.xp_cmdshell 'net use  \\10.10.56.43\AutoBackup l;''   /user:10.10.56.43\Administrator'
--2.利用映射备份数据库
BACKUP DATABASE affair_stydy   to disk='\\10.10.56.43\AutoBackup\affair_stydy.bak'
--3.删除映射
EXEC MASTER.dbo.xp_cmdshell 'net use \\10.10.56.43\AutoBackup /delete'

 

三、同时将数据库备份到服务器本地和其他服务器指定磁盘

将一、二进行整合，实现能同时备份数据库到服务器本地磁盘和其他服务器磁盘，实例如下：

--如果xp_cmdshell没有启用，请先启用
sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'xp_cmdshell',1
RECONFIGURE
GO
DECLARE
@DatabaseName VARCHAR(100)       --要备份的数据库名
,@Local_File_path   VARCHAR(500) --本地备份路径
,@Server_Name VARCHAR(20)        --目标服务器IP
,@Server_File_path VARCHAR(500)    --目标服务器备份路径
,@User VARCHAR(100)                --目标服务器登录用户名
,@Pwd  VARCHAR(100)                --目标服务器登录密码
,@Link VARCHAR(200)                --链接字符串
BEGIN
    --参数设定
    SET @DatabaseName='affair_stydy';
    SET @Local_File_path='''H:\AutoBackup\'+@DatabaseName+CONVERT(VARCHAR(50),GETDATE(),112)+'.bak''';
    SET @Server_Name='10.10.56.43';
    SET @Server_File_path='''\\'+@Server_Name+'\AutoBackup\'+@DatabaseName+CONVERT(VARCHAR(50),GETDATE(),112)+'.bak''';
    SET @User='Administrator';
    SET @Pwd='l;''';

    --一、备份到本地
    EXEC('BACKUP DATABASE '+@DatabaseName+' TO DISK ='+@Local_File_path);
    
    --二、备份到其他服务器
    --1.先创建映射
    SET @Link='net use  \\'+@Server_Name+'\AutoBackup '+@Pwd+'   /user:'+@Server_Name+'\'+@User+'';
    EXEC MASTER.dbo.xp_cmdshell @Link;

    --2.利用映射备份数据库
    EXEC('BACKUP DATABASE '+@DatabaseName+' TO DISK ='+@Server_File_path);
    --3.删除映射
    SET @Link='net use \\'+@Server_Name+'\AutoBackup /delete';
    EXEC MASTER.dbo.xp_cmdshell @Link;
END 

 

四、备份机制

可根据实际需要创建作业每日或指定时间段执行上述各代码块的内容，实现不同类型的数据库备份。