SQL做的主服务器备份与还原

在主服务器上增加备份的存储过程，然后把备份文件存储在备份服务器上，备份服务器启动还原存储过程，实现了数据的还原

具体如下：

在主服务器上的数据库对要进行备份的数据库新建备份存储过程，并把备份文件复制到备份服务器上

主服务器上的备份存储过程如下所示：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:kingkie
-- Create date: 
-- Description:    
-- =============================================
ALTER PROCEDURE [dbo].[BackUpDataBaseTo] 
    -- Add the parameters for the stored procedure here
AS

declare @device varchar(255)
declare @path varchar(255)
declare @dbname varchar(255)
declare @filename varchar(255)
declare @cmd varchar(255)

BEGIN
--exec master..xp_cmdshell 'net use z: \\192.168.11.128\C$\BackUp 123456abcd /user:DataBaseP\ReplUser-P'

/*--说明: 
exec master..xp_cmdshell 'net use z: \\xz\c$ "密码" /user:xz\administrator' 

z:是映射网络路径对应本机的盘符, \\xz\c$是要映射的网络路径 
xz\administrator xz是远程的计算机名, administrator是登陆的用户名 
密码 面指定的administrator用户的密码 
--*/ 

set @dbname = 'SmartPas' 
set @path = 'D:\database\' 
set @filename = @dbname+'_Ts_'+ convert(char(8),getdate(),112)+substring(replace(convert(char(9),getdate(),114),':',''),0,3)+'.bak' 

set @device=@path + @filename
 
--备份；with init覆盖 ¦noinit添加 先备份到本机再复制过去.
backup database SmartPas to disk = @device with init 

set @cmd = 'copy '+ @device +' z:' 
--COPY 
exec master..xp_cmdshell @cmd 

--删除（这句可以去掉） 
--set @cmd = 'del ' +  @device 
--exec master..xp_cmdshell @cmd 

--完成后删除映射 
--exec master..xp_cmdshell 'net use z: /delete' 

---完全备份，覆盖原有文件
---BACKUP DATABASE @dbname TO  DISK = @device with init
---完全备份，在原有文件后面追加
---BACKUP DATABASE @dbname TO  DISK = @device 
---差异备份
---BACKUP DATABASE @dbname TO  DISK = @device WITH  DIFFERENTIAL
    SET NOCOUNT ON;

END

使用映射时需要打开xp_cmdshell服务，可以使用代码打开配置增加这个功能，也可以在外围配置服务上增加，打开设置代码如下

EXEC sp_configure N'show advanced options',N'1'

RECONFIGURE WITH OVERRIDE

EXEC sp_configure N'xp_cmdshell',N'1'

RECONFIGURE WITH OVERRIDE

EXEC sp_configure N'show advanced options',N'0'

映射时备份服务器上需要设置共享文件夹给主服务器读写的权限，这一步是异地备份的关键。

在备份服务器上增加一个还原的存储过程，注意，这个存储过程不要建立在还原的数据库上，不然会被占用，无法还原

还原代码如下所示：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[DataBaseRestore] 
AS

declare @device varchar(255)
declare @path varchar(255)
declare @dbname varchar(255)
declare @filename varchar(255)
declare @cmd varchar(255)

set @dbname = 'SmartPas'
set @path = 'D:\database\' --备份文件存储位置
set @filename = @dbname+'_Ts_'+ convert(char(8),getdate(),112)+substring(replace(convert(char(9),getdate(),114),':',''),0,3)+'.bak' 
set @device=@path + @filename 
declare @sql nvarchar(4000),@par nvarchar(1000)
if not exists( 
 select * from master..sysdatabases 
  where name=@dbname
  )
 begin
 --select @flag='db not exist'  /*数据库不存在*/
    return 2
 end
else
 begin
 --存在数据库就判断文件是否存在
   declare @num int --申明一个接受返回值的变量
   EXEC xp_fileexist @device ,@num output -- 执行文件存在否的验证 存在返回1 不存在返回0
   if(@num = 1) --如果存在就执行还原
   begin
      --begin tran tDeal
      --begin try

           exec ('alter database '+@dbname+' set offline WITH ROLLBACK IMMEDIATE;')

           select @sql='RESTORE DATABASE @db_name FROM DISK=@filename with '
           select @sql=@sql +' replace'
           select @par='@db_name nvarchar(128),@filename nvarchar(260)'
           execute sp_executesql @sql,@par,@db_name=@dbname,@filename=@device 

           exec ('alter database '+@dbname+' set online with rollback IMMEDIATE;')
          --删除备份文件（这句可以去掉） 
          --set @cmd = 'del ' +  @device 
          --exec master..xp_cmdshell @cmd 
      --end try
      --begin catch    
          --rollback tran tDeal
          --return 0
      --end catch
   end
 end

备份好后删除备份文件，如果不删除文件会越来越大，需要维护备份的文件。经过公司的项目测试可以实现，这种方法实现了数据库的同步，但实时性等比较差，需要更进一步方案才好，解决了临时的需求。