数据库备份恢复方案

利用SQL语言,实现数据库的备份/恢复的功能体现了SQLServer中的四个知识点：1．获取SQLServer服务器上的默认目录2．备份SQL语句的使用3．恢复SQL语句的使用，同时考虑了强制恢复时关闭其他用户进程的处理4．作业创建SQL语句的使用/*1.--得到数据库的文件目录@dbname指定要取得目录的数据库名如果指定的数据不存在,返回安装SQL时设置的默认数据目录如果指定NULL,则返回默认的SQL备份目录名----*//*--调用示例select数据库文件目录=dbo.f_getdbpath('tempdb'),[默认SQLSERVER数据目录]=dbo.f_getdbpath(''),[默认SQLSERVER备份目录]=dbo.f_getdbpath(null)--*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_getdbpath]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_getdbpath]GOcreatefunctionf_getdbpath(@dbnamesysname)returnsnvarchar(260)asbegindeclare@renvarchar(260)if@dbnameisnullordb_id(@dbname)isnullselect@re=rtrim(reverse(filename))frommaster..sysdatabaseswherename='master'elseselect@re=rtrim(reverse(filename))frommaster..sysdatabaseswherename=@dbnameif@dbnameisnullset@re=reverse(substring(@re,charindex('\',@re)+5,260))+'BACKUP'elseset@re=reverse(substring(@re,charindex('\',@re),260))return(@re)endgo/*2.--备份数据库--*//*--调用示例--备份当前数据库execp_backupdb@bkpath='c:\',@bkfname='db_\DATE\_db.bak'--差异备份当前数据库execp_backupdb@bkpath='c:\',@bkfname='db_\DATE\_df.bak',@bktype='DF'--备份当前数据库日志execp_backupdb@bkpath='c:\',@bkfname='db_\DATE\_log.bak',@bktype='LOG'--*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_backupdb]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropprocedure[dbo].[p_backupdb]GOcreateprocp_backupdb@dbnamesysname='',--要备份的数据库名称,不指定则备份当前数据库@bkpathnvarchar(260)='',--备份文件的存放目录,不指定则使用SQL默认的备份目录@bkfnamenvarchar(260)='',--备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间@bktypenvarchar(10)='DB',--备份类型:'DB'备份数据库,'DF'差异备份,'LOG'日志备份@appendfilebit=1--追加/覆盖备份文件asdeclare@sqlvarchar(8000)ifisnull(@dbname,'')=''set@dbname=db_name()ifisnull(@bkpath,'')=''set@bkpath=dbo.f_getdbpath(null)ifisnull(@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@bktypewhen'LOG'then'log'else'database'end+@dbname+'todisk='''+@bkpath+@bkfname+'''with'+case@bktypewhen'DF'then'DIFFERENTIAL,'else''end+case@appendfilewhen1then'NOINIT'else'INIT'endprint@sqlexec(@sql)go/*3.--恢复数据库--*//*--调用示例--完整恢复数据库execp_RestoreDb@bkfile='c:\db_20031015_db.bak',@dbname='db'--差异备份恢复execp_RestoreDb@bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'execp_backupdb@bkfile='c:\db_20031015_df.bak',@dbname='db',@retype='DF'--日志备份恢复execp_RestoreDb@bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'execp_backupdb@bkfile='c:\db_20031015_log.bak',@dbname='db',@retype='LOG'--*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_RestoreDb]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropprocedure[dbo].[p_RestoreDb]GOcreateprocp_RestoreDb@bkfilenvarchar(1000),--定义要恢复的备份文件名@dbnamesysname='',--定义恢复后的数据库名,默认为备份的文件名@dbpathnvarchar(260)='',--恢复后的数据库存放目录,不指定则为SQL的默认数据目录@retypenvarchar(10)='DB',--恢复类型:'DB'完事恢复数据库,'DBNOR'为差异恢复,日志恢复进行完整恢复,'DF'差异备份的恢复,'LOG'日志恢复@filenumberint=1,--恢复的文件号@overexistbit=1,--是否覆盖已经存在的数据库,仅@retype为@killuserbit=1--是否关闭用户使用进程,仅@overexist=1时有效asdeclare@sqlvarchar(8000)--得到恢复后的数据库名ifisnull(@dbname,'')=''select@sql=reverse(@bkfile),@sql=casewhencharindex('.',@sql)=0then@sqlelsesubstring(@sql,charindex('.',@sql)+1,1000)end,@sql=casewhencharindex('\',@sql)=0then@sqlelseleft(@sql,charindex('\',@sql)-1)end,@dbname=reverse(@sql)--得到恢复后的数据库存放目录ifisnull(@dbpath,'')=''set@dbpath=dbo.f_getdbpath('')--生成数据库恢复语句set@sql='restore'+case@retypewhen'LOG'then'log'else'database'end+@dbname+'fromdisk='''+@bkfile+''''+'withfile='+cast(@filenumberasvarchar)+casewhen@overexist=1and@retypein('DB','DBNOR')then',replace'else''end+case@retypewhen'DBNOR'then',NORECOVERY'else',RECOVERY'endprint@sql--添加移动逻辑文件的处理if@retype='DB'or@retype='DBNOR'begin--从备份文件中获取逻辑文件名declare@lfnnvarchar(128),@tpchar(1),@iint--创建临时表,保存获取的信息createtable#tb(lnnvarchar(128),pnnvarchar(260),tpchar(1),fgnnvarchar(128),sznumeric(20,0),Msznumeric(20,0))--从备份文件中获取信息insertinto#tbexec('restorefilelistonlyfromdisk='''+@bkfile+'''')declare#fcursorforselectln,tpfrom#tbopen#ffetchnextfrom#finto@lfn,@tpset@i=0while@@fetch_status=0beginselect@sql=@sql+',move'''+@lfn+'''to'''+@dbpath+@dbname+cast(@iasvarchar)+case@tpwhen'D'then'.mdf'''else'.ldf'''end,@i=@i+1fetchnextfrom#finto@lfn,@tpendclose#fdeallocate#fend--关闭用户进程处理if@overexist=1and@killuser=1begindeclare@spidvarchar(20)declare#spidcursorforselectspid=cast(spidasvarchar(20))frommaster..sysprocesseswheredbid=db_id(@dbname)open#spidfetchnextfrom#spidinto@spidwhile@@fetch_status=0beginexec('kill'+@spid)fetchnextfrom#spidinto@spidendclose#spiddeallocate#spidend--恢复数据库exec(@sql)go/*4.--创建作业--*//*--调用示例--每月执行的作业execp_createjob@jobname='mm',@sql='select*fromsyscolumns',@freqtype='month'--每周执行的作业execp_createjob@jobname='ww',@sql='select*fromsyscolumns',@freqtype='week'--每日执行的作业execp_createjob@jobname='a',@sql='select*fromsyscolumns'--每日执行的作业,每天隔4小时重复的作业execp_createjob@jobname='b',@sql='select*fromsyscolumns',@fsinterval=4--*/ifexists(select*fromdbo.sysobjectswhereid=o