186.数据库复制-月结

分手后的思念是犯贱 2024-01-20 07:01 27阅读 0赞

/*========== 生成月结数据库 ==========*/
    /*--要求描述
    
    	数据库名格式为: xxYYYYMM,即指定的前缘xx+年月
    	要求在每个月末自动复制本月数据库,生成下个月的空数据库
    --*/
    
    --方法1. 备份+恢复
    use master
    go
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ProcCopyDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[sp_ProcCopyDb]
    GO
    
    /*--数据库自动复制
    	
    	将指定前缘的数据库,复制为一个以当前月份+1为库名的数据库中,并且清除所有的数据
    	例如,数据库前缘为 Pos ,当前日期为 2005-3-27
    	则要求复制数据 Pos200503 为 Pos200504,并且清空里面的数据
    
    	用备份+恢复的方法实现
    	好处是在清理数据时,可以设置条件,保留指定的数据
    	不好的地方是数据多时,速度慢,消耗的资源多
    
    --(引用请保留此信息)--*/
    
    /*--调用示例
    
    	-- 复制 Pos
    	exec sp_ProcCopyDb 'Pos'
    --*/
    create proc sp_ProcCopyDb
    @DB_Head sysname=N''  --数据库前缀
    as
    declare @sdbname sysname,@ddbname sysname
    declare @s Nvarchar(4000),@bkfile Nvarchar(1000),@move Nvarchar(4000)
    
    --复制的源库名及目标库名
    if @DB_Head is null set @DB_Head=N''
    
    select @sdbname=@DB_Head+convert(char(6),getdate(),112),
    	@ddbname=@DB_Head+convert(char(6),dateadd(month,1,getdate()),112)
    
    if db_id(@sdbname) is null
    begin
    	raiserror(N'源数据库"%s"不存在',1,16,@sdbname)
    	return
    end
    
    if db_id(@ddbname) is not null
    begin
    	raiserror(N'目标数据库"%s"已经存在',1,16,@ddbname)
    	return
    end
    
    --临时备份文件名
    select top 1 @bkfile=rtrim(reverse(filename)) 
    from master.dbo.sysfiles 
    where name=N'master'
    select @bkfile=stuff(@bkfile,1,charindex('\',@bkfile),N'')
    	,@bkfile=reverse(stuff(@bkfile,1,charindex('\',@bkfile),N''))
    	+N'\BACKUP\'+cast(newid() as nvarchar(36))+N'.bak'
    
    --数据文件移动语句
    set @s=N'set @move=N''''
    select @move=@move
    	+N'',move ''+quotename(rtrim(name),N'''''''')
    	+N'' to ''+quotename(rtrim(case 
    		when charindex(N'
    	+quotename(@sdbname,N'''')
    	+N',filename)>0
    		then stuff(filename,charindex(N'
    	+quotename(@sdbname,N'''')
    	+N',filename),'
    	+cast(len(@sdbname) as nvarchar)
    	+N',N'+quotename(@ddbname,N'''')+N')
    		else reverse(stuff(
    			reverse(filename),
    			charindex(''\'',reverse(filename)),
    			0,
    			+N''_''+reverse(N'+quotename(@ddbname,N'''')+N')))
    		end),N'''''''')
    from '+quotename(@sdbname)+N'.dbo.sysfiles'
    exec sp_executesql @s,N'@move Nvarchar(4000) out',@move out
    
    --备份源数据库
    set @s=N'backup database '+quotename(@sdbname)+N' to disk=@bkfile with format'
    exec sp_executesql @s,N'@bkfile Nvarchar(1000)',@bkfile
    
    --还原为目标数据库
    set @s=N'restore database '
    	+quotename(@ddbname)
    	+N' from disk=@bkfile with replace'
    	+@move
    exec sp_executesql @s,N'@bkfile Nvarchar(1000)',@bkfile
    
    --删除临时备份文件
    set @s='del "'+@bkfile+'"'
    exec master..xp_cmdshell @s,no_output
    
    --清理目标数据库中的所有数据
    set @s=N'
    use '+quotename(@ddbname)+N'
    exec sp_msforeachtable 
    	@command1=N''truncate table ?'',
    	@whereand=N'' and objectproperty(o.id,N''''TableHasForeignRef'''')=0''
    exec sp_msforeachtable 
    	@command1=N''delete from ?'',
    	@whereand=N'' and objectproperty(o.id,N''''TableHasForeignRef'''')=1''
    '
    exec sp_executesql @s
    go
    
    
    /*=================================================================*/
    
    --方法2. 脚本复制
    use master
    go
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ProcCopyDb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[sp_ProcCopyDb]
    GO
    
    /*--数据库自动复制
    	
    	将指定前缘的数据库,复制为一个以当前月份+1为库名的数据库中,并且清除所有的数据
    	例如,数据库前缘为 Pos ,当前日期为 2005-3-27
    	则要求复制数据 Pos200503 为 Pos200504,并且清空里面的数据
    
    	用生成源库脚本的方法实现
    	好处是速度快,不需要考虑源数据库的数据
    	但如果要保留源数据库的部分数据,则要专门做数据复制处理
    
    --运行需求
    	需要如下两个文件,可以在sql安装盘 X86\UPGRADE 目录下找到
    	scptxfr.exe
    	scptxfr.rll
    
    	将其复制到下述目录
    	%systemroot%\system32\
    --邹建 2005.03(引用请保留此信息)--*/
    
    /*--调用示例
    
    	-- 复制 Pos
    	exec sp_ProcCopyDb 'Pos'
    --*/
    create proc sp_ProcCopyDb
    @DB_Head sysname=N''  --数据库前缀
    as
    declare @sdbname sysname,@ddbname sysname
    declare @s Nvarchar(4000),@bkfile Nvarchar(1000)
    
    --复制的源库名及目标库名
    select @sdbname=@DB_Head+convert(char(6),getdate(),112),
    	@ddbname=@DB_Head+convert(char(6),dateadd(month,1,getdate()),112)
    
    if db_id(@sdbname) is null
    begin
    	raiserror(N'源数据库"%s"不存在',1,16,@sdbname)
    	return
    end
    
    if db_id(@ddbname) is not null
    begin
    	raiserror(N'目标数据库"%s"已经存在',1,16,@ddbname)
    	return
    end
    
    --临时备份文件名
    select top 1 @bkfile=rtrim(reverse(filename)) 
    from master.dbo.sysfiles 
    where name=N'master'
    select @bkfile=stuff(@bkfile,1,charindex('\',@bkfile),N'')
    	,@bkfile=reverse(stuff(@bkfile,1,charindex('\',@bkfile),N''))
    	+N'\BACKUP\'+cast(newid() as nvarchar(36))+N'.sql'
    
    --脚本生成处理
    set @s=N'scptxfr /s '+quotename(cast(serverproperty(N'servername') as nvarchar),N'"')
    	+N' /d '+quotename(@sdbname,N'"')
    	+N' /I'  --使用windows身份验证,如果使用sql身份验证,则愀为 +N' /P "sa密码"',固定使用sa用户
    	+N' /f '+quotename(@bkfile,N'"')
    	+N' /Y /q /T /C /Y'
    exec master..xp_cmdshell @s,no_output
    
    --创建目标数据库
    set @s=N'create database '+quotename(@ddbname)
    exec sp_executesql @s
    
    --使用源库脚本,为目标数据库创建对象
    set @s=N'osql /S'+quotename(cast(serverproperty(N'servername') as nvarchar),N'"')
    	+N' /d '+quotename(@ddbname,N'"')
    	+N' /E'  --使用windows身份验证,如果使用sql身份验证,则愀为 +N' /U"sa" /P"sa密码"'
    	+N' /i'+quotename(@bkfile,N'"')
    exec master..xp_cmdshell @s,no_output
    
    --删除临时备份文件
    set @s='del "'+@bkfile+'"'
    exec master..xp_cmdshell @s,no_output
    go
    
    
    
    /*=================================================================*/
    
    --创建一个每月最后一个工作日执行的作业,调用上述存储过程实现自动创建数据库
    
    use master
    go
    
    --设置 SQL Agent 服务为自动启动
    exec msdb..sp_set_sqlagent_properties @auto_start=1
    go
    
    --创建作业
    exec msdb..sp_add_job @job_name=N'自动建库处理'
    
    --创建作业步骤
    declare @sql varchar(800),@dbname varchar(250)
    select @sql=N'exec sp_ProcCopyDb'  --调用自动建库的存储过程
    	,@dbname=db_name()         --执行自动建库处理的数据库名
    
    exec msdb..sp_add_jobstep @job_name=N'自动建库处理',
    	@step_name = '建库处理',
    	@subsystem = 'TSQL',
    	@database_name=@dbname,
    	@command = @sql,
    	@retry_attempts = 5, --重试次数
    	@retry_interval = 5  --重试间隔
    
    --添加作业调度(每月最后一个工作日)
    EXEC msdb.dbo.sp_add_jobschedule @job_name = N'自动建库处理', 
    	@name = N'时间安排',
    	@freq_type = 32, 
    	@active_start_time = 0,  --0点开始执行
    	@freq_interval = 9,      --每月最后一个工作日
    	@freq_subday_type = 1, 
    	@freq_subday_interval = 0, 
    	@freq_relative_interval = 16, 
    	@freq_recurrence_factor = 1
    	
    -- 添加目标服务器
    EXEC msdb.dbo.sp_add_jobserver 
    	@job_name = N'自动建库处理' ,
    	@server_name = N'(local)'