sql server 调用xp_cmdshell执行bat文件

declare @Move varchar(800);

declare @name varchar(50);

select @name=replace(CONVERT(varchar(100),getdate(), 23),'-','_');

EXEC sp_configure 'show advanced options', 1;RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE WITH OVERRIDE;

 

--复制数据文件脚本

select @move = N'master.dbo.xp_cmdshell "echo copy ' + N'\\IP\d$\Double-Backup\databasename_' + @name +'.bak '+ N'E:\Backup_SQL_Server\' +N' > E:\Backup_SQL_Server\copy.bat"'

--写入到copy.bat文件中

Exec (@move)

--复制日志文件脚本

select @move = N'master.dbo.xp_cmdshell "echo copy ' + N'\\IP\d$\Double-Backup\databasename_' + @name +'.trn '+ N'E:\Backup_SQL_Server\' +N' >>E:\Backup_SQL_Server\copy.bat"'

--附加到bat文件中

Exec (@move)

--print @move

 

--执行bat文件

EXEC master.dbo.xp_cmdshell 'E:\Backup_SQL_Server\copy.bat';

 

EXEC sp_configure 'show advanced options', 1;RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'show advanced options', 0;RECONFIGURE WITH OVERRIDE;