根據當月資料庫自動產生下個月資料庫–1

/*--原帖地址:
http://community.csdn.net/Expert/topic/3818/3818559.xml?temp=.9593317
--*/

/*--處理要求

在sql資料庫裡有一個名為Pos200502的Database，每個月會有一個類似於這樣名稱(Pos200502 Pos200503)的database

我該如何利用sql server的自動作業+一段預存程序，實現以下功能：
1.每個月的25號，自動建立一個下一個月的database,database名字定為：PosYYYYMM (YYYYMM是年和月，始終是執行操作時間的下一個月)
2.再將本月database的所有結構（包括表、視圖、預存程序等）一模一樣的複製到下一個月的database中。（注意僅複製結構，不複製任何資料！）
--*/

---方法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,並且清空裡面的資料

用備份+恢複的方法實現
好處是在清理資料時,可以設定條件,保留指定的資料
不好的地方是資料多時,速度慢,消耗的資源多

--鄒建 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),@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