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

引述

---

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

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

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

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

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

●构造还原数据库SQL语句

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

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

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

---

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

返回的结果集中，有一列”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\Microsoft\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),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 @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”选项，我编写的主要目的是，存储过程参数尽可能的少，操作起来更方便，尽可能满足真实环境中的需要。如果你应用到以上的代码，可以根据自己所在的真实环境，进行修改补充。