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

--------------------创建数据库的文件组和物理文件------------------------*/ use master 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+‘_test‘ set @ndfName=N‘F‘+@newNameStr+‘_test‘ set @year= DATEPART(YY,GETDATE()) set @month=REPLICATE(‘0‘,2-LEN(DATEPART(MM,GETDATE())))+convert(varchar(10),(DATEPART(MM,GETDATE()))) --上面两处修改文件组和文件名称 set @path=N‘"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\‘+@year+‘\‘+@month+‘\"‘ set @fullPath=@path+@ndfName+‘.ndf‘ set @dosCMD = ‘dir ‘ --dos cmd set @cmdLine = @dosCMD+@path insert into #tb01 exec master..xp_cmdshell @cmdLine select * from #tb01 if exists(select 1 from #tb01 where dosCMDResult in (‘系统找不到指定的文件。‘,‘找不到文件‘,‘系统找不到指定的路径。‘)) --路径不存在 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 go sp_configure ‘xp_cmdshell‘, 0 go reconfigure go /* --此处该为自己的数据文件路径，lui注释2015-5-4（右击服务器-属性-数据库设置可看到） set @partFunName=N‘pf_Time_test‘ set @schemeName=N‘ps_Time_test‘*/ /* --上面两次修改分区方案和分区函数名称 --创建文件组 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 /* --查看创建的分区函数 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 */ GO

 

 

 

 

 

 

记下方便日后直接查看。

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

标签：