SQL 把每个表导出到CSV，然后压缩全部的csv，作为备份。

USE [emes_yun]
GO

/****** Object: StoredProcedure [dbo].[db_BackUpExport] Script Date: 2019/3/5 14:21:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[db_BackUpExport]
@DATABASENAME VARCHAR(50), --数据库名字
@FILEPATH VARCHAR(100), --备份文件的路径
@ORGID VARCHAR(4), --数据库名字
@RETVAL INT OUTPUT – 运行结果(出参) 0:OK/1:NG
AS
BEGIN
SET @RETVAL = ‘0’
BEGIN TRY

	EXEC sp_configure 'show advanced options',1    --开启配置
	RECONFIGURE

	EXEC sp_configure 'xp_cmdshell',1    --开启配置
	RECONFIGURE

	-- ▼ 新建一个最新的文件夹，是秒级的，一秒内如果2次备份数据，数据会被最后一次覆盖======
	DECLARE @PATH VARCHAR(255)  --完全路径
	DECLARE @DATE VARCHAR(255)  --日期 例如: 20171011
	SET @DATE= CONVERT(VARCHAR(10),GETDATE(),112)
	-- 文件夹的格式：yyyymmdd-hhmmss
	SET @DATE = @DATE + '-'+ RIGHT(('00'+ CAST(DATEPART(hh,GETDATE()) AS VARCHAR(2))),2)
	SET @DATE = @DATE + RIGHT(('00'+CAST(DATEPART(mi,GETDATE()) AS VARCHAR(2))),2)
	SET @DATE = @DATE + RIGHT(('00'+CAST(DATEPART(ss,GETDATE()) AS VARCHAR(2))),2);

	SET @PATH = @FILEPATH + '\' + @DATE
	DECLARE @fileEx int -- 判断文件存在不存在
	EXEC xp_fileexist @PATH, @fileEx output
	if (@fileEx =0)
		BEGIN
			DECLARE @EX NVARCHAR(255)
			SET @EX='ExEc xp_cmdshell ''MKDIR '+@PATH+'''' --- 文件夹不存在的时候创建
			ExEc (@EX)
		END
	-- ▲ ==================================================================================
	-- ▼ 数据开始备份 =====================================================================
	-- 全部的用户表
	DECLARE tabList CURSOR LOCAL FAST_FORWARD FOR (select name from sysobjects where xtype='U')
	DECLARE @tabName varchar(50)
	-- 循环全部的表
	OPEN tabList
	-- 获取游标当前指针的记录，读取一行数据并传给变量
	FETCH tabList INTO @tabName
	-- 开始循环，判断是否游标已经到达了最后作为循环条件 
	WHILE (@@fetch_status <> -1)
		BEGIN
			--- 表的字段当中有包含orgId的就开始备份
			IF EXISTS(select name from syscolumns where id=(select max(id) from sysobjects where xtype='U' and name= @tabName) and Lower(name) = 'orgid')
			BEGIN
				declare @exec_sql varchar(1024);
				-- 抽取数据orgid是本组织的数据
				--set @exec_sql='bcp "select * from '+ @DATABASENAME +'.dbo.'+ @tabName +' where left(orgId,4) = '+ @ORGID +'" queryout "'+ @PATH + '\' + @tabName +'.csv "  -c -q -U"sa" -P"xhsk.2018"'
				--EXEC master..xp_cmdshell 'bcp "select * from emes.dbo.mes_warehouse" queryout E:\emes_db_bk\aaa.csv -S 192.168.1.2 -U sa -P xhsk.2018 -c -t"," -r"\n"'
				--EXEC master..xp_cmdshell 'bcp "select * from emes.dbo.mes_warehouse" queryout E:\emes_db_bk\aaa.csv -S 192.168.1.2 -U sa -P xhsk.2018 -c -t"," -r"\n"'
				IF(Lower(@tabName) = 'mes_material') 
				BEGIN
					--材料的名称中含有[,"]等等的，为了能正常导入导出，做特殊处理
					SET @exec_sql='bcp "select id,code,replace(name,"'',''","''Ü''") ,type,stockMin,state,tag,note,length,width,weight,thickness,density,vender,outSource,amount,freeze,onway,orgId,creater,createTime,modifier,modifyTime from '+ @DATABASENAME +'.dbo.'+ @tabName +' where left(orgId,4) = '+ @ORGID +'" queryout "'+ @PATH + '\' + @tabName +'.csv " -S"192.168.1.2" -U"sa" -P"xhsk.2018" -c -t"," -r"\n"'
					print @exec_sql
					EXEC master..xp_cmdshell @exec_sql
				END
				ELSE
				BEGIN
					SET @exec_sql='bcp "select * from '+ @DATABASENAME +'.dbo.'+ @tabName +' where left(orgId,4) = '+ @ORGID +'" queryout "'+ @PATH + '\' + @tabName +'.csv " -S"192.168.1.2" -U"sa" -P"xhsk.2018" -c -t"," -r"\n"'
					EXEC master..xp_cmdshell @exec_sql;
				END
			END
		FETCH tabList INTO @tabName
		END
	CLOSE tabList
	-- ▲ =备份完成===========================================================================
	-- ▼ 备份记录 ===========================================================================
	INSERT INTO com_duplicateRecord(id,name,createtime,restoretime,note,depid)values(NEWID(),@DATE+'.rar',CONVERT(char(19), getdate(), 120),null,null,@ORGID);
	-- ▲ 备份记录完成 =======================================================================
	-- ▼ 备份的文件夹压缩 ===================================================================
	declare @cmd nvarchar(400)
	declare @xlspath nvarchar(400)
	declare @outpath nvarchar(400)
	select @xlspath= @PATH
	select @outpath= @PATH + '.rar'
	--select @cmd='""%ProgramFiles%\WinZip\Wzzip.exe"" -m -j '+@outpath+' ' + @xlspath??? --zip（!!!Note: Must install WINZIP and WinZip Command Line Support Add-On）
	select @cmd='""%ProgramFiles%\WinRAR\Rar.exe"" a -ep1 -df '+@outpath+' ' + @xlspath --rar
	exec master..xp_cmdshell @cmd
	-- ▲ 备份的文件夹压缩完成 ===============================================================
END TRY  
BEGIN CATCH
	-- 发生错误
    SET @RETVAL = '1'
END CATCH 

END

GO