Automatically generate next month database based on monthly database--1

Data | database
/*--Original post address: http://community.csdn.net/expert/topic/3818/3818559.xml?temp=.9593317--*/

/*--Processing Requirements

There is a database named Pos200502 in the SQL databases, and each month there will be a database similar to this name (Pos200502 Pos200503)

How do I take advantage of SQL Server's automatic job + a stored procedure to achieve the following functions: 1.25th per month, automatically create a next one month Database,database name is set as: Posyyyymm (Yyyymm is the year and month, is always the next month of operation time 2. Copy all of the structure of this month's database (including tables, views, stored procedures, etc.) to the next one-month database. (Note that only the structure is copied, no data is copied!) ）--*/

---Method 1. Backup + Restore use Mastergo

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 Automatic replication

Copies the database of the specified leading edge to a database with the current month +1 as the library name, and clears all data, for example, the database leading edge is Pos, and the current date is 2005-3-27 requires that the data be copied Pos200503 to Pos200504 and the data inside is emptied

The advantage of using Backup + Restore method is that when you clean up the data, you can set the conditions to keep the specified data. The data is too long, the speed is slow, and the resources consumed are more

--Jiangjian 2005.03 (please keep this information for reference)--*/

/*--Call Example

--Copy posexec sp_proccopydb ' Pos '--*/create proc sp_proccopydb@db_head sysname=n '--database prefix asdeclare @sdbname sysname, @dd Bname sysnamedeclare @s Nvarchar (4000), @bkfile Nvarchar (1000), @move Nvarchar (4000)

--Copy source and destination library 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 nullbeginraiserror (N ' source database '%s ' does not exist ', 1,16, @sdbname) returnend

If DB_ID (@ddbname) is not nullbeginraiserror (N ' target database '%s ' already exists ', 1,16, @ddbname) returnend

--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) +n '. Bak '

--Data file moving statement 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) >0then stuff (Filename,charindex ( N ' +quotename (@sdbname, n ' "] +n ', filename), ' +cast (Len (@sdbname) as nvarchar) +n ', n ' +quotename (@ddbname, n '") +n ') else reverse (reverse (filename), charindex (' \ ', reverse (filename)), 0,+n ' _ ' +reverse (N ' +quotename (@ddbname , n ' "] +n))) end), n ' ' +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

--Revert to target database set @s=n ' Restore Database ' +quotename (@ddbname) +n ' from disk= @bkfile with replace ' + @moveexec sp_executesql @s , N ' @bkfile Nvarchar (1000) ', @bkfile

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

--Clean all data in the target database set @s=n ' use ' +quotename (@ddbname) +n ' exec sp_msforeachtable @command1 =n ' truncate ' ", @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 @sgo