根据备份文件直接还原数据库
2008-09-02 12:47:11 来源:WEB开发网方法是先提取原数据库名,再提取数据库相关的数据文件(数据文件和日志文件),然后还原数据库。
代码如下:
Usemaster
Go
IfObject_id(’sp_RestoreDataBase’)IsNotNull
dropProcsp_RestoreDataBase
Go
createProcsp_RestoreDataBase
(
@DataBaseBakPathnvarchar(260),
@DataBaseNewPathnvarchar(260)
)
As
SetNocountOn
Declare
@Sqlnvarchar(max),
@DataBasenvarchar(128)
Declare@DataBakHeadertable(BackupNamenvarchar(128),BackupDescriptionnvarchar(255),BackupTypesmallint,ExpirationDatedatetime,Compressedtinyint,Positionsmallint,DeviceTypetinyint,UserNamenvarchar(128),ServerNamenvarchar(128),DatabaseNamenvarchar(128),DatabaseVersionint,DatabaseCreationDatedatetime,BackupSizenumeric(20,0),FirstLSNnumeric(25,0),LastLSNnumeric(25,0),CheckpointLSNnumeric(25,0),DatabaseBackupLSNnumeric(25,0),BackupStartDatedatetime,BackupFinishDatedatetime,SortOrdersmallint,CodePagesmallint,UnicodeLocaleIdint,UnicodeComparisonStyleint,CompatibilityLeveltinyint,SoftwareVendorIdint,SoftwareVersionMajorint,SoftwareVersionMinorint,SoftwareVersionBuildint,MachineNamenvarchar(128),Flagsint,BindingIDuniqueidentifier,RecoveryForkIDuniqueidentifier,Collationnvarchar(128),FamilyGUIDuniqueidentifier,HasBulkLoggedDatabit,IsSnapshotbit,IsReadOnlybit,IsSingleUserbit,HasBackupChecksumsbit,IsDamagedbit,BeginsLogChainbit,HasIncompleteMetaDatabit,IsForceOfflinebit,IsCopyOnlybit,FirstRecoveryForkIDuniqueidentifier,ForkPointLSNnumeric(25,0)NULL,RecoveryModelnvarchar(60),DifferentialBaseLSNnumeric(25,0)NULL,DifferentialBaseGUIDuniqueidentifier,BackupTypeDescriptionnvarchar(60),BackupSetGUIDuniqueidentifierNULL)
Declare@DataBakFileListtable(LogicalNamenvarchar(128),PhysicalNamenvarchar(260),Typechar(1),FileGroupNamenvarchar(128),Sizenumeric(20,0),MaxSizenumeric(20,0),FileIdbigint,createLSNnumeric(25,0),dropLSNnumeric(25,0)NULL,UniqueIDuniqueidentifier,ReadOnlyLSNnumeric(25,0)NULL,ReadWriteLSNnumeric(25,0)NULL,BackupSizeInBytesbigint,SourceBlockSizeint,FileGroupIDint,LogGroupGUIDuniqueidentifierNULL,DifferentialBaseLSNnumeric(25,0)NULL,DifferentialBaseGUIDuniqueidentifier,IsReadOnlybit,IsPresentbit)
insertInto@DataBakHeader
execsp_executesqlN’RestoreHeaderOnlyFromDisk=@DataBaseBakPath’,N’@DataBaseBakPathnvarchar(260)’,@DataBaseBakPath
insertInto@DataBakFileList
execsp_executesqlN’RestoreFileListOnlyFromDisk=@DataBaseBakPath’,N’@DataBaseBakPathnvarchar(260)’,@DataBaseBakPath
select@DataBase=DatabaseNameFrom@DataBakHeader
select@Sql=Isnull(@Sql+char(13)+char(10),’’)+’Kill’+Rtrim(spid)Frommaster.sys.sysprocesseswheredbid=db_id(@DataBase)
Set@Sql=Isnull(@Sql+char(13)+char(10),’’)+’RestoreDataBase@DataBaseFromDisk=@DataBaseBakPathWith’
select@Sql=@Sql+’Move’’’+LogicalName+’’’To’’’+@DataBaseNewPath+’’+LogicalName+Right(PhysicalName,charindex(’.’,Reverse(PhysicalName)))+’’’,’
From@DataBakFileList
Set@Sql=@Sql+’Replace,Stats=10’
SetNocountOff
Print’还原数据库:’+@DataBase
execsp_executesql@Sql,N’@DataBasenvarchar(128),@DataBaseBakPathnvarchar(260)’,@DataBase,@DataBaseBakPath
e.g:
更多精彩
赞助商链接