SQLServer理解copyonly备份操作

Always在添加数据库的过程中如果同步首选项选择的是“完整”，那么就会在主副本上执行copyonly的完整备份和日志备份在辅助副本上执行还原操作，也正是这个操作让我对copyonly有了新的理解。接下来详细介绍copyonly的操作

一、备份测试

CREATE DATABASE city;

GO

CREATE TABLE city.dbo.test(id INT);

---执行完整备份

BACKUP DATABASE [city] TO  DISK = N'D:\backup\city_full_20170613.bak' WITH NOFORMAT, NOINIT,  NAME = N'city-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

--插入1条记录

INSERT INTO city.dbo.test VALUES(1);

--执行日志备份1

BACKUP LOG [city] TO  DISK = N'D:\backup\city_log1_20170613.trn' WITH NOFORMAT, NOINIT,  NAME = N'city-日志备份1 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

INSERT INTO city.dbo.test VALUES(2);

GO

--执行完整copy only备份

BACKUP DATABASE [city] TO  DISK = N'D:\backup\city_full_copyonly_20170613.bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'city-完整copyonly 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

INSERT INTO city.dbo.test VALUES(3);

--执行差异备份

BACKUP DATABASE [city] TO  DISK = N'D:\backup\city_diff_20170613.bak' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'city-差异 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

INSERT INTO city.dbo.test VALUES(4);

GO

--执行日志备份2

BACKUP LOG [city] TO  DISK = N'D:\backup\city_log2_20170613.trn' WITH NOFORMAT, NOINIT,  NAME = N'city-日志备份2 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

二、查询备份

SELECT

bs.database_name,

bs.name AS BackupName,

bs.first_lsn,--备份集中最早的一条日志记录的日志序列号

bs.last_lsn, --备份集下一条日志记录的日志序列号

bs.database_backup_lsn, --最近的数据库完整备份的日志序列号

bs.checkpoint_lsn,  --重做日志开始的日志序列号

bs.is_copy_only,

CASE bs.type WHEN 'D' THEN 'FullBack' WHEN 'L' THEN 'LogBack' WHEN 'I' THEN 'DiffBack' ELSE  bs.type END AS BackupType,

bs.backup_start_date,

bs.backup_finish_date,

bs.backup_size,

bs.recovery_model

FROM msdb.dbo.backupset bs

--INNER JOIN msdb.dbo.backupfile bf ON bs.backup_set_id=bf.backup_set_id

WHERE bs.database_name='city'

巴特西

SQLServer理解copyonly备份操作

最新文章

热门文章