1. Create a function / * - Get the database's file directory @dbName Specifies the database name to obtain the directory. If the specified data does not exist, return the default data directory set when installing SQL If specified NULL, return to the default SQL backup Directory Name - Zou Jian 2003.10 - * /
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [f_getdbpath]') and xtype in (n'fn ', n'if ", n')) DROP FUNCTION [DBO] [f_getdbpath] Go
create function f_getdbpath (@dbname sysname) returns nvarchar (260) as begin declare @re nvarchar (260) if @dbname is null or db_id (@dbname) is null select @ re = rtrim (reverse (filename)) from master .. sysdatabases where name = 'master' else select @ re = r r (Reverse (filename)) from master..sysdatabasees where name = @ dbname
IF @dbname is null set @ RE = Reverse (Substring (@ RE) 5, 260)) 'Backup' else set @ RE = Reverse (@ RE, Charindex ('/' , @ RE), 260) RETURN (@RE) endgo / * - Call sample select database file directory = dbo.f_getdbpath ('tempdb'), [Default SQL Server Data Directory] = dbo.f_getdbpath (''), [Default SQL Server Backup Directory] = dbo.f_getdbpath (null) - * / 2, Backup Database
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_backupdb]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_backupdb] Go
CREATE PROC P_BACKUPDB @ DBNAME SYSNAME = ', - To back up the database name, not specified, back up the current database @BKPATH NVARCHAR (260) =' ', the storage directory of the backup file, not specified, use SQL default backup Directory @BKFNAME NVARCHAR (260) = ', - Backup file name, file name can be used in the file name, / DBNAME / representative database name, / Date / representative date, / time / representative time @BkType nvarchar (10) =' db ' , - Backup Type: 'DB' Backup Database, 'DF' Difference Backup, 'Log' Log Backup @Appendfile Bit = 1 - Adding / Overlay Backup File as Declare @SQL VARCHAR (8000) if Isnull (@dbname, ' ') =' 'set @ dbname = db_name () if isnull (@BKPATH,' ') =' 'set @ bkpath = dbo.f_getdbpath (null) if isnull (@BKFNAME,' ') =' 'set @ bkfname = '/Dbname/_/date/_/time/.bak' set @ bkfname = replace (Replace (@BKFNAME, '/ DBNAME /', @ dbname), '/ Date /', Convert (varchar, getdate ), 112), '/ TIME /', REPLACE (Convert (varchar, getdate (), 108), ':', '')) set @ SQL = 'backup' case @Bktype when 'log' Then ' Log 'Else' Database 'end @ dbname ' to disk = '' ' @ bkpath @ bkfname ' '' with ' case @Bktype when' DF 'TEN' DIFFERENTIAL, 'ELSE' END CASE @Appendfile When 1 Then 'Noinit' Else 'INIT' End Print @SQL EXEC (@SQL) GO / * - Call Example
- Back up the current database EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = 'db_ / date / _db.bak'
- Difference backup Current Database EXEC P_BACKUPDB @ bkpath = 'c: /', @ bkfname = 'db_ / date / _df.bak', @ bktype = 'DF'
- Backup Current Database Log EXEC P_BACKUPDB @ Bkpath = 'C: /', @ bkfname = 'db_ / date / _log.bak', @ bktype = 'log'
- * / 3, restore database if exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [P_restoredb]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [DBO ]. [p_restoredb] Go
CREATE PROC P_RESTOREDB @ bkfile nvarchar (1000), - Define Backup file name @dbname sysname = '', - Define the restored database name, default for backup file name @dbpath nvarchar (260) = '' , - Restore data inventory placement, not specified, the default data directory @retype nvarchar (10) @retype nvarchar (10) = 'DB', - Recovery type: 'DB' Financial Recovery Database, 'DBNOR' Recovery, Log Recovery for complete recovery, 'DF' Difference backup recovery, 'log' log recovery @FileNumber int = 1, - Restore file number @overexist bit = 1, - Only override existing database, only @RetyPE is @ @retype Killuser bit = 1 - Do you turn off the user's use process, only @ overexist = 1 time effective asclare @SQL varchar (8000)
- Get recovered database name if isnull (@dbname, ') =' 'select @ SQL = Reverse (@BKFILE), @ SQL = Case When Charindex ('. ', @ SQL) = 0 Then @SQL ELSE Substring (@ SQL, Charindex ('.', @ SQL) 1,1000) End, @ SQL = Case When Charindex ('/', @ SQL) = 0 Then @SQL Else Left (@ SQL, Charindex ('/ ', @ SQL) -1) end, @ dbname = reverse (@SQL)
- Get recovered data inventory placed by IF isnull (@dbpath, '') = '' set @ dbpath = dbo.f_getdbpath ('')
- Generate Database Recovery Statement Set @ SQL = 'Restore' Case @RTYPE WHEN 'LOG' THEN 'LOG' ELSE 'DATABASE' END @ DBNAME 'From Disk =' ' @ bkfile ' '' ' ' with File = ' cast (@filenumber as var) case when @ Overexist = 1 and @retype in (' db ',' dbnor ') Then', Replace 'else' end case @rety gen 'dbnor' Then ' , Norecovery 'Else', Recovery 'EndPrint @ SQL - Processing IF @ Retype =' DB 'OR @ Retype =' DB 'OR @ RetyPE =' DBNOR'BEGIN - Gets logic file name Declare @LFN nVarchar from the backup file (128 ), @ TP char (1), @ i int-- created a temporary table, saving the acquired information Create Table #TB (ln nvarchar (128), PN NVARCHAR (260), TP Char (1), FGN NVARCHAR (128) , SZ Numeric (20, 0), MSZ Numeric (20, 0)) - Get Information Insert INTO #TB EXEC ('RESTORE FileListonly from Disk] Declare # from backup file F Cursor for select ln, tp from # p @f fetch next from #f Into @ lfn, @ t @ @ i = 0 while @@ fetch_status = 0 begin select @ SQL = @ SQL ', Move' '' @ LFN '' 'to' '' @ dbpath @ dbname cast (@i as varchar) case @tp when 'd' Ten '.mdf' 'Else' .ldf '' end, @ i = @ i 1 fetch next from #f Into @ lfn, @ TP end close #f deallocate #fnd
- Close User Process Processing if @ Overexist = 1 and @ Killuser = 1begin Declare @Spid Varchar (20) Declare #SPID CURSOR for SELECT SPID = CAST (SPID As Varchar (20)) from master..sysprocesses where dbid = dB_ID ( @dbname) open #spid fetch next from #spid into @spid while @@ fetch_status = 0 begin exec ( 'kill' @ spid) fetch next from #spid into @spid end close #spid deallocate # spidend-- recover the database exec (@SQL) GO / * - Call Example - Complete Recovery Database EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ DBNAME = 'DB'
- Difference backup recovery EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ dbname = 'db', @ retype = 'dbnor'Exec p_backupdb @ bkfile =' c: /db_20031015_df.bak ', @ dbname =' DB ', @ rettype =' DF '
- Log Backup Restore EXEC P_RESTOREDB @ bkfile = 'c: /db_20031015_db.bak', @ dbname = 'db', @ retype = 'dbnor'exec p_backupdb @ bkfile =' c: /db_20031015_log.bak ', @ dbname =' DB ', @ rettype =' log '
- * / 4, create jobs
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_createjob]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_createjob] Go
Create Proc P_createJob @ JobName VARCHAR (100), - Job Name @SQL VARCHAR (8000), - The command @dbname sysname = '', - default is the current database name @freqType varchar (6) = ' Day ', time period, month, week week, day @fsinterval int = 1, - relative to daily repetition @time int = 170000 - start execution time, for repeated jobs, 0 points to 23:59 ASIF Isnull (@dbname, ') =' 'set @ dbname = db_name ()
- Create Jobs EXEC MSDB..SP_ADD_JOB @ Job_Name = @ JobName
- create a job step exec msdb..sp_add_jobstep @ job_name = @ jobname, @step_name = 'Data Processing', @subsystem = 'TSQL', @ database_name = @ dbname, @command = @sql, @retry_attempts = 5, - Retry Tips @retry_interval = 5 - Retry Interval - Create Scheduling Declare @ftype Int, @ fstype Int, @ ffactor Intselect @ ftype = Case @freqType When 'Day' Ten 4 WHEN 'Week' Then 8 WHEN 'MONTH' THEN 16 end, @ fstype = case @fsinterval when 1 Then 0 else 8 endif @fsinterval <> 1 set @ Time = 0set @ ffactor = case @freqtype when 'day' Then 0 else 1 End
EXEC MSDB..SP_ADD_JOBSCHEDULE @ Job_Name = @ JobName, @name = 'Time Arranger', @freq_type = @ ftype, - Daily, 8 week, 16 month @freq_interval = 1, - Repeat execution time @ freq_subday_type = @ FSTYPE, - Do you repeat @ freq_subday_interval = @ fsinterval, - Repeat cycle @factor, @ Active_Start_time = @ Time - 17:00:00 in the afternoon execution
Go
/ * - Call example
- Monthly Job EXEC P_CREATEJOB @ JobName = 'mm', @ SQL = 'SELECT * from syscolumns', @freqtype = 'MONTH'
- Weekly Job EXEC P_CREATEJOB @ JobName = 'WW', @ SQL = 'SELECT * from syscolumns', @freqtype = 'Week'
- Daily execution EXEC P_CREATEJOB @ JobName = 'a', @ SQL = 'SELECT * from Syscolumns'
- Daily execution work, repeated job exec p_createjob @ JobName = 'b', @ SQL = 'select * from syscolumns' every day, @ SQL = 'Select * from syscolumns', @ fsinterval = 4
- * /
/ * - Application case - Backup program: Full backup (once every Sunday) Difference backup (backup every day) log backup (backup every 2 hours)
Call the above stored procedure to implement - * / - Complete backup (once every Sunday)
Declare @SQL VARCHAR (8000) Set @ SQL = 'EXEC P_BACKUPDB @dbname =' 'username' '' EXEC P_CREATEJOB @ JobName = 'Weekly Backup', @ SQL = @ SQL, @freqType = 'Week' - Difference Backup (Back up every day)
DECLARE @SQL VARCHAR (8000) SET @ SQL = 'EXEC P_BACKUPDB @dbname =' 'Username' ', @ bktype =' 'DF' '' EXEC P_CREATEJOB @ JobName = 'Daily Backup', @ SQL = @ SQL, @ Freqtype = 'day'
- Log backup (backup every 2 hours) Declare @SQL varchar (8000) set @ SQL = 'exec p_backupdb @dbname =' 'username' ', @ bktype =' 'log' 'EXEC P_CREATEJOB @ JobName =' 2 hours log backup ', @ SQL = @ SQL, @freqtype =' day ', @ fsinterval = 2
5, backup the entire database create proc backupall @Type varchar (15) = 'DB'AS
Declare @count int, @ name varchar (100) Create Table #aa (id int identity (1, 1), name varchar (100)) Insert #aa (name) select name from master..sysdatabasesset @count = @@ rowcountwhile @count> 0 Begin select @ name = name from #aa where id = @ count exec p_backupdb @ dbname = @ name, @ bktype = @ Type set @ count = @ count-1 enddrop table # aago / * call instance 1, complete Backup All Databases Exec Backupall 'DB'2, Difference Backup All Databases Exec Backupall' DF'3, Log Backup All Databases Exec Backupall 'Log' * /
