/*********************************************************************************************Function：数据库还原(支持自动覆盖现有数据库及自动创建相关路径)Author：BeanDate:2012-09-24注：感谢“小爱”提供帮助*********************************************************************************************/use mastergoif OBJECT_ID('sp_restore','P') is not null drop proc sp_restoregocreate proc sp_restore@dbName varchar(128),@crtDBName varchar(128),@fullDBSourcePath varchar(1024),@dbStorePath varchar(1024)asBeginset nocount ondeclare @result int, --定义变量用来判断文件是否存在@mdfPath varchar(1024),@ldfPath varchar(1024),@crtPath varchar(1024)--//把'/'替换成'\'set @fullDBSourcePath=REPLACE(@fullDBSourcePath,'/','\')set @dbStorePath=REPLACE(@dbStorePath,'/','\')--//得到mdf 和 ldf 生成文件地址 set @mdfPath=@dbStorePath+'\'+@dbName+'.mdf'set @ldfPath=@dbStorePath+'\'+@dbName+'.ldf'/*** 判断路径是否存在 ***/exec sp_configure 'show advanced options',1;reconfigure;exec sp_configure 'xp_cmdshell',1;reconfigure;set @crtPath='mkdir '+replace(@dbStorePath,' ','" "')--//创建路径Exec master.dbo.xp_cmdshell @crtPath,NO_OUTPUT/*** 判断数据库是否存在 ***/if exists(select 1 from sys.databases where name =@dbName)Begin--//备份数据库declare @filename varchar(1024)--获取文件名set @fileName=@dbName+replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')+'.bak'--开始备份exec('backup database '+@dbName+' to disk='''+@dbStorePath+'\'+@fileName+'''')--//断开连接exec('alter database '+@dbName+' set single_user with rollback immediate')End/*** bak数据库是否存在 ***/Exec master.dbo.xp_fileexist @fullDBSourcePath,@result out--如果存在if @result=1Beginexec('restore database '+@dbName+' from disk= '''+@fullDBSourcePath+''''+' with replace , move '''+@crtDBName+''' to '''+@mdfPath+''','+ ' move '''+@crtDBName+'_log'''+' to '''+@ldfPath+'''')EndElse BeginRaisError('Can not find bak recourse from the Database Recourse Path.',16,1);Return;EndEndgo--测试脚本--master.dbo.sp_restore 'XJTLU_04201','LANDAV8','D:\Data\bak\XJTLU_042020120911123443.bak','D:\Data'
