use
Master
go
if
object_ID
(
'sp_RestoreDB'
)
is
not
null
Drop
Procedure
sp_RestoreDB
go
/****************************************************************************************************************************************************************
%%
存儲過程名：
sp_RestoreDB
%%
輸入參數：
@Path,@DBs,@DefaultPath
%%
輸出參數：
%%
功能：還原文件夾路徑下的備份
****************************************************************************************************************************************************************
%%
編寫：
Roy
2009-09-24
****************************************************************************************************************************************************************/
Create
Procedure
sp_RestoreDB
(
@Path nvarchar
(
1000)
--
路徑如：
G:/
,
@DBs nvarchar
(
2000)=null
--
指定要還原的數據庫如：
HR,SalesOrder;
用逗號分隔
,
不指定時按備份文件中的數據庫還原
,
@DefaultPath nvarchar
(
2000)=null
--
通過還原文件生成數據時，指定數據庫文件存放路徑
,
不指定時取數據最大的一個作為路徑
)
as
set
nocount
on
;
declare
@Sql nvarchar
(
max
),
@Path2 nvarchar
(
1000),
@Path3 nvarchar
(
1000)
set
@Path3=
replace
(
@Path,
'"'
,
''
)
declare
@FileExist table
(
Col1 int
,
Col2 int
,
Col3 int
)
insert
@FileExist exec
xp_fileexist
@Path3
if
@DefaultPath is
not
null
begin
set
@Path3=
replace
(
@DefaultPath,
'"'
,
''
)
insert
@FileExist exec
xp_fileexist @Path3
end
if
exists(
select
1 from
@FileExist where
Col2=
0)
begin
raiserror
50001 N'
指定文件路徑不正確
,
請確認
!'
return
end
select
top
1 @DefaultPath=
isnull
(
@DefaultPath,left(
Physical_name,
len
(
Physical_name)-
charindex
(
'/'
,
reverse
(
Physical_name))+
1))
from
sys.master_files
order
by
Database_id desc
if
object_id
(
'Tempdb..#BackFile'
)
is
not
null
drop
table
#BackFile
create
table
#BackFile(
FName nvarchar
(
1000))
if
object_id
(
'Tempdb..#BackDB'
)
is
not
null
drop
table
#BackDB
create
table
#BackDB
(
ID int
identity
(
1,
1)
,
BackupName
nvarchar
(
128)
,
BackupDescription
nvarchar
(
255)
,
BackupType
smallint
,
ExpirationDate
datetime
,
Compressed
tinyint
,
Position
smallint
,
DeviceType
tinyint
,
UserName
nvarchar
(
128)
,
ServerName
nvarchar
(
128)
,
DatabaseName
nvarchar
(
128)
,
DatabaseVersion
int
,
DatabaseCreationDate
datetime
,
BackupSize
numeric
(
20,
0)
,
FirstLSN
numeric
(
25,
0)
,
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
uniqueidentifier
NULL
,
PathName nvarchar
(
2000)
)
if
object_id
(
'Tempdb..#TmpBackDB'
)
is
not
null
drop
table
#TmpBackDB
create
table
#TmpBackDB
(
BackupName
nvarchar
(
128)
,
BackupDescription
nvarchar
(
255)
,
BackupType
smallint
,
ExpirationDate
datetime
,
Compressed
tinyint
,
Position
smallint
,
DeviceType
tinyint
,
UserName
nvarchar
(
128)
,
ServerName
nvarchar
(
128)
,
DatabaseName
nvarchar
(
128)
,
DatabaseVersion
int
,
DatabaseCreationDate
datetime
,
BackupSize
numeric
(
20,
0)
,
FirstLSN
numeric
(
25,
0)
,
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
uniqueidentifier
NULL
)
if
object_id
(
'Tempdb..#BackDB2'
)
is
not
null
drop
table
#BackDB2
create
table
#BackDB2
(
ID int
identity
(
1,
1)
,
LogicalName
nvarchar
(
128)
,
PhysicalName
nvarchar
(
260)
,
Type
char
(
1)
,
FileGroupName
nvarchar
(
128)
,
Size
numeric
(
20,
0)
,
MaxSize
numeric
(
20,
0)
,
FileID
bigint
,
CreateLSN
numeric
(
25,
0)
,
DropLSN
numeric
(
25,
0)
NULL
,
UniqueID
uniqueidentifier
,
ReadOnlyLSN
numeric
(
25,
0)
NULL
,
ReadWriteLSN
numeric
(
25,
0)
NULL
,
BackupSizeInBytes
bigint
,
SourceBlockSize
int
,
FileGroupID
int
,
LogGroupGUID
uniqueidentifier
NULL
,
DifferentialBaseLSN
numeric
(
25,
0)
NULL
,
DifferentialBaseGUID
uniqueidentifier
,
IsReadOnly
bit
,
IsPresent
bit
,
DatabaseName nvarchar
(
128)
,
Position smallint
,
PathName nvarchar
(
2000)
)
if
object_id
(
'Tempdb..#TmpBackDB2'
)
is
not
null
drop
table
#TmpBackDB2
create
table
#TmpBackDB2
(
LogicalName
nvarchar
(
128)
,
PhysicalName
nvarchar
(
260)
,
Type
char
(
1)
,
FileGroupName
nvarchar
(
128)
,
Size
numeric
(
20,
0)
,
MaxSize
numeric
(
20,
0)
,
FileID
bigint
,
CreateLSN
numeric
(
25,
0)
,
DropLSN
numeric
(
25,
0)
NULL
,
UniqueID
uniqueidentifier
,
ReadOnlyLSN
numeric
(
25,
0)
NULL
,
ReadWriteLSN
numeric
(
25,
0)
NULL
,
BackupSizeInBytes
bigint
,
SourceBlockSize
int
,
FileGroupID
int
,
LogGroupGUID
uniqueidentifier
NULL
,
DifferentialBaseLSN
numeric
(
25,
0)
NULL
,
DifferentialBaseGUID
uniqueidentifier
,
IsReadOnly
bit