T-SQL语句实现数据库备份与还原
T-SQLT-SQL 语句实现数据库备份与还原语句实现数据库备份与还原-利用 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_getdbpathGOcreate function f_getdbpath(dbname sysname)returns nvarchar(260)asbegindeclare re nvarchar(260)if dbname is null or db_id(dbname) is nullselect re=rtrim(reverse(filename) from mastersysdatabases where name='master'elseselect re=rtrim(reverse(filename) from mastersysdatabases where name=dbnameif dbname is nullset re=reverse(substring(re,charindex('',re)+5,260)+'BACKUP'elseset re=reverse(substring(re,charindex('',re),260)return(re)endgo/*2.-备份数据库 移动电玩城 http:/www.44226.net*/*-调用示例-备份当前数据库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_backupdbGOcreate proc p_backupdbdbname sysname='', -要备份的数据库名称,不指定则备份当前数据库bkpath nvarchar(260)='', -备份文件的存放目录,不指定则使用 SQL 默认的备份目录bkfname nvarchar(260)='', -备份文件名,文件名中可以用DBNAME代表数据库名,DATE代表日期,TIME代表时间bktype nvarchar(10)='DB', -备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份appendfile bit=1 -追加/覆盖备份文件asdeclare 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(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' endprint sqlexec(sql)go/*3.-恢复数据库*/*-调用示例-完整恢复数据库exec p_RestoreDb bkfile='c:db_20031015_db.bak',dbname='db'-差异备份恢复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'-日志备份恢复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_RestoreDbGOcreate proc p_RestoreDbbkfile nvarchar(1000), -定义要恢复的备份文件名dbname sysname='', -定义恢复后的数据库名,默认为备份的文件名dbpath nvarchar(260)='', -恢复后的数据库存放目录,不指定则为 SQL 的默认数据目录retype nvarchar(10)='DB', -恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复filenumber int=1, -恢复的文件号overexist bit=1, -是否覆盖已经存在的数据库,仅retype 为killuser bit=1 -是否关闭用户使用进程,仅overexist=1 时有效asdeclare sql varchar(8000)-得到恢复后的数据库名if isnull(dbname,'')=''select sql=reverse(bkfile),sql=case when charindex('.',sql)=0 then sqlelse substring(sql,charindex('.',sql)+1,1000) end,sql=case when charindex('',sql)=0 then sqlelse left(sql,charindex('',sql)-1) end,dbname=reverse(sql)-得到恢复后的数据库存放目录if isnull(dbpath,'')='' set dbpath=dbo.f_getdbpath('')-生成数据库恢复语句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' endprint sql-添加移动逻辑文件的处理if retype='DB' or retype='DBNOR'begin-从备份文件中获取逻辑文件名declare lfn nvarchar(128),tp char(1),i int-创建临时表,保存获取的信息create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0)-从备份文件中获取信息 棋牌评测网 http:/www.77884.netinsert into #tb exec('restore filelistonly from disk='''+bkfile+'''')declare #f cursor for select ln,tp from #tbopen #ffetch next from #f into lfn,tpset i=0while fetch_status=0beginselect sql=sql+',move '''+lfn+''' to '''+dbpath+dbname+cast(i as varchar)+case tp when 'D' then '.mdf''' else '.ldf''' end,i=i+1fetch next from #f into lfn,tpendclose #fdeallocate #fend-关闭用户进程处理if overexist=1 and killuser=1begindeclare spid varchar(20)declare #spid cursor forselect spid=cast(spid as varchar(20) from mastersysprocesses where dbid=db_id(dbname)open #spidfetch next from #spid into spidwhile fetch_status=0beginexec('kill '+spid)fetch next from #spid
