从多个 .bak 文件还原数据库 SQL Server 2012 [英] Restore a database from multiple .bak files SQL Server 2012

问题描述

第三方正在向我们发送拆分备份，我们每天都需要将这些备份恢复到单个数据库中.有没有一种方法可以使这个自动化，这是低维护的?

Third party is sending us split backups that we need to restore everyday into single database. Is there a way to automate this which is low maintenance?

首选是编写 T-SQL 或 SQL Server 作业:

Preference is to do this writing either T-SQL or a SQL Server job:

• AdventureWorks07182018.back0
• AdventureWorks07182018.back1
• AdventureWorks07182018.back2
• AdventureWorks07182018.back3

推荐答案

评论太长，但这通常在 SQL 代理作业中完成.

Too long to comment, but this is usually done in an SQL Agent Job.

拆分备份意味着(至少)4 件事中的 1 件事，如果您还不知道，您需要从供应商那里得到澄清.我排除了部分备份:

Split backups means 1 of (at least) 4 things which you'll need to get clarification from the vendor, if you don't already know. I excluded partial backups:

1. 供应商将在一天内发送上面列出的所有备份，这意味着它是一个基础备份和 3 个差异或日志备份(可能)
2. 供应商仅发送差异备份，因为他们最初向您发送了基本数据(不太可能)
3. 供应商在不同日期备份文件组或文件(可能是 VLDB)并在发生时发送给您(不太可能发生的情况)
4. 供应商每天都会向您发送完整备份，其中您收到的最后一个是最新的(最有可能的)

排除文件和文件组还原，您将:

Excluding file and filegroup restores, you will:

1. 恢复完整备份
2. 恢复上次差异备份(如果适用)
3. 恢复在上次差异备份之后发生的任何日志备份，或者如果差异没有发生，那么自上次完整备份以来发生的任何日志备份

1. Restore the full backup
2. Restore the last differential backup, if applicable
3. Restore any log backups that happened after the last differential backup, or since the last full backup if differentials aren't happening

您还可以在第 2 步中选择任何差异，然后选择该差异之后的所有日志备份(如果存在).当然，如果他们只向您发送完整备份，那么您只需恢复完整备份即可.

You could also choose any differential in step 2, and then all log backups since that differential if it exists. Naturally, if they are only sending you full backups, then you simply need to restore the full backup only.

这些东西...这将根据您的环境而有所不同(AlwaysOn、设置为只读、设置为 STANDBY 等...)

Something along these lines... which will vary based on your environment (AlwaysOn, setting to read only, setting to STANDBY, etc...)

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [AdventureWorks] FROM  DISK ='\\UNC\AdventureWorks.back0'
WITH
--what ever options... but likely a file move
MOVE 'data_file_1' TO 'E:\somefolder\data.mdf',
MOVE 'db_log' TO 'E:\somefolder\log.ldf',
REPLACE, --overwrites the database
RECOVERY --sets the DB to READ/WRITE. Use NORECOVERY if you need to restore logs / differentials
GO

--if using logs...
RESTORE LOG AdventureWorks
   FROM '\\UNC\AdventureWorks.back01' --assuming this is a log
   WITH FILE = 1,  --this is the first log
   WITH NORECOVERY;  --keep in norecovery to restore other logs...
GO  

等等...

这篇关于从多个 .bak 文件还原数据库 SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！