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

Data | database


--Method 2. Script replication 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 the method of generating the Source library script is that it is fast and does not need to consider the data of the source database but if you want to keep some of the data from the source database, you should do the data replication processing

--operational requirements require the following two files, can be found in the SQL installation disk X86\upgrade directory Scptxfr.exescptxfr.rll

Copy it to the following directory%systemroot% ystem32\--Jiangjian 2005.03 (please keep this information for reference)--*/

/*--Call Example

--Copy posexec sp_proccopydb ' Pos '--*/

--1.master database to create a processed stored procedure to achieve the monthly database to the next month of automatic replication of data

/*--system requirements require the following two files, can be found in the SQL installation disk X86\upgrade directory Scptxfr.exescptxfr.rll

Copy it to the following directory%systemroot% ystem32\--*/

create proc Sp_proccopydb@db_head sysname=n '--database prefix asdeclare @sdbname sysname, @ddbname sysnamedeclare @s Nvarchar ( 4000), @bkfile Nvarchar (1000)

--Copy source and destination library name 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 '. sql '

--Script generation handles set @s=n ' scptxfr/s ' +quotename (CAST (serverproperty (N ' servername ') as nvarchar), N ' "') +n '/d ' +quotename (@ Sdbname,n ' "') +n '/I '--using Windows authentication, if SQL authentication is used, 愀 is +n '/P ' sa password ', fixed using SA user +n '/F ' +quotename (@bkfile, N '" ') +n '/y/q/T /c/y ' EXEC master. xp_cmdshell @s,no_output

--Create target database set @s=n ' CREATE Database ' +quotename (@ddbname) EXEC sp_executesql @s

--Create object Set @s=n ' osql/s ' +quotename (CAST (serverproperty (N ' servername ') as nvarchar), N ' "') +n '/d ' + with Source Library script QuoteName (@ddbname, n ' "') +n '/e '--Using Windows authentication, 愀 to +n '/U sa '/P ' sa password ' +n '/I ' +quotename (@bkfile, n '" ") if SQL authentication is used EXEC master.. xp_cmdshell @s,no_output

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