如何还原某一个目录下的所有数据库备份文件-数据库-火龙果软件工程

捐助

如何还原某一个目录下的所有数据库备份文件

火龙果软件发布于 2014-06-30

引述

在工作中，经常为测试或开发部门搭建数据库环境，每一次搭建环境，需要还原某一个目录下的所有数据库备份文件，也就是，一次需要还原一百多个数据库。

每碰到类似的情况，需要编写一个SQL脚本来实现还原数据库的功能。这里就简单介绍如何编写存储过程来实现，还原某一个目录下的所有数据库备份文件。

要实现还原某一个目录下的所有数据库备份文件，首先要实现还原某一个目录下的某一个备份文件。真实世界中，只提供数据库备份路径，和备份数据库文件名，及还原到的本地路径。接下来，将讲述以下内容：

1.根据备份文件找出数据库名

2.根据备份文件找出数据、日志目录列表

3.构造还原数据库SQL语句

4.还原单个数据库的存储过程代码

5.还原多个数据库的存储过程代码(一个目录下)

根据备份文件找出数据库名

在还原数据库的开始，我们先要知道把备份数据库文件还原到哪一个数据库上。在真实世界中，我们无法保证备份数据库文件都为”数据库名+.bak”格式 (如：myDB.bak,其中myDB就是数据库名)。通常情况，只知道备份文件，需要知道数据库名，我们可以通过SQL Server提供的T-SQL语句“Restore HeaderOnly”来找出原来的数据库名，简单语法这样：Restore HeaderOnly From

返回的结果集中，有一列”DatabaseName”描述备份的数据名。通过”DatabaseName”列，就可以找出我们需要的原来数据库名了。

e.g.

Restore HeaderOnly From Disk = 'E:\DBBackup\dbA2011-09-05.bak'

【注：】在结果集中， SQL Server 2008/R2 比 SQL Server 2005 多一个列”CompressedBackupSize”。

根据备份文件找出数据、日志列表

接下来，我们需要知道备份文件中包含哪些数据、日志文件，及要知道原来的文件路径是什么。要是原来的数据库文件存储路径与新路径不一致,我们需要在”Restore Database …” 里面使用” Move”选项。这里我们可以通过使用SQL Server 提供的T-SQL语句”Restore FileListOnly”，返回数据库和日志文件列表组成的结果集，简单语法这样：Restore FileListOnly From <backup_device>

e.g.

Restore FileListOnly From Disk = 'E:\DBBackup\dbA2011-09-05.bak'

【注：】在结果集中， SQL Server 2008/R2 比 SQL Server 2005 多一个列”TDEThumbprint”，应用于显示数据库加密密钥的指纹。

构造还原数据库SQL语句

上面两点内容，我们根据备份文件，知道了要还原的数据库名和数据库文件列表，那么，我们基本可以构造出还原数据库的SQL语句了。

e.g.

Restore DataBase dbA  
    From Disk='E:\DBBackup\dbA2011-09-05.bak'  
    With File=1, 
        Move 'dbA' To 'E:\DATA\SQL2008DE01\dbA.mdf', 
        Move 'dbA_log' To 'E:\DATA\SQL2008DE01\dbA_Log.LDF',Replace,Recovery

还原单个数据库的存储过程代码

根据上面分析的方法，这里演示我写的一个还原单个数据库的存储过程代码，因为代码是之前写的，中间因真实世界的特殊情况，修改成几个版本的存储过程。当然，如有可能，你可以根据自己所在的真实环境，修改对应部分的代码，以便满足自己的需要。

存储过程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

还原多个数据库的存储过程代码(一个目录下)

当一个目录下，存放这很多个备份文件的时候，我们需要还原整个目录，或者部分数据库备份文件，这时我们另外一个存储过程能实现这样的操作。这里我写一个存储过程sp_RestoreDataBase2：

if object_id('sp_restoredatabase2') Is Not Null 
    Drop proc sp_restoredatabase2 
Go 
create proc sp_restoredatabase2 
( 
    @Path_bak nvarchar(1024), 
    @Path_new nvarchar(1024)=null, 
    @DataBaseList nvarchar(max)=null 
) 
As 
/* 
@DataBaseList 数据库列表,可留空，或数据库之间使用回车、空格、逗号分隔都ok 
 
*/ 
Set Nocount On 
Declare @subdirectory nvarchar(1024),@ErrorMsg nvarchar(1024),@flag smallint 
 
if isnull(@Path_new,'')=''         
    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Mic
rosoft\MSSQLServer\MSSQLServer', N'DefaultData', @Path_new Output
If Not Exists(Select 1 From master.sys.procedures Where name='sp_RestoreDataBase') 
Begin 
    Raiserror 50001 N'找不到存储过程 sp_RestoreDataBase ' 
    Goto ExitFLag 
End 
Declare @Dir Table(subdirectory nvarchar(1024),depth int,[file] int) 
Declare @DBList table (DatabaseName nvarchar(128)) 
Declare @DBListNull table (DatabaseName nvarchar(128)) 
 
/* 
--SQL Server 2005 
Declare @BakHeaderTMP Table(BackupName nvarchar(128),BackupDescription nvarchar(255),BackupType smallint,
ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName nvarchar(128),
erverName 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 @BakHeaderTMP 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))
While charindex(char(13)+Char(10),@DataBaseList)>0 
    Set @DataBaseList=Replace(@DataBaseList,char(13)+Char(10),',') 
While charindex(char(13),@DataBaseList)>0 
    Set @DataBaseList=Replace(@DataBaseList,char(13),',') 
While charindex(char(9),@DataBaseList)>0 
    Set @DataBaseList=Replace(@DataBaseList,char(9),',') 
While charindex(char(32),@DataBaseList)>0 
    Set @DataBaseList=Replace(@DataBaseList,Char(32),',') 
Set @DataBaseList='Select '''+Replace(@DataBaseList,',',''' Union All Select ''')+'''' 
Insert Into @DBList  Exec(@DataBaseList) 
Delete @DBList Where DatabaseName ='' 
Insert Into @Dir Exec xp_dirtree @Path_bak,1,1 
If Not Exists(Select subdirectory From @Dir Where Charindex('.bak',subdirectory)>0) 
Begin 
    Set @ErrorMsg= N'无效的数据库路径: '+ rtrim(@Path_bak) 
    Raiserror 50001 @ErrorMsg 
    Goto ExitFlag 
End 
Declare cur_x cursor For Select subdirectory From @Dir Where Charindex('.bak',subdirectory)>0 
Open cur_x 
Fetch Next From cur_x Into @subdirectory 
While @@Fetch_status=0 
Begin 
     
    Set @subdirectory=Case When Right(@Path_bak,1)='\' Then @Path_bak Else   @Path_bak+'\' End+@subdirectory 
     
    Delete From @BakHeaderTMP 
 
    Insert Into @BakHeaderTMP 
        Exec sp_executesql N'Restore HeaderOnly From Disk=
@DatabBaseBakPath',N'@DatabBaseBakPath nvarchar(260)',@subdirectory 
         
    IF @@ERROR <> 0             
        Break 
    Else if Exists(Select 1 From @BakHeaderTMP As a Where Exists
(Select 1 From @DBList Where DatabaseName=a.DatabaseName) Or Not Exists(Select 1 From @DBList))         
        Begin 
            Exec @flag=sp_RestoreDataBase @subdirectory,@Path_new             
            If @flag <>0 Break 
        End 
 
    Insert Into @DBListNull(DatabaseName) 
        Select DatabaseName From @BakHeaderTMP     
                         
    Fetch Next From cur_x Into @subdirectory 
End 
CLose cur_x 
Deallocate cur_x 
If Exists(Select 1 From @DBList a Where Not Exists
(Select 1 From @DBListNull Where DatabaseName=a.DatabaseName)) 
    Select DatabaseName As [无效的数据库] 
From @DBList a Where Not Exists(Select 1 From @DBListNull Where DatabaseName=a.DatabaseName) 
 
print replicate('=',60) 
Print N'@Path_new ：  '+@Path_new 
 
ExitFLag: 
 
Go

存储过程测试：

use master 
Go 
Exec dbo.sp_RestoreDataBase2 'E:\DBBackup' 
go

小结

上面还原数据库的存储过程，它们给我们在工作中还原数据库的时候，带来许多便捷，如，不用我们一个个通过Microsoft SQL Server Management Studio(MSSMS)中的还原数据库向导去还原数据库，或也不用我们一个个执行”Restore Database”SQL语句去还原数据库。当然，在上面的代码中，我没有对每一个存储过程的每一个具体位置，进行解释。没有全部应用到”Restore Database”中”WITH”选项，我编写的主要目的是，存储过程参数尽可能的少，操作起来更方便，尽可能满足真实环境中的需要。如果你应用到以上的代码，可以根据自己所在的真实环境，进行修改补充。

次浏览