如何還原某一個目錄下的所有數據庫備份文件(2)

●還原單個數據庫的存儲過程代碼

根據上面分析的方法，這里演示我寫的一個還原單個數據庫的存儲過程代碼，因為代碼是之前寫的，中間因真實世界的特殊情況，修改成幾個版本的存儲過程。當然，如有可能，你可以根據自己所在的真實環境，修改對應部分的代碼，以便滿足自己的需要。

存儲過程sp_RestoreDataBase代碼：

Use master 
Go 
if object_ID('[sp_restoredatabase]') is not null 
    Drop Procedure [sp_restoredatabase] 
Go 
/* 
    --還原數據庫(v3.2) Andy 2011-2-22 
    @DatabBaseBakPath nvarchar(260), --數據庫備份路徑(包含備份文件名) 
    @DatabBaseNewPath nvarchar(260)  --新數據庫路徑 
    @NewDataBaseName nvarchar(128)   --新數據庫名 
    e.g: 
    --Exec sp_RestoreDataBase 'E:\DatabaseBackup\DE\Support_2008722_14_33_39.bak','D:\SQL2005\DE' 
    V3.0版本修改說明： 
        1.修正了之前版本在還原包含全文索引文件的時候發生的錯誤。 
        2.修正了之前版本在還原包含多個數據庫文件和日志文件時發生的錯誤。 
    V3.1版本說明，增加了參數@Keep_Replication，表示是否保存復制設定 
    V3.2版本說明，增加了FILE ={ file_number | @file_number } 邏輯判斷，在包含多個備份組，還原最后一個備份組 
    V3.3 版本說明 ，還原發生錯誤時返回 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 
--    Set @Dir='Dir '+@DatabBaseBakPath 
--    Exec @i=xp_cmdshell  @Dir,no_output 
--    If @i<>0     
    Exec master.dbo.xp_fileexist @DatabBaseBakPath,@i Output 
    If @i=0 
    Begin 
        Raiserror 50001 N'無效的備份數據庫路徑/文件名!' 
        Return 1 
    End 
     
    If Charindex('\\',@DatabBaseNewPath)>0 
    Begin 
        Raiserror 50001 N'數據庫還原路徑中不能含有''\\''!' 
        Return 1 
    End   
    If Right(Rtrim(@DatabBaseNewPath),1)='\' 
    Begin 
        Raiserror 50001 N'數據庫還原路徑的最后一位能含有''\''!' 
        Return 1 
    End  
    Set @Dir='Dir '+@DatabBaseNewPath 
    Exec @i=xp_cmdshell  @Dir,no_output 
    If @i<>0 
    Begin 
        Raiserror 50001 N'無效的數據庫還原路徑!' 
        Return 1 
    End 
    set @DatabBaseNewPath=replace(@DatabBaseNewPath,'"','') 
    /* 
    --SQL Server 2005 
    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) 
    */ 
    --SQL Server 2008 
    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 
    /* 
    --SQL Server 2005 
    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) 
    */ 
    --SQL Server 2008 
    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 'File='+rtrim(max(Position))+', ' From @BakHeaderInfo) 
  
    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 '還原數據庫: '+@DataBaseName 
  
     
--    Print @sql1 
    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 

更多請聯系：億恩科技
QQ:1613285598   0371-63322220

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]