Job操作：数据库备份与还原处理

--利用T-SQL语句,实现数据库的备份与还原的功能

--体现了SQL Server中的四个知识点：

--1．   获取SQL Server服务器上的默认目录

--2．   备份SQL语句的使用

--3．   恢复SQL语句的使用，同时考虑了强制恢复时关闭其他用户进程的处理

--4．   作业创建SQL语句的使用

 

/*1.--得到数据库的文件目录

 

 @dbname 指定要取得目录的数据库名

   如果指定的数据不存在,返回安装SQL时设置的默认数据目录

   如果指定NULL,则返回默认的SQL备份目录名

*/

 

/*--调用示例

 select 数据库文件目录=dbo.f_getdbpath('tempdb')

  ,[默认SQL SERVER数据目录]=dbo.f_getdbpath('')

  ,[默认SQL SERVER备份目录]=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.--备份数据库*/

 

/*--调用示例

 

--备份当前数据库

exec p_backupdb @bkpath='c:/',@bkfname='db_/DATE/_db.bak'

 

--差异备份当前数据库

exec p_backupdb @bkpath='c:/',@bkfname='db_/DATE/_df.bak',@bktype='DF'

 

--备份当前数据库日志

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='', --要备份的数据库名称,不指定则备份当前数据库

@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录

@bkfname nvarchar(260)='', --备份文件名,文件名中可以用/DBNAME/代表数据库名,/DATE/代表日期,/TIME/代表时间

@bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份

@appendfile bit=1 --追加/覆盖备份文件

as

begin

 declare @sql varchar(8000)

 if isnull(@dbname,'')='' 

set @dbname=db_name()

 if isnull(@bkpath,'')='' 

set @bkpath=dbo.f_getdbpath(nul