备份/还原SQL Server备份问题... [英] Backup/Restore SQL Server backup problem...
问题描述
大家好，
我在恢复数据库备份方面遇到了一些麻烦。
我有以下查询备份我的数据库：
Hi all,
I'm having a little trouble restoring a database backup.
I've got the following queries for backing up my database:
-- Full backup every night at 01:00.
DECLARE @FileName VARCHAR(256)
SET @FileName = 'C:\Backups\mydb_backup_' + CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 112) + '_full.bak'
BACKUP DATABASE [mydb]
TO DISK = @FileName
-- Differential every three hours daily starting at 00:00.
DECLARE @FileName VARCHAR(256)
SET @FileName = 'C:\Backups\mydb_backup_' + CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 112) + '_diff.bak'
BACKUP DATABASE [mydb]
WITH DIFFERENTIAL
TO DISK = @FileName
-- Every 5 minutes starting daily at 07:00.
DECLARE @FileName VARCHAR(256)
SET @FileName = 'C:\Backups\mydb_backup_' + CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 112) + '_tran.bak'
BACKUP LOG [mydb]
TO DISK = @FileName
这样每天给我三个文件，一个完整备份，一个差异备份和一个事务日志备份。
现在，当我尝试恢复（在不同的服务器上）时，我只需右键单击SSMS中的数据库，然后选择恢复数据库。我选择从设备并添加我的三个备份文件。
我收到以下错误消息：
So that gives me three files a day, a full backup, a differential backup and a transaction log backup.
Now when I try to restore (on a different server) I simply right click on 'Databases' in SSMS and then I choose 'Restore Database'. I choose 'From device' and add my three backup files.
I get the following error message though:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The media loaded on "C:\Backups\mydb_backup_20150715_tran.bak" is formatted to support 1 media families, but 3 media families are expected according to the backup device specification.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3231)我用Google搜索了这条消息，我发现有很多页面解决了这个问题...但由于某种原因，我无法找到导致此问题的原因，我如何解决它。
解决问题的一种方法是逐个恢复文件并使数据库处于恢复状态。这适用于完整备份和差异备份，但是当我到达事务日志时，我收到以下错误：
I've Googled this message and I've found lots of pages addressing this problem... But for some reason I can't find what's causing this problem and how I can solve it.
One way to get around the problem is by restoring the files one by one and leaving the database in a restore state. This works for the full and differential backups, but when I get to the transaction logs I get the following error:
Restore failed for Server 'myserver'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 101105000000049300001, which is too recent to apply to the database. An earlier log backup that includes LSN 101097000000212100001 can be restored. (Microsoft.SqlServer.Smo)我知道这意味着什么，但不知道我为什么会这样做。
所以我似乎错过了什么...
欢迎任何提示。
谢谢。
I know what it means, but not why I get it.
So I seem to be missing something...
Any tips are welcome.
Thanks.
推荐答案
这是基于（微弱的） ）内存但如果我没记错的话，当你定义多个设备时，就意味着备份分布在那些媒体上。基于此，您应该在恢复时只使用一个媒体，因为您将所有数据都放在一个文件中。
但是我相信离开数据库最简单恢复状态并按文件恢复它。
日志恢复问题看起来你刚刚从最近的日志备份开始。尝试上一个。
This is based on a (faint) memory but if I remember correctly, when you define multiple devices, it means that the backup is distributed on those media. Based on that you should use only one media on the restore since you have all the data in one file.
However I believe that it would be easiest to leave the database in restoring state and restore it file by file.
What comes to the log restore problem it looks like you have simply started from too recent log backup. Try previous one.
这篇关于备份/还原SQL Server备份问题...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
