/*========== 生成月結(jié)數(shù)據(jù)庫 ==========*/
/*--要求描述

	數(shù)據(jù)庫名格式為: xxYYYYMM,即指定的前緣xx+年月
	要求在每個月末自動復(fù)制本月數(shù)據(jù)庫,生成下個月的空數(shù)據(jù)庫
--*/

--方法1. 備份+恢復(fù)
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

/*--數(shù)據(jù)庫自動復(fù)制
	
	將指定前緣的數(shù)據(jù)庫,復(fù)制為一個以當(dāng)前月份+1為庫名的數(shù)據(jù)庫中,并且清除所有的數(shù)據(jù)
	例如,數(shù)據(jù)庫前緣為 Pos ,當(dāng)前日期為 2005-3-27
	則要求復(fù)制數(shù)據(jù) Pos200503 為 Pos200504,并且清空里面的數(shù)據(jù)

	用備份+恢復(fù)的方法實現(xiàn)
	好處是在清理數(shù)據(jù)時,可以設(shè)置條件,保留指定的數(shù)據(jù)
	不好的地方是數(shù)據(jù)多時,速度慢,消耗的資源多

--鄒建 2005.03(引用請保留此信息)--*/

/*--調(diào)用示例

	-- 復(fù)制 Pos
	exec sp_ProcCopyDb 'Pos'
--*/
create proc sp_ProcCopyDb
@DB_Head sysname=N''  --數(shù)據(jù)庫前綴
as
declare @sdbname sysname,@ddbname sysname
declare @s Nvarchar(4000),@bkfile Nvarchar(1000),@move Nvarchar(4000)

--復(fù)制的源庫名及目標(biāo)庫名
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'源數(shù)據(jù)庫"%s"不存在',1,16,@sdbname)
	return
end

if db_id(@ddbname) is not null
begin
	raiserror(N'目標(biāo)數(shù)據(jù)庫"%s"已經(jīng)存在',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'

--數(shù)據(jù)文件移動語句
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

--備份源數(shù)據(jù)庫
set @s=N'backup database '+quotename(@sdbname)+N' to disk=@bkfile with format'
exec sp_executesql @s,N'@bkfile Nvarchar(1000)',@bkfile

--還原為目標(biāo)數(shù)據(jù)庫
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

--清理目標(biāo)數(shù)據(jù)庫中的所有數(shù)據(jù)
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. 腳本復(fù)制
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

/*--數(shù)據(jù)庫自動復(fù)制
	
	將指定前緣的數(shù)據(jù)庫,復(fù)制為一個以當(dāng)前月份+1為庫名的數(shù)據(jù)庫中,并且清除所有的數(shù)據(jù)
	例如,數(shù)據(jù)庫前緣為 Pos ,當(dāng)前日期為 2005-3-27
	則要求復(fù)制數(shù)據(jù) Pos200503 為 Pos200504,并且清空里面的數(shù)據(jù)

	用生成源庫腳本的方法實現(xiàn)
	好處是速度快,不需要考慮源數(shù)據(jù)庫的數(shù)據(jù)
	但如果要保留源數(shù)據(jù)庫的部分?jǐn)?shù)據(jù),則要專門做數(shù)據(jù)復(fù)制處理

--運行需求
	需要如下兩個文件,可以在sql安裝盤 X86\UPGRADE 目錄下找到
	scptxfr.exe
	scptxfr.rll

	將其復(fù)制到下述目錄
	%systemroot%\system32\
--鄒建 2005.03(引用請保留此信息)--*/

/*--調(diào)用示例

	-- 復(fù)制 Pos
	exec sp_ProcCopyDb 'Pos'
--*/
create proc sp_ProcCopyDb
@DB_Head sysname=N''  --數(shù)據(jù)庫前綴
as
declare @sdbname sysname,@ddbname sysname
declare @s Nvarchar(4000),@bkfile Nvarchar(1000)

--復(fù)制的源庫名及目標(biāo)庫名
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'源數(shù)據(jù)庫"%s"不存在',1,16,@sdbname)
	return
end

if db_id(@ddbname) is not null
begin
	raiserror(N'目標(biāo)數(shù)據(jù)庫"%s"已經(jīng)存在',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

--創(chuàng)建目標(biāo)數(shù)據(jù)庫
set @s=N'create database '+quotename(@ddbname)
exec sp_executesql @s

--使用源庫腳本,為目標(biāo)數(shù)據(jù)庫創(chuàng)建對象
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



/*=================================================================*/

--創(chuàng)建一個每月最后一個工作日執(zhí)行的作業(yè),調(diào)用上述存儲過程實現(xiàn)自動創(chuàng)建數(shù)據(jù)庫

use master
go

--設(shè)置 SQL Agent 服務(wù)為自動啟動
exec msdb..sp_set_sqlagent_properties @auto_start=1
go

--創(chuàng)建作業(yè)
exec msdb..sp_add_job @job_name=N'自動建庫處理'

--創(chuàng)建作業(yè)步驟
declare @sql varchar(800),@dbname varchar(250)
select @sql=N'exec sp_ProcCopyDb'  --調(diào)用自動建庫的存儲過程
	,@dbname=db_name()         --執(zhí)行自動建庫處理的數(shù)據(jù)庫名

exec msdb..sp_add_jobstep @job_name=N'自動建庫處理',
	@step_name = '建庫處理',
	@subsystem = 'TSQL',
	@database_name=@dbname,
	@command = @sql,
	@retry_attempts = 5, --重試次數(shù)
	@retry_interval = 5  --重試間隔

--添加作業(yè)調(diào)度(每月最后一個工作日)
EXEC msdb.dbo.sp_add_jobschedule @job_name = N'自動建庫處理', 
	@name = N'時間安排',
	@freq_type = 32, 
	@active_start_time = 0,  --0點開始執(zhí)行
	@freq_interval = 9,      --每月最后一個工作日
	@freq_subday_type = 1, 
	@freq_subday_interval = 0, 
	@freq_relative_interval = 16, 
	@freq_recurrence_factor = 1
	
-- 添加目標(biāo)服務(wù)器
EXEC msdb.dbo.sp_add_jobserver 
	@job_name = N'自動建庫處理' ,
	@server_name = N'(local)'