数据库自动备份服务,带配置,还算可以吧
周末抽时间,编写了一个这样的工具,可以让,对数据库不了解或不熟悉的人,直接学会使用备份,省时省力,同样,我也将一份,通过脚本进行备份的,也奉献上来,
- 通过sql脚本进行数据库备份
通过脚本备份数据库,同样也支持压缩,但是需要安装winrar来实现,整体来说也还行,在服务器上创建一个 维护计划,就可以实现,也是很方便的,脚本如下:
USE [AppsDBHK]
GO
/****** Object: StoredProcedure [dbo].[P_DataBack] Script Date: 2018.8.10 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,yfb,Name>
-- Create date: <Create Date,20180810,>
-- Description: <Description,数据库备份,>
-- =============================================
ALTER PROCEDURE [dbo].[P_DataBack]
-- Add the parameters for the stored procedure here
@Result varchar(50) output,
@Msg varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
declare @prefix nvarchar(100),
@datefile nvarchar(100),
@bakfile nvarchar(100),
@rarfile nvarchar(100),
@rarcmd nvarchar(150),
@str_date nvarchar(100)
GO
/****** Object: StoredProcedure [dbo].[P_DataBack] Script Date: 2018.8.10 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,yfb,Name>
-- Create date: <Create Date,20180810,>
-- Description: <Description,数据库备份,>
-- =============================================
ALTER PROCEDURE [dbo].[P_DataBack]
-- Add the parameters for the stored procedure here
@Result varchar(50) output,
@Msg varchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
declare @prefix nvarchar(100),
@datefile nvarchar(100),
@bakfile nvarchar(100),
@rarfile nvarchar(100),
@rarcmd nvarchar(150),
@str_date nvarchar(100)
--设置备份的目录
set @prefix='D:/DataBack/'
set @str_date = rtrim(replace(replace(replace(convert(varchar(20),getdate(), 121),' ',''),'-',''),':',''))
set @datefile = 'AppsDBHK' +@str_date
set @bakfile = @prefix+@datefile+'bak'
set @rarfile = @prefix+@datefile+'rar'
--备份
BACKUP Database AppsDBHK TO DISK = @bakfile WITH NOFORMAT, NOINIT, NAME = N'xx-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--压缩rar
set @rarcmd ='"c:\Program Files\WinRAR\winrar.exe" ' +'a -df ' +@rarfile+' '+@bakfile
exec master..xp_cmdshell @rarcmd,NO_OUTPUT;
--删除前五天备份
set @rarcmd='del d:\DataBack\AppsDBHK' +convert(varchar(20),getdate()-5, 112)+'*.rar'
exec master..xp_cmdshell @rarcmd,NO_OUTPUT;
if @@ERROR<>0
begin
set @Result='Error'
set @Msg='错误'
end
else
begin
set @Result='Succ'
set @Msg='成功'
end
END
begin
set @Result='Error'
set @Msg='错误'
end
else
begin
set @Result='Succ'
set @Msg='成功'
end
END
别问我代码都是干啥的,无非就是打开权限,创建变量、时间戳的文件名、备份脚本、启动备份,哈哈。。都说完了,你也不用问了,