Use the T-SQL statement to implement the database backup and restore functionality
Reflected four knowledge points in SQL Server:
1. Get the default directory on the SQL Server server
2. Back up the use of SQL statements
3. Restore the use of the SQL statement, while considering the processing of other user processes when mandatory recovery
4. Job creation SQL statement
/*1. - Get the file directory @dbname to obtain the database name to obtain the database name if the specified data does not exist, return the default data directory set when installing SQL If specified NULL, return the default SQL backup directory name - Zou Jian 2003.10 (Please keep this information) - * /
/ * - Call the 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) - * / if EXISTS (Select * from dbo.sysObjects where id = Object_ID (n '[dbo]. [f_getdbpath]') and xtype in (n'fn ', n'if ") 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
/*2.-- Backup Database
- Zou Jiansheng 2003.10 (Please keep this information when referenced) - * /
/ * - 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'
- * /
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] GOcreate proc p_backupdb @dbname sysname = '', - To back up the database name, not specified, back up the current database @Bkpath nVarchar (260) = '', backup file storage directory, not specified, using 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 - Additional / 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 'INT' End Print @SQL EXEC (@SQL) GO
/*3.-- Restore the database
- Zou Jiansheng 2003.10 (Please keep this information when referenced) - * /
/ * - 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 recovery EXEC P_RESTOREDB @ bkfile =' c: /db_20031015_db.bak ', @ retype =' dbnor'Exec p_backupdb @ bkfile = 'C: / DB_20031015_log.bak ', @ dbname =' db ', @ rettype =' log '
- * /
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 to put the directory if isnull (@dbpath, '') = '' set @ dbpath = dbo.f_getdbpath ('') - Generate Database Recovery Statement Set @ SQL = 'Restore' Case @retype by 'Log' TEN 'LOG' Else 'Database' end @ dbname 'from disk =' ' @ bkfile ' '' 'with file =' cast (@filenumber as varchar) case when @ Overexist = 1 AND @RETYPE IN ('DB', 'DBNOR') THEN ', REPLACE' ELSE 'END CASE @Retype when' DBNOR 'TEN', NORECOVERY 'ELSE', Recovery 'endprint @ SQL - Add mobile logical file Processing if @ Retype = 'db' or @ rettype = 'dbnor'begin - Gets logic filename from backup file Declare @LFN NVARCHAR (128), @ TP char (1), @ i int
- Create a temporary table, save 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 from backup file Insert Into #tb Exec ('RESTORE FILELISTONLY from Disk =' ' @ bkfile ' '') Declare #f Cursor for Select LN, TP from #tb Open # fetch next from #f Into @ lfn, @ TP set @ i = 0 while @@ fetch_status = 0 begin select @ SQL = @ SQL ', Move' '' '' ' @ dbpath @ @ @ @ @ @ dbpath @ DBNAME CAST (@i as varchar) case @tp when 'd' Ten '.mdf' '' Else '' '' '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
/*4.-- Create a job
- Zou Jiansheng 2003.10 (Please keep this information when referenced) - * /
/ * - 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
- * / 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
/ * - 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 - * /
Declare @SQL VARCHAR (8000) - Full Backup (once every Sunday) Set @ SQL = 'exec p_backupdb @ dbname =' '' 'EXEC P_CREATEJOB @ JobName =' Weekly Backup ', @ SQL @ freqtype = 'week'
- Difference backup (backup every day) set @ SQL = 'exec p_backupdb @ dbname =' 'To back up the database name', @ bktype = 'df''Exec p_createjob @ JobName =' Daily backup ', @ SQL, @freqtype = 'day'
- Log Backup (backup every 2 hours) set @ SQL = 'exec p_backupdb @ dbname =' '' to back up the database name ', @ bktype =' log''Exec p_createjob @ JobName = 'Every 2 hours log backup' , @ SQL, @freqtype = 'day', @ fsinterval = 2 / * - Application case 2
Production data core library: Product
The backup scheme is as follows: 1. Set three jobs, separately back up the Produce library, back up, a monthly backup 2. New three new libraries, named: daily backup, weekly backup, monthly backup 3. Establish three jobs, restore three backup libraries to three new libraries.
OBJECTIVE: When the user has any data loss in the Produce library, the corresponding TABLE data can be imported from the three backup libraries above. - * /
Declare @SQL VARCHAR (8000)
--1. Establish a monthly backup and generated monthly backup database, per month, 16:40 pm every month: set @ SQL = 'declare @path nvarchar (260), @fname nvarchar (100) set @fname = 'Productue _' ' Convert (VARCHAR (10), getdate (), 112) ' '_ m.bak''Set @ path = dbo.f_getdbpath (null) @ fname
- Backup EXEC P_BACKUPDB @dbname = '' Product ', @ bkfname = @ fname
- Generate a monthly new library EXEC P_RESTOREDB @ bkfile = @ Path, @ dbname = '' Product_ month ''
- Restore the underlying database for week database EXEC P_RESTOREDB @ bkfile = @ path, @ dbname = '' Product_ Week ', @ rettype =' 'dbnor' '
- Restore the daily database EXEC P_RESTOREDB @ bkfile = @ path, @ dbname = '' product_ 日 ', @ retype =' 'dbnor' 'exec p_createjob @ JobName =' Monthly Backup ', @ SQL , @ freqtype = 'MONTH', @ Time = 164000
--2. Establish a weekly differential backup and build a weekly backup database, at 17:00 pm on Sunday: set @ SQL = 'declare @path nvarchar (260), @ fname nvarchar (100) set @ fname = '' Product_ '' Convert (varchar (10), getdate (), 112) '' _ w.bak''Set @ path = dbo.f_getdbpath (null) @ fname
- Difference backup EXEC P_BACKUPDB @dbname = '' product '', @ bkfname = @ fname, @ bktype = '' DF '- Difference Recovery Week Database EXEC P_BACKUPDB @ bkfile = @ Path, @ dbname =' 'Product_ Week '', @ rettype = '' DF '' EXEC P_CREATEJOB @ JobName = 'Weekly Difference Backup', @ SQL, @freqType = 'Week', @ Time = 170000
--3. Establish daily log backup and build day backup database, every Sunday at 17:15: set @ SQL = 'declare @path nvarchar (260), @ fname nvarchar (100) set @ fname = '' Product_ '' Convert (VARCHAR (10), Getdate (), 112) '' _ L.bak''Set @ Path = dbo.f_getdbpath (null) @ fname
- Log Backup EXEC P_BACKUPDB @dbname = '' Product '', @ bkfname = @ fname, @ bktype = '' log ''
- Log Restoration Day Database EXEC P_BACKUPDB @ bkfile = @ path, @ dbname = '' product_ 日 ', @ rettype =' 'log' '' exec p_createjob @ JobName = 'weekly differential backup', @ SQL, @ FreqType = 'day', @ Time = 171500
More information Refer to post I published on 9CBS:
Http://expert.9cbs.net/expert/topic/2359/2359124.xml?temp=.7861292
