如何还原某一个目录下的所有数据库备份文件(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]