SQL Server复原某一个目录下之持有数据库备份文件

引述


在工作中,经常为测试或出机构搭建数据库环境,每一样软搭建环境,需要还原某一个索引下之拥有数据库备份文件,也就是是,一糟用恢复一百差不多独数据库。

每遇类似之状况,需要编制一个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 1

 

【注:】在结果集中,  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 2

 

【注:】在结果集中,  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

 

SQL Server 3

 

 

 

●还原多只数据库的积存过程代码(一个索引下)


当一个索引下,存放这大多单备份文件的时段,我们得还原整个目录,或者部分数据库备份文件,这时我们另外一个囤过程能够兑现如此的操作。这里自己形容一个囤积过程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

SQL Server 4

 

 

 

小结


点还原数据库的积存过程,它们被我们在工作中还原数据库的时节,带来许多轻便,如,不用我们一个个经Microsoft
SQL Server Management
Studio(MSSMS)中之过来数据库向导去还原数据库,或为无用我们一个个实践”Restore
Database”SQL语句去恢复数据库。当然,在上面的代码中,我莫对每一个仓储过程的诸一个具体位置,进行诠释。没有任何以到”Restore
Database”中”WITH”选项,我修的根本目的是,存储过程参数尽可能的不见,操作起来重新便利,尽可能满足实际环境受到的内需。如果你用及上述之代码,可以根据自己所于的真环境,进行改动补充。

相关文章