sql server syslog外发 sqlserver log_backup

SQL Server事务日志备份

目录

• SQL Server事务日志备份

• 简介
• 使用T-SQL创建事务日志备份

• 示例

• 从事务日志备份还原数据库
• 总结

简介

当数据库的恢复模式为FULL（完整）或BULK_LOGGED（大容量日志）时，可以备份数据库的事务日志

在创建事务日志备份之前，需要创建至少一个完整备份。之后，可以创建任意数量的事务日志备份。

最好更频繁地将事务日志备份以：

• 最小化数据丢失
• 截断日志文件

通常，偶尔创建一次完整备份（如每周），并以较短的间隔（如每天）创建一系列差异备份。独立于数据库备份，您可以以更频繁的间隔（如每小时）创建事务日志。

下图说明了事务日志备份：

在这张图片中，我们有：

• 两个完整备份
• 三个事务日志备份

第一个完整备份包含id 1，第二个完整备份包括id 1、2和3。

第一个事务日志备份包含id 2，第二个事务日志副本包含id 3，第三个事务日志附件包含id 3。

注意，事务日志备份不像完整备份或差异备份那样包含重复数据。

使用T-SQL创建事务日志备份

语法：

BACKUP LOG database_name
TO DISK = path_to_backup_file
WITH options;

• 首先，指定要备份事务日志的数据库的名称。数据库必须存在并处于联机状态。
• 其次，指定备份日志文件的路径。路径必须存在于文件系统中。
• 第三，在WITH子句中指定其他备份选项。

示例

首先，删除HR数据库：

USE master;
DROP DATABASE IF EXISTS HR;

第二步，创建新的HR数据库：

CREATE DATABASE HR;
GO

第三步，确保HR数据库处于FULL恢复模式以执行事务日志备份:

ALTER DATABASE HR 
SET RECOVERY FULL;

第四步，创建人员表并插入一行：

USE HR;
GO

CREATE TABLE People (
  Id int IDENTITY PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL
);

INSERT INTO People (FirstName, LastName)
VALUES ('John', 'Doe');

第五步，创建完整备份：

BACKUP DATABASE HR 
TO  DISK = 'D:\backup\hr.bak'
WITH INIT,
NAME = 'HR-Full Database Backup';

第六步，在人员表中再插入一行：

INSERT INTO People(FirstName, LastName)
VALUES ('Jane', 'Doe');

第七步，创建第一个事务日志备份：

BACKUP LOG HR
TO  DISK = N'D:\backup\hr.bak' 
WITH NAME = N'HR-Transaction Log Backup';

第八步，在人员表中插入另一行：

INSERT INTO People(FirstName, LastName)
VALUES ('Upton', 'Luis');

第九步，创建第二个事务日志备份：

BACKUP LOG HR
TO  DISK = N'D:\backup\hr.bak' 
WITH NAME = N'HR-Transaction Log Backup';

第十步，创建第二个完整备份：

BACKUP DATABASE HR 
TO  DISK = 'D:\backup\hr.bak'
WITH NOINIT,
NAME = 'HR-Full Database Backup';

第十一步，在人员表中插入另一行：

INSERT INTO People(FirstName, LastName)
VALUES('Dach', 'Keon');

最后，查看备份文件：

RESTORE HEADERONLY   
FROM DISK = N'D:\backup\hr.bak';

从事务日志备份还原数据库

要恢复数据库，可以还原第二个完整备份（Position 4）和最后一个事务日志备份（Position 5）。

首先，删除人力资源数据库：

USE master;
DROP DATABASE HR;

然后，从第二次完整备份中恢复数据库：

RESTORE DATABASE HR
FROM  DISK = N'D:\backup\hr.bak'
WITH FILE = 4, NORECOVERY;

在该语句中，数字4指定备份文件中的第二个完整备份。NORECOVERY将数据库置于还原状态，以便可以还原事务日志备份。

第三步，使用RESTORE LOG语句恢复事务日志备份：

RESTORE LOG HR
FROM DISK = N'D:\backup\hr.bak'
WITH FILE = 5, RECOVERY

在该语句中，数字5表示最后一次事务日志备份。RECOVERY表示没有要恢复的进一步备份。因此，在恢复完成后，可以访问数据库。

最后，将当前数据库切换到HR，并从人员表中选择数据：

USE HR;
SELECT * FROM people;

People表有四行，表示已成功从完整备份和事务日志备份中恢复了数据库。

总结

若要备份事务日志，数据库的恢复模式必须是 FULL 或 BULK _ LOGGED。

事务日志备份包含数据库的事务日志。

使用 BACKUP LOG 语句备份事务日志。

使用 RESTORE LOG从事务日志备份中恢复数据库。