/********************************************************************************************* Function:数据库还原(支持自动覆盖现有数据库及自动创建相关路径) Author:Bean Date:2012-09-24 注:感谢“小爱”提供帮助 *********************************************************************************************/ use master go if OBJECT_ID('sp_restore','P') is not null drop proc sp_restore go create proc sp_restore @dbName varchar(128), @crtDBName varchar(128), @fullDBSourcePath varchar(1024), @dbStorePath varchar(1024) as Begin set nocount on declare @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=1 Begin exec( 'restore database '+@dbName+ ' from disk= '''+@fullDBSourcePath+''''+ ' with replace , move '''+@crtDBName+''' to '''+@mdfPath+''','+ ' move '''+@crtDBName+'_log'''+' to '''+@ldfPath+'''' ) End Else Begin RaisError('Can not find bak recourse from the Database Recourse Path.',16,1); Return; End End go --测试脚本 --master.dbo.sp_restore 'XJTLU_04201','LANDAV8','D:\Data\bak\XJTLU_042020120911123443.bak','D:\Data'
数据库还原(支持覆盖数据库、自动创建相关路径)
最新推荐文章于 2022-05-25 11:17:34 发布