日志传送SQL Server 2012

Question

我是一家没有DBA的小商店的开发人员，我正在努力让日志随sql server 2012一起运行。我正在尝试将报告从事务系统卸载到一个新的数据仓库，并将使用此db作为暂存区域。

我运行了日志传送向导，每次主备份和文件复制作业都能工作。次级还原作业似乎随机失败。

主服务器只有一个事务日志作业。差异备份是禁用的(不确定这是否重要)，但是有完整的备份。

辅助服务器是一个新安装，没有维护计划、备份或活动用户。

是否有办法强制备份同步，还是始终确保备份保持同步？

它看起来太脆弱了。请给我建议。

经编辑的日志如下：

*Starting transaction log copy. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
Retrieving copy settings. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
Retrieved copy settings. 
Primary Server: '', 
Primary Database: 'db', Backup Source Directory: '\\server\folder', 
Backup Destination Directory: '\\server\folder', 
Last Copied File: '\\server\folder\db_20160105070002.trn'
Starting transaction log restore. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
Retrieving restore settings. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
Copying log backup files. 
Primary Server: 'server', Primary Database: 'db', 
Backup Source Directory: '\\server\folder', 
Backup Destination Directory: '\\server\folder'
Retrieved common restore settings. 
Primary Server: 'server', 
Primary Database: 'db', 
Backup Destination Directory: '\\server\folder', 
File Retention Period: 14400 minute(s)
Retrieved database restore settings. 
Secondary Database: 'db', 
Restore Delay: 10, 
Restore All: True, 
Restore Mode: Standby, 
Disconnect Users: True, 
Last Restored File: \\server\folder\db_20160105060002.trn, 
Block Size: Not Specified, 
Buffer Count: Not Specified, 
Max Transfer Size: Not Specified
Disconnecting users. 
Secondary DB: 'db'
Copying log backup file to temporary work file.
 Source: '\\server\folder\db_20160105080001.trn', 
Destination: '\\server\folder\db_20160105080001.wrk'
Renamed temporary work file. 
Source: '\\server\folder\db_20160105080001.wrk',
Destination: '\\server\folder\db_20160105080001.trn'
Checking to see if any previously copied log backup files that are required by the restore operation are missing. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'
The copy operation was successful. 
Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7', 
Number of log backup files copied: 1
An error occurred restoring the database access mode. (Alter failed for Database 'db'. )
The file '\\server\folder\db_20160105070002.trn' is too recent to apply to the secondary database 'db'. 
(The log in this backup set begins at LSN 52498000002221000001, which is too recent to apply to the database. An earlier log backup that includes LSN 52498000002197900001 can be restored.
RESTORE LOG is terminating abnormally.)
Searching for an older log backup file. 
Secondary Database: 'db'
Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105060002.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105050001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105040001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105030001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105020000.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105010001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160105000001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104230001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104220001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104210001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104200001.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104190004.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104180000.trn'

Skipped log backup file. Secondary DB: 'EntRIS', File: '\\server\folder\db_20160104170002.trn'

Could not find a log backup file that could be applied to secondary database 'db'.
Deleting old log backup files. Primary Database: 'db'

The restore operation completed with errors. Secondary ID: 'b58d7ce8-2fd7-4cec-b5bd-f3c5e5d3c0f7'*

UPDATE:在query的下面运行一些奇怪的事务日志备份(可能)

核就是桌子上的东西。不知道为什么它不是空的

这是备份完成时间，设备，类型

2016-01-08 02:00:01.000 D:\Folder\DB_20160108090001.trn日志

2016-01-08 :00:01.000 D:\Folder\DB_20160108080001.trn日志

2016-01-08 00:00:00.000 D:\Folder\DB_20160108070000.trn日志

2016-01-07 23:46:41.000 NUL原木

2016-01-07 23:41:07.000 {51C661F9-2DC2-4424-913F-B9CFADA69FEE}1数据库

2016-01-07 23:00:01.000 D:\Folder\DB_20160108060001.trn日志

Answer 1

它看起来太脆弱了。

自从sql server 2000 (甚至更旧的)天以来，日志传送就得到了测试和证明。它并不脆弱。

看看这些错误..。

最后还原的文件：\server\folder\db_20160105060002.trn

日志传送正在尝试恢复

目的地：“\server\folder\db_20160105080001.trn”

这意味着您在测井序列测井序列中有一个空白。可能会发生临时日志备份，这会破坏日志链。

请参考我的答案- 原木运输如何跟踪。

您甚至可以使用限制用户仅复制日志备份，这样特殊的日志备份就不会破坏日志链。另外，

@Sp rri提供了一个有效的点来禁用SQL编写器服务，这样第三方备份工具就不能与SQL交互。从第三方软件有时很疯狂开始，发现它是件很痛苦的事！

要查找日志备份中的空白，可以使用以下查询

SELECT 
    s.database_name,s.backup_finish_date,y.physical_device_name
FROM 
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE 
    (s.database_name = 'databaseNamePrimaryServer')
ORDER BY 
    s.backup_finish_date DESC;

另一个有用的查询是：

-- http://sqlblog.com/blogs/tibor_karaszi/archive/2014/11/03/can-you-restore-from-your-backups-are-you-sure.aspx
-- modified by Kin to include backup start and finish dates
SELECT TOP(100)
database_name
,CASE bs.TYPE
   WHEN 'D' THEN 'Full'
   WHEN 'I' THEN 'Differential'
   WHEN 'L' THEN 'Log'
   WHEN 'F' THEN 'File or filegroup'
   WHEN 'G' THEN 'Differential file '
   WHEN 'P' THEN 'Partial'
   WHEN 'Q' THEN 'Differential partial'
END AS backup_type
,bs.is_copy_only
,bs.is_snapshot
,bs.backup_start_date
,bs.backup_finish_date
,DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_time_sec
,mf.physical_device_name
,bs.database_name
FROM msdb.dbo.backupset AS bs
  INNER JOIN msdb.dbo.backupmediafamily AS mf ON bs.media_set_id = mf.media_set_id  
  where database_name = 'master' -- change here for your database 
ORDER BY backup_finish_date DESC;