SQL2013数据库自动网络备份

来源：互联网发布：sentinel 1 数据下载编辑：程序博客网时间：2024/05/22 07:01

1.启用 SQL 代理

2.添加作业

3.编写代码

CODE:

1.开放 xp_cmdshell

SELECT * FROM sys.configurations
ORDER BY name
GO

sp_configure 'show advanced options', 1
go
reconfigure
GO

sp_configure 'xp_cmdshell', 1
go
reconfigure
go

2.备份七天

EXEC master..xp_cmdshell 'net use z: \\s54524\D$\bakdb"878549" /user:s54524\administrator'

declare @file varchar(30)
set @file = 'z:\bakdb' + convert(char(1),datepart(dw,getdate()))

BACKUP DATABASE [ERP] TO DISK = @file WITH INIT , NOUNLOAD , NAME = N'ERP_bak', NOSKIP , STATS = 10, NOFORMAT

exec master..xp_cmdshell 'net use z: /delete'

3.备份每天

-------------------------------

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [SP_BK](@FolderPath varchar(500))
as
DECLARE @FullPath varchar(1000)
,@FullPath1 varchar(1000)
set @FullPath = @FolderPath+ 'ERP_'+convert(VARCHAR(4),year(getdate()))
+right( '0'+convert(VARCHAR(2),MONTH(getdate())),2)+right('0'+convert(VARCHAR(2),DAY(getdate())),2)
+substring(convert(VARCHAR(2),getdate()),12,2)+ '.bak'
set @FullPath1 = @FolderPath+ 'ERP_'+convert(VARCHAR(4),year(getdate()))
+right( '0'+convert(VARCHAR(2),MONTH(getdate())),2)
+right('0'+convert(VARCHAR(2),DAY(getdate())),2)
+substring(convert(VARCHAR(20),getdate()),12,2)+ '_log' + '.bak'
backup database ERP to disk=@FullPath WITH INIT
backup log ERP to disk=@FullPath1 WITH NOINIT
SET NOCOUNT ON
GO

---------------------------------

EXEC master..xp_cmdshell 'net use z: \\s54524\D$\bakdb"878549" /user:s54524\administrator'

EXEC [SP_BK] 'z:\bakdb'

exec master..xp_cmdshell 'net use z: /delete'

0 0