if(len(@DirSQL)<1)
set@DirSQL=N'EXEC master.sys.xp_create_subdir N'''+SUBSTRING(@PhysicalName,1,@endpos-1)+''''
else
set@DirSQL=@DirSQL+char(13)+N'EXEC master.sys.xp_create_subdir N'''+SUBSTRING(@PhysicalName,1,@endpos-1)+''''
---Check Drives
set@checkdrive=1
execmaster.dbo.Usp_Check_DriveExists @PhysicalName,@checkdriveoutput
if(@checkdrive<>1)
GotoExitFLag
set@LastPhysicalName=SUBSTRING(@PhysicalName,1,@endpos-1);
END
set@SQL=@SQL+char(13)+Char(10)+N' ,Move N'''+@LogicalName+''''+' TO N'''+@PhysicalName+''''
FETCHNEXTFROMdb_file INTO@LogicalName,@PhysicalName
end
set@SQL=@SQL+char(13)+Char(10)+N' ,NOUNLOAD,Recovery,STATS = 10'
if(@IsRun=0)
print( @DirSQL+char(13)+char(10)+'GO'+char(13)+Char(10)+@SQL+char(13))
else
begin
print('-----------Begin Restore Database:'+@dbname+'------------------')
exec(@DirSQL)
exec(@SQL)
print('-----------End Restore Database:'+@dbname+'---------------------'+char(13))
end
closedb_file
deallocatedb_file
ExitFLag:
setnocount off
end
批量还原数据库：
Usemaster
GO
/*=================Usp_RestoreMuiteDataBaseFromPath========================
=====Restore Mutite DataBase File From a Path ======
=====Ken.Guo ======
=====2010.9.10 ======
=====Version: 2005 & 2008 SQL Server ======
=====EXEC Usp_RestoreMuiteDataBaseFromPath 'D:\databack','',0 ======
=========================================================================
*/
CREATEPROCUsp_RestoreMuiteDataBaseFromPath
( @DatabBaseBakPathnvarchar(400)
,@RestoreDataPathnvarchar(400)='' --RESTORE DATABASE PATH
,@IsRunsmallint=0 -- 0 PRINT 1 run
)
AS
BEGIN
setnocount on
DECLARE@BackUpFileNamenvarchar(200)
,@DbNamenvarchar(200)
,@errorinfonvarchar(400)
IFnotexists(SELECT1
FROMmaster.sys.procedures WITH(NOLOCK)
WHERE
name=N'Usp_RestoreDataBaseFormPath'
)
begin
Raiserror50001N'找不到存储过程SP_RestoreDataBaseFormPath '
GotoExitFLag
end
--add path \
if(@DatabBaseBakPathisnotnull) andlen(@DatabBaseBakPath)>1
and(right(@DatabBaseBakPath,1)<>'\')
set@DatabBaseBakPath=@DatabBaseBakPath+'\'
--Check Restore Path and Size >1000M
DECLARE@checkdriveint
SET@checkdrive=1
EXECmaster.dbo.Usp_Check_DriveExists @RestoreDataPath,@checkdriveOUTPUT
IF(@checkdrive<>1)
GotoExitFLag
DECLARE@DirTABLE
(
BackDBFileName nvarchar(100)
,DEPTH int
,[File]int
)
INSERTINTO@DirEXECxp_dirtree @DatabBaseBakPath
,1
,1
DELETEFROM@Dir
WHEREcharindex('.bak',BackDBFileName)=0
ifnotexists(selecttop11from@Dir)
begin
Raiserror50001N'在提供的路径下没有找到合符要求的备份文件'
GotoExitFLag
end
declaredb_file CursorLocal Static Read_Only Forward_Only
for
selectBackDBFileName from@Dir
Opendb_file
FetchNextfromdb_file into@BackUpFileName
while@@FETCH_STATUS=0
begin
--Restore DataBase
set@BackUpFileName=@DatabBaseBakPath+@BackUpFileName
execmaster.dbo.Usp_RestoreDataBaseFo