动态还原完整和所有事务日志备份

• 下载脚本 - 2.04 KB

引言

本教程将展示如何动态恢复最新的完全备份和所有事务日志备份。

背景

当我尝试恢复一个数据库时，我需要恢复一个完全备份和几个日志备份。我需要手动查找数据库的物理位置、逻辑名称以及所有事务日志文件列表。因此，我需要一个能够完成相同操作的流程。

你需要准备

此脚本在 SQL Server 2008 中进行了测试。

创建初始步骤

----------------------------------------------------------------------------
-- Create Database
----------------------------------------------------------------------------
USE [master] 
GO 
CREATE DATABASE [DatabaseForLogBackups] ON PRIMARY  
(     NAME = N'DatabaseForLogBackups' 
   , FILENAME = N'D:\SQLData\DatabaseForLogBackups.mdf'  
   , SIZE = 512000KB  
   , FILEGROWTH = 51200KB ) LOG ON  
( NAME = N'DatabaseForLogBackups_log' 
     , FILENAME = N'D:\SQLData\DatabaseForLogBackups_log.ldf'  
   , SIZE = 51200KB  
   , FILEGROWTH = 51200KB ) 
GO 
ALTER DATABASE [DatabaseForLogBackups] SET RECOVERY FULL 
GO

----------------------------------------------------------------------------
-- Create Table
----------------------------------------------------------------------------
USE [DatabaseForLogBackups] 
GO 
 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[MessageTable1] 
    ( 
      [Message] [nvarchar](100) NOT NULL , 
      [DateTimeStamp] [datetime2] NOT NULL 
    ) 
ON  [PRIMARY] 
GO 
CREATE TABLE [dbo].[MessageTable2] 
    ( 
      [Message] [nvarchar](100) NOT NULL , 
      [DateTimeStamp] [datetime2] NOT NULL 
    ) 
ON  [PRIMARY] 
GO 
CREATE TABLE [dbo].[MessageTable3] 
    ( 
      [Message] [nvarchar](100) NOT NULL , 
      [DateTimeStamp] [datetime2] NOT NULL 
    ) 
ON  [PRIMARY] 
GO

----------------------------------------------------------------------------
-- Load Data in Table
----------------------------------------------------------------------------

USE [DatabaseForLogBackups] 
 
INSERT INTO dbo.MessageTable1  
   VALUES ('This is the initial data for MessageTable1', GETDATE()) 
GO 1000 
 
INSERT INTO dbo.MessageTable2  
   VALUES ('This is the initial data for MessageTable2', GETDATE()) 
GO 1000 
 
INSERT INTO dbo.MessageTable3  
   VALUES ('This is the initial data for MessageTable3', GETDATE()) 
GO 1000

创建完全备份

DECLARE @name sysname -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'D:\SQLData\'  
set @name='DatabaseForLogBackups';
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
SET @fileName = @path + @name + '_' + @fileDate + '.bak'  
BACKUP DATABASE @name 
TO DISK = @fileName  
WITH NAME = N'DatabaseForLogBackups-Full Database Backup', STATS = 10, INIT 
GO

添加更多数据

USE [DatabaseForLogBackups] 
GO 
INSERT INTO MessageTable1  
   VALUES ('Second set of data for MessageTable1', GETDATE()) 
GO 50 
INSERT INTO MessageTable2  
   VALUES ('Second set of data for MessageTable2', GETDATE()) 
GO 50 
INSERT INTO MessageTable3  
   VALUES ('Second set of data for MessageTable3', GETDATE()) 
GO 50

获取事务日志

DECLARE @name sysname -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'D:\SQLData\'  
set @name='DatabaseForLogBackups';
 
-- specify filename format
 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  
   + '_'  
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') 
 
SET @fileName = @path + @name + '_' + @fileDate + '.trn'  
BACKUP LOG @name 
TO DISK = @fileName  
WITH NAME = N'DatabaseForLogBackups-Log Database Backup', STATS = 10 
GO

添加更多数据

USE [DatabaseForLogBackups] 
GO 
INSERT INTO MessageTable1  
   VALUES ('Second set of data for MessageTable1', GETDATE()) 
GO 150 
INSERT INTO MessageTable2  
   VALUES ('Second set of data for MessageTable2', GETDATE()) 
GO 150 
INSERT INTO MessageTable3  
   VALUES ('Second set of data for MessageTable3', GETDATE()) 
GO 150

获取另一个事务日志

DECLARE @name sysname -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'D:\SQLData\'  
set @name='DatabaseForLogBackups';
 
-- specify filename format
 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  
   + '_'  
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') 
 
SET @fileName = @path + @name + '_' + @fileDate + '.trn'  
BACKUP LOG @name 
TO DISK = @fileName  
WITH NAME = N'DatabaseForLogBackups-Log Database Backup', STATS = 10 
GO

备份之后

创建用于恢复的新数据库

USE [master] 
GO 
CREATE DATABASE [DatabaseForRestore] ON PRIMARY  
(     NAME = N'DatabaseForRestore' 
   , FILENAME = N'D:\SQLData\DatabaseForRestore.mdf'  
   , SIZE = 512000KB  
   , FILEGROWTH = 51200KB ) LOG ON  
( NAME = N'DatabaseForRestore_log' 
     , FILENAME = N'D:\SQLData\DatabaseForRestore_log.ldf'  
   , SIZE = 51200KB  
   , FILEGROWTH = 51200KB ) 
GO 
ALTER DATABASE [DatabaseForRestore] SET RECOVERY FULL 
GO

主脚本

----------------------------------------------------------------------------
-- Variable
----------------------------------------------------------------------------
DECLARE      
    @BackupFile nvarchar(260),
    @Restore_DatabaseName sysname = NULL,
    @Backup_DatabaseName sysname = NULL,
    @Restore_DataFile nvarchar(260) = NULL,
    @Restore_LogFile nvarchar(260) = NULL,
    @Backup_DataFile nvarchar(260) = NULL,
    @Backup_LogFile nvarchar(260) = NULL,
    @tmp nvarchar(200),
    @physical_device_name varchar(1000),
    @backup_set_id_Full INT;
      
DECLARE @FileList TABLE
    (
    LogicalName nvarchar(128) NOT NULL,
    PhysicalName nvarchar(260) NOT NULL,
    Type char(1) NOT NULL,
    FileGroupName nvarchar(120) NULL,
    Size numeric(20, 0) NOT NULL,
    MaxSize numeric(20, 0) NOT NULL,
    FileID bigint NULL,
    CreateLSN numeric(25,0) NULL,
    DropLSN numeric(25,0) NULL,
    UniqueID uniqueidentifier NULL,
    ReadOnlyLSN numeric(25,0) NULL ,
    ReadWriteLSN numeric(25,0) NULL,
    BackupSizeInBytes bigint NULL,
    SourceBlockSize int NULL,
    FileGroupID int NULL,
    LogGroupGUID uniqueidentifier NULL,
    DifferentialBaseLSN numeric(25,0)NULL,
    DifferentialBaseGUID uniqueidentifier NULL,
    IsReadOnly bit NULL,
    IsPresent bit NULL,
    TDEThumbprint varbinary(32) NULL
 );
 
 DECLARE @TranBackupList TABLE
 (
    id int identity(1,1) NOT NULL,
    PhysicalName nvarchar(260) NOT NULL
 );      
    
SET @Backup_DatabaseName='DatabaseForLogBackups';
SET @Restore_DatabaseName='DatabaseForRestore';

----------------------------------------------------------------------------
--1. Get Physical File Name from Database Name
----------------------------------------------------------------------------

SELECT 
    @Restore_DataFile=f.filename
FROM master..sysaltfiles f
    INNER JOIN master..sysdatabases d
    ON f.dbid = d.dbid
WHERE d.name = @Restore_DatabaseName
AND f.fileid=1

SELECT 
    @Restore_LogFile=f.filename
FROM master..sysaltfiles f
    INNER JOIN master..sysdatabases d
    ON f.dbid = d.dbid
WHERE d.name = @Restore_DatabaseName
AND f.fileid=2


-- Set Database in Single User Mode
SET @tmp = N'ALTER DATABASE '+ @Restore_DatabaseName +' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
EXEC(@tmp);

-- Get the ID and path Name of the most recent full backup for the database
SELECT @physical_device_name=physical_device_name,
@backup_set_id_Full = b.backup_set_id
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id
WHERE b.backup_set_id=
(
    SELECT MAX(backup_set_id)
    FROM  msdb.dbo.backupset  
    WHERE database_name = @Backup_DatabaseName 
        AND type = 'D' 
)
 
-- get mdf and ldf info from backup file name
SET @tmp = N'RESTORE FILELISTONLY
      FROM DISK=N''' + @physical_device_name + ''' WITH FILE=1' ;
INSERT INTO @FileList
      EXEC(@tmp);
--mdf 
select @Backup_DataFile=LogicalName
from @FileList
where [Type]='D';
--ldf
select @Backup_LogFile=LogicalName
from @FileList
where [Type]='L';


----------------------------------------------------------------------------
--2. Restore Last Full Backup
----------------------------------------------------------------------------

RESTORE DATABASE @Restore_DatabaseName
FROM DISK = @physical_device_name 
WITH MOVE @Backup_DataFile TO @Restore_DataFile ,
MOVE @Backup_LogFile TO @Restore_LogFile,
REPLACE,
NORECOVERY, STATS = 10;

----------------------------------------------------------------------------
--3. Get All transaction Log backup list 
----------------------------------------------------------------------------

insert into @TranBackupList
SELECT  physical_device_name
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id
WHERE b.backup_set_id>(@backup_set_id_Full)
AND database_name = @Backup_DatabaseName 
AND type = 'L'
ORDER BY b.backup_set_id

----------------------------------------------------------------------------
--4. Restore transaction Log 
----------------------------------------------------------------------------

DECLARE @Flag INT
DECLARE @RowCount INT
select @RowCount=COUNT(*) from @TranBackupList;

SET @Flag = 1
WHILE (@Flag <=@RowCount )
BEGIN
    select @BackupFile=PhysicalName
    from @TranBackupList
    where id=@Flag;
    PRINT @BackupFile
    RESTORE LOG @Restore_DatabaseName
    FROM DISK = @BackupFile
    WITH FILE = 1, NORECOVERY, STATS = 10 ;
    
    SET @Flag = @Flag + 1
END

-- Set Database in Normal mode
RESTORE DATABASE @Restore_DatabaseName
   WITH RECOVERY;

-- Set Database in Multi User Mode
SET @tmp = N'ALTER DATABASE '+ @Restore_DatabaseName +' SET MULTI_USER;';
EXEC(@tmp);

执行脚本后

分析脚本

SELECT 
    @Restore_DataFile=f.filename
FROM master..sysaltfiles f
    INNER JOIN master..sysdatabases d
    ON f.dbid = d.dbid
WHERE d.name = @Restore_DatabaseName
AND f.fileid=1

SELECT 
    @Restore_LogFile=f.filename
FROM master..sysaltfiles f
    INNER JOIN master..sysdatabases d
    ON f.dbid = d.dbid
WHERE d.name = @Restore_DatabaseName
AND f.fileid=2

print @Restore_DataFile
print @Restore_LogFile

这将检索用于恢复数据库的物理文件名，输出将是

D:\SQLData\DatabaseForRestore.mdf
D:\SQLData\DatabaseForRestore_log.ldf

分析脚本

-- Get the ID and path Name of the most recent full backup for the database
SELECT @physical_device_name=physical_device_name,
@backup_set_id_Full = b.backup_set_id
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id
WHERE b.backup_set_id=
(
    SELECT MAX(backup_set_id)
    FROM  msdb.dbo.backupset  
    WHERE database_name = @Backup_DatabaseName 
        AND type = 'D' 
)

-- get mdf and ldf info from backup file name
SET @tmp = N'RESTORE FILELISTONLY
      FROM DISK=N''' + @physical_device_name + ''' WITH FILE=1' ;
INSERT INTO @FileList
      EXEC(@tmp);
--mdf 
select @Backup_DataFile=LogicalName
from @FileList
where [Type]='D';
--ldf
select @Backup_LogFile=LogicalName
from @FileList
where [Type]='L';

print @Backup_DataFile
print @Backup_LogFile

这将检索最新的完全备份的物理文件名，并从备份文件名中获取 mdf 和 ldf 信息。

DatabaseForLogBackups
DatabaseForLogBackups_log

分析脚本

RESTORE DATABASE @Restore_DatabaseName
FROM DISK = @physical_device_name 
WITH MOVE @Backup_DataFile TO @Restore_DataFile ,
MOVE @Backup_LogFile TO @Restore_LogFile,
REPLACE,
NORECOVERY, STATS = 10;

这将恢复最新的完全备份，并将数据库置于NORECOVERY模式，以便可以恢复日志文件。

分析脚本

insert into @TranBackupList
SELECT  physical_device_name
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id
WHERE b.backup_set_id>(@backup_set_id_Full)
AND database_name = @Backup_DatabaseName 
AND type = 'L'
ORDER BY b.backup_set_id

这将填充在完全备份之后的所有事务日志备份列表。

分析脚本

DECLARE @Flag INT
DECLARE @RowCount INT
select @RowCount=COUNT(*) from @TranBackupList;

SET @Flag = 1
WHILE (@Flag <=@RowCount )
BEGIN
    select @BackupFile=PhysicalName
    from @TranBackupList
    where id=@Flag;
    PRINT @BackupFile
    RESTORE LOG @Restore_DatabaseName
    FROM DISK = @BackupFile
    WITH FILE = 1, NORECOVERY, STATS = 10 ;
    
    SET @Flag = @Flag + 1
END

-- Set Database in Normal mode
RESTORE DATABASE @Restore_DatabaseName
   WITH RECOVERY;

这将恢复所有事务日志备份，并将数据库置于正常运行状态。

结论

目前还没有。

参考文献

• SQL Server 备份和恢复 由 Shawn McGehee 撰写

历史

目前还没有。