- Method 2. Script Copy 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 the method of generating source library scripts is fast, no need to consider data of the source database but if you want to reserve some of the data from the source database, you must specifically do data replication processing.
- Running needs requires two files, you can find ScPTXFR.exescptxfr.rll in the SQL installation disk x86 / upgrade directory
Copy it to the following directory% systemroot% / system32 / - Zou Jian 2005.03 (please keep this information) - * /
/ * - Call example
- Copy POSEXEC SP_PROCCOPYDB 'POS' - * /
--1.Master database creates a process-proof store procedure to implement automatic copy of the current month database to next month
/ * - System requirements need two files to find ScPTXFR.exescptxfr.rll in the SQL installation disk x86 / upgrade directory
Copy it to the following directory% systemroot% / system32 / - * /
Create Proc S_ProccopyDB @ db_head sysname = n '- Database Prefix Asdeclare @SDBName Sysname, @ ddbname sysnamedeclare @s nvarchar (4000), @ bkfile nvarchar (1000)
- Copy source library name and target library name select @ sdbname = @ db_head conver (), 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, n ''), @ bkfile = reverse (stuff (@ bkfile, 1, charIndex ('/', @ bkfile), n ')) n' / backup / ' cast (newid () AS NVARCHAR (36)) n'.sql '
- Script generation processing set @ s = n'scptxfr / s ' quotename (CAST (serverProperty (N'ServerName') as nvarchar), n '") n' / d ' quotename (@ sdbname, n' "') N' / i '- use Windows authentication, if you use SQL authentication, 愀愀 n' / p" sa password ", fixed use SA user n '/ f' quotename (@ BKFILE, N '") n' / y / q / t / c / y'Exec master..xp_cmdshell @ s, no_output - Create a target database set @ s = n'create database ' quotename (@ddbname) EXEC SP_EXECUTESQL @S
- Use the source library script, create objects set @ s = n'sql / s' quotename (Cast (N'ServerName ') as nvarchar, N'ServerName') N '/ D' Quotename (@ ddbname, n '") n' / e '- Use Windows authentication, if you use SQL authentication, 愀 is n' / u" sa "/ p" sa password "' n' / i ' quotename (@ bkfile, n' ") exec master@cmdshell @ S, NO_OUTPUT
- Delete Temporary Backup File Set @ S = 'DEL "' @ bkfile '"' EXEC MASTER..XP_CMDSHELL @ S, NO_OUTPUTGO
Previous: Automatically generate the next month database according to the current month of the database - 1
Next: Automatically generate the next month database according to the current month of the database - 3
