According to the same month the database automatically generated database next month – 1

  / *– Original posts Address: 
  http://community.csdn.net/Expert/topic/3818/3818559.xml?temp=.9593317 
  –* / 

  / *– Processing requirements 

  Sql in a database called Pos200502 the Database, this month will have a similar name (Pos200502 Pos200503) the database 

  How can I use sql server operating + section of the automatic storage process, the following functions: 
  1. On the 25th of each month, automatically create a next month's database, database names as: PosYYYYMM (YYYYMM, and is, is always the implementation of the operation time of the month) 
  2. Then all of this month database structure (including tables, views, stored procedures, etc.) to the same copy of the month in the database.    (Note that only copy the structure, not to copy any data!) 
  –* / 

  — 1. Restore backup + 
  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

  / *– Database automatically copy 

  Will specify the database front, a copy of the current month +1 for the name of the database and remove all the data, for example, the database front for the Pos, the current date for 2005-3-27 
  Pos200503 asked to copy the data Pos200504, and empty the inside of the data 

  Backup + recovery methods to achieve benefits in cleaning up data, can set conditions, retention of data not specify where the data is a long time ago, slower, more consumption of resources 

  — Zou Jian 2005.03 (cited Please keep this information )–*/ 

  / *– Call Example 

  — Copy Pos 
  exec sp_ProcCopyDb 'Pos' 
  –* / 
  create proc sp_ProcCopyDb 
  @ DB_Head sysname = N''- the database prefix 
as
  declare @ sdbname sysname, @ ddbname sysname 
  declare @ s Nvarchar (4000), @ bkfile Nvarchar (1000), @ move Nvarchar (4000) 

  — Copy of the source and target of the name 
  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 'source database "% s" does not exist', 1,16, @ sdbname) 
return
end

  if db_id (@ ddbname) is not null 
begin
  raiserror (N 'goal of the database "% s" already exists', 1,16, @ ddbname) 
return
end

  — Temporary backup file name 
  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' 

  — Mobile data file statements 
  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 

  — Backup source database 
  set @ s = N'backup database '+ quotename (@ sdbname) + N' to disk = @ bkfile with format ' 
  exec sp_executesql @ s, N '@ bkfile Nvarchar (1000)', @ bkfile 

  — Reduction target database 
  set @ s = N'restore database ' 
  + quotename (@ ddbname) 
  + N 'from disk = @ bkfile with replace' 
  + @ move 
  exec sp_executesql @ s, N '@ bkfile Nvarchar (1000)', @ bkfile 

  — Delete temporary backup files 
  set @ s = 'del "' + @ bkfile + '"' 
  exec master .. xp_cmdshell @ s, no_output 

  — Cleaning up the goal of all the data in the database 
  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

  Next: According to the same month the database automatically generated database next month – 2 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Tags: