/ * - Originally: http://community.9cbs.net/expert/topic/3818/3818559.xml? Temp = .9593317 - * /
/ * - Treatment requirements
There is a Database called POS200502 in the SQL database, there is a Database similar to this name (POS200502 POS200503).
How do I use SQL Server's automatic job a stored procedure, implement the following features: 1. Each month, automatically create a next month's Database, Database name is set to: PosyyyyMM (YYYYMM is the year and month, always It is the next month of performing operational time) 2. All structures of Database (including tables, views, stored procedures, etc.) are then copied in the next month in Database. (Note Only copy the structure, do not reproduce any data!) - * /
--- 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
Copy the database of the specified leading edge, copied into a database named in the current month 1, and clear all the data, for example, the database leading edge is POS, the current date is 2005-3-27 request copy data POS200503 is POS200504 And the data inside
The advantage of using backup recovery is to clean the data. If the condition can be set, where the specified data is not good, the data is more, the speed is slow, and the consumption has more resources.
- Zou Jian 2005.03 (Please keep this information) - * /
/ * - Call example
- Copy Posexec sp_ProcCopyDb 'Pos' - * / create proc sp_ProcCopyDb @ DB_Head sysname = N '' - prefixed database asdeclare @sdbname sysname, @ ddbname sysnamedeclare @s Nvarchar (4000), @ bkfile Nvarchar (1000), @ move NVARCHAR (4000)
- Copy source library name and target library name if @db_head is null set @ db_head = n ''
Select @ sdbname = @ db_head conver (char (6), getdate (), 112), @ ddbname = @ db_head conver (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 (36)) n'.bak '- Data file mobile statement set @ s = N'SET @move = n' '' 'select @move = @move n' ', Move' ' quotename (RTRIM Name), n '' '' '') n '' to '' quotename (RTRIM (Case When Charindex (@ sdbname, n '' ') n', filename)> 0Then Stuff (FileName, Charindex (N ' Quotename) N', FileName), ' Cast (Len (@SDBNAME) AS NVARCHAR) N', N ' Quotename (@ DDBNAME, N '' ') N') Else Reverse, Charindex ('' / ', Reverse (FileName)), 0, N' '_' ' Reverse (N' quotename (@ ddbname, n '' ') n')))), 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
- Restore 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 File 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'Runcate Table?' ', @ Whereand = n' 'and ObjectProperty (O ObjectProperty .id, n '' '' ') = 0''Exec sp_msForeachTable @ command1 = N''delete from?', @ whereand = n '' and ObjectProperty (o.id, n '' '" TableHasforeIgnRef '' ') = 1' 'EXEC SP_EXECUTESQL @SGO
Next: Automatically generate the next month database according to the current month database - 2
