備份和還原 SQL Server 文件和文件組

我有一個名為 Admin 的數據庫。它只有一個名為 primary 的預設文件組。

File group: primary
files in the file group: Admin_data.mdf

現在我已經進行了完整的文件組備份。

Backup file name: fg_full.bak

然後在名為 sec_file.ndf 的同一文件組中創建了一個新的 ndf並創建了幾個表，在每個表上插入了 1000 行。

一小時後對我的 MDF 和 NDF 文件進行了文件備份

MDF backup: Admin_Data.bak
NDF backup: sec_file_ndf.bak

使用 SSMS 進行文件備份

在這裡不知何故我的sec_file 被刪除了，所以我想恢復最新的 NDF 備份，我該如何恢復它？

restore headeronly 的輸出

Restore filelistonly from disk = 'D:\sec_file_ndf.bak'

查詢備份文件

RESTORE HEADERONLY FROM DISK = 'D:\sec_file_ndf.bak'

和

RESTORE FILELISTONLY FROM DISK= 'D:\sec_file_ndf.bak' 

…驗證D:\sec_file_ndf.bak文件的內容。

您可能在一個 *.BAK 文件中有多個備份。如果是這樣，請將選項添加FILE = 2到您的 RESTORE 命令以恢復您最近的*.NDF文件備份。

假設您的數據庫處於FULL恢復模式（正如您在評論中確認的那樣），可以只恢復第二個文件。

順序是這樣的：

• 將日誌管理員備份到磁碟 = ‘…\tailofthelog.trn’ with norecovery;
• 從磁碟恢復數據庫管理文件 = ‘sec_file’ = ‘…’ with norecovery;
• 從磁碟恢復數據庫管理員 = ‘…\tailofthelog.trn’ 並恢復；

更新

這是我的複制程式碼：

if db_id('test') is not null drop database test;
go

CREATE DATABASE test ON  PRIMARY 
( NAME = N'prime1', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf' , 
 SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
LOG ON 
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , 
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%);
go

alter database test set recovery FULL;
go

backup database test filegroup = 'PRIMARY' 
to disk = 'Z:\backups\default\test_primaryFG1.bak';
go

alter database test add file
( NAME = N'prime2', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_prime2.ndf' , 
 SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB );
go 

-- second connection:
use test;
go  
create table dbo.t(id int);
go

-- first connection:  
backup database test file = 'prime1' 
to disk = 'Z:\backups\default\test_prime1.bak';
go

-- second connection:
create table dbo.t1(id int);
insert into dbo.t1(id) values(1);
go


-- first connection:
backup database test file = 'prime2' 
to disk = 'Z:\backups\default\test_prime2.bak'

-----------------------------------
backup log test 
to disk = 'Z:\backups\default\test_tail.trn' with norecovery;

restore database test file = 'prime2'
from disk = 'Z:\backups\default\test_prime2.bak'
with norecovery;

restore database test
from disk = 'Z:\backups\default\test_tail.trn' with recovery;

-----------------------------------
-- just another restore starting with FD restore as OP did:
restore database test 
from disk = 'Z:\backups\default\test_primaryFG1.bak' with replace, norecovery;

restore database test file = 'prime2' 
from disk = 'Z:\backups\default\test_prime2.bak' with norecovery


restore database test
from disk = 'Z:\backups\default\test_tail.trn' with recovery;

引用自：https://dba.stackexchange.com/questions/184151