Sqlserver關於備份報錯提示某個資料檔案不線上it is not online的解決方法

涉及語句
alter database db_name modify file (name='logicalname', offline)
backup database db_name to disk = N'XX'
RESTORE DATABASE db_name FILEGROUP='FILEGROUPNAME' WITH RECOVERY;



故障模擬語句
alter database test3_new modify file (name='test3_23', offline)

然後執行備份
backup database test3_new to disk = N'L:\full.bak'報錯
Msg 3007, Level 16, State 1, Line 3
The backup of the file or filegroup "test3_23" is not permitted because it is not online. Container state: "Offline" (7). Restore status: 0. BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

嘗試設定檔案online報錯
alter database test3_new modify file (name='test3_23', online)會報錯'online' is not a recognized CREATE/ALTER DATABASE option.



解決方法
1、如果test3_23對應的檔案還在磁碟上，則找到test3_23對應的FILEGROUP假如為FILEGROUP1，執行如下解決
RESTORE DATABASE db_name FILEGROUP='FILEGROUP1' WITH RECOVERY;
2、如果test3_23對應的檔案不在磁碟上，只能利用之前的備份進行恢復