,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,CodePage smallint
,UnicodeLocaleId int
,UnicodeComparisonStyle int
,CompatibilityLevel tinyint
,SoftwareVendorId int
,SoftwareVersionMajor int
,SoftwareVersionMinor int
,SoftwareVersionBuild int
,MachineName nvarchar(128)
,Flags int
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,COLLATION nvarchar(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData bit
,IsSnapshot bit
,IsReadOnly bit
,IsSingleUser bit
,HasBackupChecksums bit
,IsDamaged bit
,BeginsLogChain bit
,HasIncompleteMetaData bit
,IsForceOffline bit
,IsCopyOnly bit
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0) NULL
,RecoveryModel nvarchar(60)
,DifferentialBaseLSN numeric(25,0) NULL
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription nvarchar(60)
,BackupSetGUID uniqueidentifierNULL
)
INSERTINTO@BakHeaderInfo2005
EXECsp_executesql N'Restore HeaderOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath
insertinto@BakHeaderInfo(DatabaseName)
selectDatabaseName from@BakHeaderInfo2005
insertinto@BakFileList(LogicalName ,PhysicalName)
select LogicalName ,PhysicalName from@BakFileList2005
end
--Check back file info
ifnotexists(select1from@BakFileList) ORnotexists(select1from@BakHeaderInfo)
begin
set@errorinfo=N'取不到备份文件:'+@DatabBaseBakPath+N' 的信息，请检查备份文件是否正确或者版本是否兼容'
Raiserror50001@errorinfo
GotoExitFLag
--Get DataBase Name
SELECTTOP1@dbname=databasename FROM@BakHeaderInfo
ifexists(select1frommaster.sys.databases with(nolock) wherename=@dbname)
begin
set@errorinfo=N'数据库：'+@dbname+N'已经存在，不能还原'
Raiserror50001@errorinfo
GotoExitFLag
end
DECLARE@LogicalNamenvarchar(200),@PhysicalNamenvarchar(400)
,@posint,@endposint,@LastPhysicalNamenvarchar(400)
DECLAREdb_file CURSOR
LOCAL
READ_ONLY
FORWARD_ONLY
STATIC
FOR
SELECT
LogicalName
,PhysicalName
FROM@BakFileList
OPENdb_file
set@DirSQL=''
set@SQL=+N'RESTORE DATABASE '+QUOTENAME(@dbname)+' from disk=N'''+@DatabBaseBakPath+''''
set@SQL=@SQL+char(13)+Char(10)+N' WITH FILE=1 '
FETCHNEXTFROMdb_file INTO@LogicalName,@PhysicalName
WHILE@@FETCH_STATUS=0
begin
---Get DB PhysicalName
set@endpos=0
whileCHARINDEX('\',@PhysicalName)>0
begin
set@pos=CHARINDEX('\',@PhysicalName,@endpos)
if(@pos=0)
break;
set@endpos=@pos+1;
end
--create new db path
if(len(@RestoreDataPath)>1)
begin
set@PhysicalName=@RestoreDataPath+@dbname+'\'+SUBSTRING(@PhysicalName,@endpos,LEN(@PhysicalName)-@endpos+1)
set@DirSQL=N'EXEC master.sys.xp_create_subdir N'''+@RestoreDataPath+@dbname+''''
END
else
begin
iflen(@DirSQL)<1OR(SUBSTRING(@PhysicalName,1,@endpos-1)<>@LastPhysicalName)