Database backup and restore processing __ Database

Using T-SQL statement to realize the function of database backup and restore

Embodies four of the knowledge points in SQL Server:

1. Get the default directory on the SQL Server server

2. Use of Backup SQL statements

3. Restore the use of SQL statements while considering the process of shutting down other user processes while forced recovery

4. Use of job creation SQL statements

/*1.--get the file directory of the database

@dbname Specify the name of the database to get the directory
Returns the default data directory set when installing SQL if the specified data does not exist
If NULL is specified, the default SQL backup directory name is returned
--Jiangjian 2003.10 (please keep this information for reference)--*/

/*--Call Example
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 ', n ' TF '))
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 =rtrim (reverse (filename)) from master. sysdatabases where Name= @dbname

If @dbname is null
Set @re =reverse (substring (@re, charindex ('/', @re) +5,260)) + ' BACKUP '
Else
Set @re =reverse (substring (@re, charindex ('/', @re), 260)
Return (@re)
End
Go

/*2.--Backup Database

--Jiangjian 2003.10 (please keep this information when referencing)--*/

/*--Call Example

--Back up the current database
exec p_backupdb @bkpath = ' c:/', @bkfname = ' Db_/date/_db.bak '

--Differential backup current database
exec p_backupdb @bkpath = ' c:/', @bkfname = ' Db_/date/_df.bak ', @bktype = ' DF '

--Back up the 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]
Go

Create proc P_backupdb
@dbname sysname= ',--the name of the database to be backed up, and the current database is not specified
@bkpath nvarchar (260) = ',--the directory where the backup files are stored, and the SQL default backup directory is not specified
@bkfname nvarchar (260) = ',--backup file name, file name can use/dbname/to represent database name,/date/representative date,/time/representative time
@bktype nvarchar = ' db ',--Backup type: ' DB ' backup database, ' DF ' differential backup, ' Log ' log backup
@appendfile bit=1--Append/overwrite backup files
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 ' then ' differential, ' else ' end
+case @appendfile when 1 Then ' noinit ' Else ' INIT '
Print @sql
EXEC (@sql)
Go

/*3.--Recovery Database

--Jiangjian 2003.10 (please keep this information when referencing)--*/

/*--Call Example
--Full Recovery database
exec p_restoredb @bkfile = ' C:/db_20031015_db.bak ', @dbname = ' db '

--Differential 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 ', @retype = ' DF '

--Log backup recovery
exec p_restoredb @bkfile = ' C:/db_20031015_db.bak ', @dbname = ' db ', @retype = ' Dbnor '
exec p_backupdb @bkfile = ' C:/db_20031015_log.bak ', @dbname = ' db ', @retype = ' 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 the backup file name to be recovered
@dbname sysname= ',--Define the restored database name, default to the file name of the backup
@dbpath nvarchar (260) = ',--restored database storage directory, unspecified SQL default data directory
@retype nvarchar = ' db ',--recovery type: ' DB ' finished recovery database, ' Dbnor ' for differential recovery, log recovery for full recovery, ' DF ' differential backup recovery, ' log ' log recovery
@filenumber int=1,--Recovered file number
@overexist Bit=1,--whether to overwrite a database that already exists, @retype only
@killuser Bit=1--whether to turn off user-use processes, only when @overexist=1 is valid
As
DECLARE @sql varchar (8000)

--The restored 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 the restored database storage directory
If IsNull (@dbpath, ') = ' Set @dbpath =dbo.f_getdbpath (')

--Generate Database recovery statements
Set @sql = ' Restore ' +case @retype when ' log ' then ' 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 ' then ', NORECOVERY ' else ', RECOVERY ' end
Print @sql
--Add the processing of the mobile logical file
If @retype = ' DB ' or @retype = ' Dbnor '
Begin
--Get the logical file name from the backup file
declare @lfn nvarchar (128), @tp char (1), @i int

 --creates a temporary table, saves the obtained information
 create table #tb (ln nvarchar (128), PN nvarchar (a), TP char (1), FGN nvarchar (128), SZ Numeric (20,0), Msz Numeric (20,0))
 --get information from backup files
 insert into #tb exec (' Restore FILELISTONLY from disk= ' + @bkfile + ']
 declare #f cursor for select LN,TP from #tb
 open #f
 fetch next to #f into @ LFN, @tp
 set @i=0
 while @ @fetch_status =0
 begin
  select @sql = @sql + ', move ' ' + @lfn + ' "to" + @dbpath + @dbname +cast (@i as varchar)
   +case @tp when ' D ' then '. mdf ' Else '. End
   ,@i=@i+1
  fetch next to #f into @lfn, @tp
 end
 close #f
 deallocate #f
End

--Close User process processing
If @overexist =1 and @killuser =1
Begin
DECLARE @spid varchar (20)
DECLARE #spid cursor FOR
Select Spid=cast (spid as varchar) 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 #spid
End

--Restoring the database
EXEC (@sql)

Go

/*4.--Create Job

--Jiangjian 2003.10 (please keep this information when referencing)--*/

/*--Call Example

--Jobs performed on a monthly basis
exec p_createjob @jobname = ' mm ', @sql = ' select * from syscolumns ', @freqtype = ' month '

--Jobs performed on a weekly basis
exec p_createjob @jobname = ' ww ', @sql = ' select * from syscolumns ', @freqtype = ' Week '

--Daily Jobs performed
exec p_createjob @jobname = ' a ', @sql = ' select * from syscolumns '

--a daily job that repeats every 4 hours a day
exec p_createjob @jobname = ' B ', @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 to be executed
@dbname sysname= ',--default view of current database name
@freqtype varchar (6) = ' Day ',--time period, month month, Week week, days
@fsinterval int=1--relative to the number of repetitions per day
@time int=170000--Start execution time, for recurring jobs, from 0 to 23:59
As
If IsNull (@dbname, ') = ' Set @dbname =db_name ()

--Create Job
EXEC msdb.. Sp_add_job @job_name = @jobname

--Create job step
EXEC msdb.. Sp_add_jobstep @job_name = @jobname,
@step_name = ' data processing ',
@subsystem = ' TSQL ',
@database_name = @dbname,
@command = @sql,
@retry_attempts = 5--Number of retries
@retry_interval = 5--Retry interval

--Create a schedule
declare @ftype int, @fstype int, @ffactor int
Select @ftype =case @freqtype when ' Day ' then 4
When ' Week ' then 8
When ' month ' then
, @fstype =case @fsinterval when 1 then 0 else 8 end
If @fsinterval <>1 set @time =0
Set @ffactor =case @freqtype when ' Day ' then 0 else 1 end

EXEC msdb.. sp_add_jobschedule @job_name = @jobname,
@name = ' time schedule ',
@freq_type = @ftype,--daily, 8 per week, 16 per month
@freq_interval = 1,--Repeat execution times
@freq_subday_type = @fstype,--Repeated execution
@freq_subday_interval = @fsinterval,--Recurrence cycle
@freq_recurrence_factor = @ffactor,
@active_start_time = @time--17:00:00 Division execution

Go

/*--Application Case--backup scenario:
Full backup (once per Sunday) + differential backup (once a day) + log backup (back up every 2 hours)

Invoke the above stored procedure to implement the
--*/

DECLARE @sql varchar (8000)
--full backup (once per Sunday)
Set @sql = ' exec p_backupdb @dbname = ' database name to be backed up '
exec p_createjob @jobname = ' Weekly backup ', @sql, @freqtype = ' Week '

--Differential backups (back up once a day)
Set @sql = ' exec p_backupdb @dbname = ' database name to be backed up ', @bktype = ' DF '
exec p_createjob @jobname = ' Daily differential backup ', @sql, @freqtype = ' Day '

--Log backups (backed up every 2 hours)
Set @sql = ' exec p_backupdb @dbname = ' database name to be backed up ', @bktype = ' LOG '
exec p_createjob @jobname = ' Log backups every 2 hours ', @sql, @freqtype = ' Day ', @fsinterval =2

/*--Application Case 2

Production Data Core library: Produce

The backup scenario is as follows:
1. Set three jobs, daily backup to produce library, weekly backup, monthly backup
2. Create three new libraries, each named: daily backup, weekly backup, monthly backup
3. Set up three jobs and restore three backup libraries to the above three new libraries respectively.

Purpose: When a 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 build monthly backup database of the job, every 1 days a month 16:40:
Set @sql = '
declare @path nvarchar (@fname), nvarchar (100)
Set @fname = ' Produce_ ' +convert (varchar), GETDATE (), 112) + ' _m.bak '
Set @path =dbo.f_getdbpath (null) + @fname

--Backup
exec p_backupdb @dbname = ' produce ', @bkfname = @fname

--Generate monthly new library based on backup
exec p_restoredb @bkfile = @path, @dbname = ' Produce_ month '

--Prepare the underlying database for weekly database recovery
exec p_restoredb @bkfile = @path, @dbname = ' Produce_ week ', @retype = ' Dbnor '

--Prepare the underlying database for daily database recovery
exec p_restoredb @bkfile = @path, @dbname = ' Produce_ Day ', @retype = ' Dbnor '
'
exec p_createjob @jobname = ' monthly backup ', @sql, @freqtype = ' month ', @time =164000

--2. Create weekly differential backups and generate weekly backup database jobs, every Sunday 17:00:
Set @sql = '
declare @path nvarchar (@fname), nvarchar (100)
Set @fname = ' Produce_ ' +convert (varchar), GETDATE (), 112) + ' _w.bak '
Set @path =dbo.f_getdbpath (null) + @fname

--Differential backup
exec p_backupdb @dbname = ' produce ', @bkfname = @fname, @bktype = ' DF '

--Differential Recovery Week database
exec p_backupdb @bkfile = @path, @dbname = ' Produce_ week ', @retype = ' DF '
'
exec p_createjob @jobname = ' Weekly differential backup ', @sql, @freqtype = ' Week ', @time =170000

--3. Establish a daily log backup and build day backup database of the job, every Sunday 17:15 minutes:
Set @sql = '
declare @path nvarchar (@fname), nvarchar (100)
Set @fname = ' Produce_ ' +convert (varchar), GETDATE (), 112) + ' _l.bak '
Set @path =dbo.f_getdbpath (null) + @fname

--Log backup
exec p_backupdb @dbname = ' produce ', @bkfname = @fname, @bktype = ' LOG '

--Log Recovery Day database
exec p_backupdb @bkfile = @path, @dbname = ' Produce_ Day ', @retype = ' LOG '
'
exec p_createjob @jobname = ' Weekly differential backup ', @sql, @freqtype = ' Day ', @time =171500

For more information, refer to my post on CSDN:

http://expert.csdn.net/Expert/topic/2359/2359124.xml?temp=.7861292