SQL server计划任务分区并按照年月作为文件目录实现分类管理

cdchenli

　　近期需要对数据量较大的表进行数据分区,但是若按照150W条数据进行分区,需要三天创建一个ndf文件,一年120个文件存放在一个文件夹中不方便进行文件管理。经过一下午的查阅资料，将以下两篇文章所提到的内容进行综合利用，实现按照年份和月份进行分类存放的SQL定时数据分区。
　　使用过程中碰到以下问题：
　　1.dir 命令当路径中存在空格‘ ’时会报错，若路径存在空格时用双引号""将路径包含起来即可   
　　2.改作业失败.无法确定所有者是否有服务器访问权限.               将所有者改为sa即可
　　本文主要参考:
　　http://www.cnblogs.com/linjt0416/articles/5666235.html           SQL计划实现自动数据分区
　　http://www.cnblogs.com/linjt0416/articles/5667111.html           SQL查找路径是否存在，不存在则创建

　　/*--------------------创建数据库的文件组和物理文件------------------------*/
use  DC_WATER   --此处为需要添加分区的数据库名
go
　　--创建存储路径部分
--//1,涉及安全问题，(用DBO权限用户)开启使用xp_cmdshell存储过程的权限  
--SQL Server blocked access to procedure 'xp_cmdshell'  
sp_configure 'show advanced options', 1  
go  
reconfigure --重新配置  
go  
sp_configure 'xp_cmdshell', 1  
go  
reconfigure  
go  
  
  
--//2,建立临时表保存临时信息  
--if not exists(select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#tb01')) --ok  
--if not exists(select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#tb01') and type='U') --ok  
if (OBJECT_ID('tempdb..#tb01') is not null)  
drop table #tb01  --drop table tempdb..#tb01 --ok too  
create table #tb01([dosCMDResult] varchar(4000)) --save DOS cmd result  
　　--声明所需变量
declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath
　　varchar(250), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50) ,@year varchar(50) ,@month varchar(50),@path varchar(250)
,  @dosCMD varchar(50) , @cmdLine varchar(4000)
set @tableName='DC_WATER'   --添加分区数据库名称
set @newDay=CONVERT(varchar(100), GETDATE(), 23)--23:按天 114:按时间
set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-1,getdate()), 120 ) as datetime)
set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')
set @fileGroupName=N'G'+@newNameStr+'_his'
set @ndfName=N'F'+@newNameStr+'_his'
--上面两处修改文件组和文件名称
set @year= DATEPART(YY,GETDATE())
set @month=REPLICATE('0',2-LEN(DATEPART(MM,GETDATE())))+convert(varchar(10),(DATEPART(MM,GETDATE())))
set @path=N'D:\MSSQL\'+@year+'\'+@month+'\'
set @fullPath=@path+@ndfName+'.ndf'
--此处该为自己的数据文件路径，lui注释2015-5-4（右击服务器-属性-数据库设置可看到）
set @partFunName=N'pf_Time_his'
set @schemeName=N'ps_Time_his'
--上面两处修改分区方案和分区函数名称
  
  
--//3,判断路径是否存在,若不存在则创建
set @dosCMD = 'dir ' --dos cmd  
set @cmdLine = @dosCMD+'"'+@path+'"'   --cmd命令行中若路径中存在' '空格  会产生错误  ,用双引号将路径包起来可以避免这个问题   ps:若要修改注意将下方md 命令出一并修改  
insert into #tb01 exec master..xp_cmdshell @cmdLine  
select * from #tb01  
if exists(select 1 from #tb01 where dosCMDResult in ('系统找不到指定的文件。','找不到文件','系统找不到指定的路径。')) --路径不存在  
--   lin   原文为   dosCMDResult = '找不到文件'  但经过实际测试当父级目录不存在时结果为'系统找不到指定的文件。'和'系统找不到指定的路径。'    因此对where 条件进行合并 以应对不同情况
begin     
    set @dosCMD = 'md '  
    set @cmdLine = @dosCMD +'"'+@path+'"'
    exec master..xp_cmdshell @cmdLine  
    --更多操作,比如在指定路径下建立某数据库,然后建立相关表等等  
end  
else  --路径存在  
begin  
    print char(13)+'路径:' + @path + ' 已经存在'  
end  
   
--//4,释放相关系统资源
drop table #tb01  
set @cmdLine = null  
set @dosCMD = null  
　　--分区部分
--创建文件组
if exists(select * from sys.filegroups where name=@fileGroupName)
begin
print '文件组存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
print '新增文件组'
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
print '修改分区方案'
end
if exists(select * from sys.partition_range_values where function_id=(select function_id from
sys.partition_functions where name =@partFunName))-- and value=@oldDay  
begin
exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')
print '修改分区函数'
end
end
--创建NDF文件
if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
begin
print 'ndf文件存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')
print '新创建ndf文件'
end
/*--------------------以上创建数据库的文件组和物理文件------------------------*/
--分区函数
if exists(select * from sys.partition_functions where name =@partFunName)
begin
print '此处修改需要在修改分区函数之前执行'
end
else
begin
exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay
+''')')
print '新创建分区函数'
end
--分区方案
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
print '此处修改需要在修改分区方案之前执行'
end
else
begin
exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO
(''PRIMARY'','''+@fileGroupName+''')')
print '新创建分区方案'
end
　　print '---------------以下是变量定义值显示---------------------'
print '当前数据库:'+@tableName
print '当前日期:'+@newDay+'(用作随机生成的各种名称和分区界限)'
print '合法命名方式:'+@newNameStr
print '文件组名称:'+@fileGroupName
print 'ndf物理文件名称:'+@ndfName
print '物理文件完整路径:'+@fullPath
print '文件所在文件夹路径:'+@path
print '分区函数:'+@partFunName
print '分区方案:'+@schemeName
print '年份:'+@year
print '月份:'+@month
go  
--// 恢复安全问题  
sp_configure 'xp_cmdshell', 0  
go  
reconfigure  
go
　　

/*
--查看创建的分区函数
select * from sys.partition_functions
--查看分区函数的临界值
select * from sys.partition_range_values
--查询分区方案
select * from sys.partition_schemes
--查询表数据在哪个分区中存储,where条件查询第一个分区中存在的数据
select *,$partition.pf_SaveTime(分区字段) as Patition from 表名 where $partition.pf_SaveTime(分区字段)=1
*/
　　记下方便日后直接查看。