Sql Server 自動還原解決方案

根據前一篇關於自動備份的文章，對應的自動還原方案

 

create procedure [dbo].[sp_RestoreDatabase]

@SourceDirBackupFiles NVARCHAR(200)

as

declare @DatabaseName sysname

--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from

CREATE TABLE #bdev(

 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 INT

,IsSnapshot INT

,IsReadOnly INT

,IsSingleUser INT

,HasBackupChecksums INT

,IsDamaged INT

,BegibsLogChain INT

,HasIncompleteMetaData INT

,IsForceOffline INT

,IsCopyOnly INT

,FirstRecoveryForkID uniqueidentifier

,ForkPointLSN numeric(25,0)

,RecoveryModel NVARCHAR(128)

,DifferentialBaseLSN numeric(25,0)

,DifferentialBaseGUID uniqueidentifier

,BackupTypeDescription NVARCHAR(128)

,BackupSetGUID uniqueidentifier

)

TRUNCATE TABLE #bdev

INSERT #bdev

EXEC('RESTORE HEADERONLY FROM DISK = ''' + @SourceDirBackupFiles + '''')

--select * from #bdev

 

declare @position int  

declare @BackupType int

declare @last_log_position int

 

select @last_log_position=max(position)

from #bdev

--where backupType=2

 

declare backup_file_cursor cursor for

select position,BackupType,DatabaseName

from #bdev

 

open backup_file_cursor;

fetch next from backup_file_cursor into @position,@BackupType,@DatabaseName

 

while @@fetch_status = 0

       begin

        if @BackupType=1--full restore

            if @last_log_position=@position

                RESTORE DATABASE @DatabaseName FROM  DISK = @SourceDirBackupFiles WITH  FILE = @position,  RECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10

            else

                RESTORE DATABASE @DatabaseName FROM  DISK = @SourceDirBackupFiles WITH  FILE = @position,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10

        else if @BackupType=5--diff restore

            if @last_log_position=@position

                RESTORE DATABASE @DatabaseName FROM  DISK = @SourceDirBackupFiles WITH  FILE = @position,  RECOVERY,  NOUNLOAD,  STATS = 10

            else

                RESTORE DATABASE @DatabaseName FROM  DISK = @SourceDirBackupFiles WITH  FILE = @position,  NORECOVERY,  NOUNLOAD,  STATS = 10

        else--log restore

            if @last_log_position=@position

               RESTORE LOG @DatabaseName FROM  DISK = @SourceDirBackupFiles WITH  FILE = @position,  NOUNLOAD,  STATS = 10

            else

               RESTORE LOG @DatabaseName FROM  DISK =@SourceDirBackupFiles WITH  FILE = @position,  NORECOVERY,  NOUNLOAD,  STATS = 10

 

        fetch next from backup_file_cursor into @position,@BackupType,@DatabaseName

end

 

close backup_file_cursor

deallocate backup_file_cursor

 

drop table #bdev