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

lusklusklusk發表於2020-10-19

涉及語句
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對應的檔案不在磁碟上,只能利用之前的備份進行恢復


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30126024/viewspace-2727953/,如需轉載,請註明出處,否則將追究法律責任。

相關文章