●還原單個數(shù)據(jù)庫的存儲過程代碼
根據(jù)上面分析的方法,這里演示我寫的一個還原單個數(shù)據(jù)庫的存儲過程代碼,因為代碼是之前寫的,中間因真實世界的特殊情況,修改成幾個版本的存儲過程。當然,如有可能,你可以根據(jù)自己所在的真實環(huán)境,修改對應部分的代碼,以便滿足自己的需要。
存儲過程sp_RestoreDataBase代碼:
- Use master
- Go
- if object_ID('[sp_restoredatabase]') is not null
- Drop Procedure [sp_restoredatabase]
- Go
- /*
-
- @DatabBaseBakPath nvarchar(260),
- @DatabBaseNewPath nvarchar(260)
- @NewDataBaseName nvarchar(128)
- e.g:
-
- V3.0版本修改說明:
- 1.修正了之前版本在還原包含全文索引文件的時候發(fā)生的錯誤。
- 2.修正了之前版本在還原包含多個數(shù)據(jù)庫文件和日志文件時發(fā)生的錯誤。
- V3.1版本說明,增加了參數(shù)@Keep_Replication,表示是否保存復制設定
- V3.2版本說明,增加了FILE ={ file_number | @file_number } 邏輯判斷,在包含多個備份組,還原最后一個備份組
- V3.3 版本說明 ,還原發(fā)生錯誤時返回 1
- */
- CREATE Proc sp_RestoreDataBase
- (
- @DatabBaseBakPath nvarchar(260),
- @DatabBaseNewPath nvarchar(260),
- @NewDataBaseName nvarchar(128)=null,
- @Keep_Replication bit=0
- )
- As
-
- Set Nocount On
- Begin Try
- Declare
- @DataBaseName nvarchar(128),
- @Sql nvarchar(max),
- @SqlDatabaseRename nvarchar(max),
- @Enter nvarchar(10)
-
- Declare
- @Dir nvarchar(4000),
- @i int
-
-
-
- Exec master.dbo.xp_fileexist @DatabBaseBakPath,@i Output
- If @i=0
- Begin
- Raiserror 50001 N'無效的備份數(shù)據(jù)庫路徑/文件名!'
- Return 1
- End
-
- If Charindex('\\',@DatabBaseNewPath)>0
- Begin
- Raiserror 50001 N'數(shù)據(jù)庫還原路徑中不能含有''\\''!'
- Return 1
- End
- If Right(Rtrim(@DatabBaseNewPath),1)='\'
- Begin
- Raiserror 50001 N'數(shù)據(jù)庫還原路徑的最后一位能含有''\''!'
- Return 1
- End
- Set @Dir='Dir '+@DatabBaseNewPath
- Exec @i=xp_cmdshell @Dir,no_output
- If @i<>0
- Begin
- Raiserror 50001 N'無效的數(shù)據(jù)庫還原路徑!'
- Return 1
- End
- set @DatabBaseNewPath=replace(@DatabBaseNewPath,'"','')
- /*
-
- Declare @BakFileList Table(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)
- */
-
- Declare @BakFileList Table(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,TDEThumbprint varbinary(32))
- Insert Into @BakFileList
- Exec sp_executesql N'Restore FileListOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath
- /*
-
- Declare @BakHeaderInfo Table(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)
- */
-
- Declare @BakHeaderInfo Table(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,CompressedBackupSize numeric(20,0))
- Insert Into @BakHeaderInfo
- Exec sp_executesql N'Restore HeaderOnly From Disk=@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@DatabBaseBakPath
-
-
- If Isnull(@NewDataBaseName,'')>''
- Set @DataBaseName=@NewDataBaseName
- Else
- Begin
- Select @DataBaseName=DatabaseName From @BakHeaderInfo
- End
-
-
- Set @Enter=char(13)+Char(10)
- Select @Sql=Isnull(@Sql+@Enter,'')+'Kill '+Rtrim(spid) From master.sys.sysprocesses Where dbid=db_id(@DataBaseName)
- Exec(@Sql)
-
- Set @Sql=N'Restore DataBase @DataBaseName From Disk=@DatabBaseBakPath With File=??,'
-
- Select @Sql=@Sql+'Move '''+LogicalName+''' To '''+@DatabBaseNewPath+'\'+@DataBaseName+
- Case
- When [Type]='D' And Not Exists(Select 1 From @BakFileList Where [Type]=a.[Type] And FileID<a.FileID) Then ''
- When [Type]='D' Then '_'+(Select Rtrim(Count(*)) From @BakFileList Where [Type]=a.[Type] And FileID<a.FileID)
- When [Type]='L' And Not Exists(Select 1 From @BakFileList Where [Type]=a.[Type] And FileID<a.FileID) Then '_Log'
- When [Type]='L' Then '_Log'+(Select Rtrim(Count(*)) From @BakFileList Where [Type]=a.[Type] And FileID<a.FileID)
- Else ''
- End+Right(PhysicalName,Charindex('.',Reverse(PhysicalName)))+''',',
- @SqlDatabaseRename=Isnull(@SqlDatabaseRename+@Enter,'')+
- Case
- When [Type]='D' And LogicalName=@DataBaseName Then ''
- When [Type]='D' And LogicalName Like @DataBaseName+'[_]%' Then ''
- When [Type]='L' And LogicalName Like @DataBaseName+'[_]Log%' Then ''
- When [Type]='F' Then ''
- Else
- 'Alter DataBase '+Quotename(@DataBaseName)+' Modify File(Name='''+LogicalName+''',NewName='''+@DataBaseName+
- Case
- When [Type]='D' And Not Exists(Select 1 From @BakFileList Where [Type]=a.[Type] And FileID<a.FileID) Then ''
- When [Type]='D' Then '_'+(Select Rtrim(Count(*)) From @BakFileList Where [Type]=a.[Type] And FileID<a.FileID)
- When [Type]='L' And Not Exists(Select 1 From @BakFileList Where [Type]=a.[Type] And FileID<a.FileID) Then '_Log'
- When [Type]='L' Then '_Log'+(Select Rtrim(Count(*)) From @BakFileList Where [Type]=a.[Type] And FileID<a.FileID)
- Else ''
- End+''')'
- End
- From @BakFileList As a
-
-
- Set @Sql=@Sql+'Replace'
-
- If @Keep_Replication=1
- Set @Sql=@Sql+'Keep_Replication'
-
- Declare @sql1 nvarchar(max),
- @MaxPosition int
-
- Select @MaxPosition=Position
- From @BakHeaderInfo As a
- Where a.BackupType=1
- And Not Exists(Select 1 From @BakHeaderInfo Where BackupType=a.BackupType And Position>a.Position)
-
-
- Select @sql1=isnull(@sql1+char(13)+char(10),'')+replace(@sql,'??',rtrim(Position)) +
- Case When Exists(Select 1 From @BakHeaderInfo Where Position>a.Position) Then N',Norecovery' Else N',Recovery' End
- From @BakHeaderInfo As a
- Where Position>=@MaxPosition
- Order By Position
-
- Print '還原數(shù)據(jù)庫: '+@DataBaseName
-
-
-
- Exec sp_executesql @sql1,N'@DataBaseName nvarchar(128),@DatabBaseBakPath nvarchar(260)',@DataBaseName,@DatabBaseBakPath
-
- If @SqlDatabaseRename>''
- Exec(@SqlDatabaseRename)
-
- End Try
- Begin Catch
- Declare @Error nvarchar(1024)
- Set @Error=ERROR_MESSAGE()
- Raiserror 50001 @Error
- Return 1
- End Catch
- Set Nocount Off
-
-
- Go
存儲過程測試:
- use master
- Go
- Exec dbo.sp_RestoreDataBase 'E:\DBBackup\dbA2011-09-05.bak','E:\DATA\SQL2008DE01'
- go
更多請聯(lián)系:億恩科技
QQ:1613285598 0371-63322220
本文出自:億恩科技【mszdt.com】
服務器租用/服務器托管中國五強!虛擬主機域名注冊頂級提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|