--检查sqlserver所在服务的运行账号是否有权限访问共享文件夹,没有的话右键添加写权限
--检查方法:建立一网络驱动器,右键属性安全
--注:若sqlserver在localsystem账号下面运行,是访问不了共享文件夹的,要改成有权限的账号,如network services
--开启权限
sp_configure 'show advanced options', 1;
go
reconfigure;
go
EXEC sp_configure 'xp_cmdshell', 1
GO
reconfigure;
go
DECLARE @DesAddress NVARCHAR(500),@DesAccount NVARCHAR(100),@DesPassword NVARCHAR(100), @DataBaseName VARCHAR(100)
SET @DesAddress = '\\172.16.88.204\soft-0509\aa'
SET @DesAccount = 'administrator'
SET @DesPassword = 'Lvshou!@#'
SET @DataBaseName = 'master'
DECLARE @bakName VARCHAR(500)
SET @bakName = @DataBaseName + '_' + convert(varchar(10),getdate(),112) + '.bak'
SET @bakName = @DesAddress + '\' + @bakName
--print @bakName
DECLARE @sql NVARCHAR(1000)
--连接共享,打开通道
SET @sql = 'net use Y: '+ @DesAddress+ ' ' + @DesPassword + ' ' +'/user:'+@DesAccount
PRINT @sql
EXEC master..xp_cmdshell @sql
--备份
SET @sql = 'backup database ' +@DataBaseName + ' TO DISK = ' + QUOTENAME(@bakName,'''')
--PRINT @sql
EXEC (@sql)
--删除一周前备份
SET @sql = 'del ' + @DesAddress + '\' + @DataBaseName + '_' + convert(varchar(10),dateadd(day,-7,getdate()),112) + '.bak'
--PRINT @sql
EXEC master..xp_cmdshell @sql
--执行关闭关掉通道
exec master..xp_cmdshell 'net use Y: /delete /y'
--关闭权限
EXEC sp_configure 'xp_cmdshell', 0
GO
reconfigure;
go
sp_configure 'show advanced options', 0
go
reconfigure;
go