星期三, 10月 07, 2015

[SQL] 還原參數 Move

[SQL] 模擬資料庫毀損 時,常需要把 DB 還原,原本都是用 SSMS 來 Step By Step 進行還原,心血來潮時直接下語法還原就出現下面錯誤

還原 TSQL
USER MASTER
GO

RESTORE DATABASE AdventureWorks2014 
FROM DISK = N'D:\AdventureWorks2014.bak' 
WITH 
    File = 1 , 
    RECOVERY , 
    REPLACE ,
    STATS = 10
訊息 5133,層級 16,狀態 1,行 3
檔案 "C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorks2014_Data.mdf" 的目錄查閱失敗,有作業系統錯誤 3(系統找不到指定的路徑。)。

訊息 3156,層級 16,狀態 3,行 3
檔案 'AdventureWorks2014_Data' 無法還原到 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorks2014_Data.mdf'。請使用 WITH MOVE 來識別該檔案的有效位置。

訊息 5133,層級 16,狀態 1,行 3
檔案 "C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorks2014_Log.ldf" 的目錄查閱失敗,有作業系統錯誤 3(系統找不到指定的路徑。)。

訊息 3156,層級 16,狀態 3,行 3
檔案 'AdventureWorks2014_Log' 無法還原到 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014MULTI\MSSQL\DATA\AdventureWorks2014_Log.ldf'。請使用 WITH MOVE 來識別該檔案的有效位置。

訊息 3119,層級 16,狀態 1,行 3
計劃 RESTORE 陳述式的時候識別出問題。先前的訊息可提供詳細資料。
訊息 3013,層級 16,狀態 1,行 3
RESTORE DATABASE 正在異常結束。

在 MSDN 上找到這篇 將資料庫還原到新位置 來解決還原問題

利用 restore filelistonly 來查詢
RESTORE FILELISTONLY FROM DISK = N'D:\AdventureWorks2014.bak'

[SQL] 還原參數 Move-1

restore 搭配 move 參數
RESTORE DATABASE [AdventureWorks2014] 
FROM DISK = N'D:\AdventureWorks2014.bak'
WITH 
    File = 1 , 
    RECOVERY , 
    REPLACE ,
    MOVE 'AdventureWorks2014_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorks2014.mdf',
    MOVE 'AdventureWorks2014_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorks2014_Log.ldf',
    STATS = 10
[SQL] 還原參數 Move-2
  • MOVE 參數說明
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ]
要注意第一個參數 logical_file_name_in_backup 是輸入 restore filelistonly 的 LogicalName 欄位資訊,練習時一直輸入 restore filelistonly 的 PhysicalName 欄位資訊,怎麼 try 都沒有辦法進行還原,眼殘

MSDN 將資料庫還原到新位置庫和移動檔案 內也提到另一種情況要下 MOVE 參數:在同一個 instance 要還原成另一個不同名稱的 DB,而且 範例 D.還原資料庫和移動檔案中,還原時只需要針對第一個還原檔案指定 MOVE 參數,之後就不用再加上 MOVE 參數囉

把 AdventureWorks2014 備份出來練習該主題
-- 事先建立 AdventureWorks2014V2 DB,AdventureWorks2014 預設為 simple,轉換為 FULL 模式,才能進行 Log Bakcup
ALTER DATABASE [AdventureWorks2014V2] SET RECOVERY FULL
BACKUP DATABASE [AdventureWorks2014] TO DISK = N'D:\RestoreMoveDemo.bak'
BACKUP LOG [AdventureWorks2014] TO DISK = N'D:\RestoreMoveDemo.bak'
BACKUP LOG [AdventureWorks2014] TO DISK = N'D:\RestoreMoveDemo.bak'
-- restore headeronly 確認備份檔案內的 file
RESTORE HEADERONLY FROM DISK = N'D:\RestoreMoveDemo.bak'

[SQL] 還原參數 Move-3

利用上述備份還原已事先建立好的 AdventureWorks2014V2 DB
RESTORE DATABASE [AdventureWorks2014V2] 
FROM DISK = N'D:\RestoreMoveDemo.bak'
WITH 
    File = 1 , 
    NORECOVERY , 
    REPLACE ,
    -- 把 mdf、ldf 更名並還原到新位置
    MOVE 'AdventureWorks2014_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorks2014V2.mdf',
    MOVE 'AdventureWorks2014_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\AdventureWorks2014V2_Log.ldf',
    STATS = 10

-- 不需要 MOVE 參數
RESTORE DATABASE [AdventureWorks2014V2] 
FROM DISK = N'D:\RestoreMoveDemo.bak'
WITH 
    File = 2 ,
    NORECOVERY , 
    STATS = 10

-- 不需要 MOVE 參數
RESTORE DATABASE [AdventureWorks2014V2] 
FROM DISK = N'D:\RestoreMoveDemo.bak'
WITH 
    File = 3 ,
    RECOVERY , 
    STATS = 10

[SQL] 還原參數 Move-4

最後利用 sys.master_files 來查詢 AdventrueWorks2014V2
SELECT 
  DB_NAME([database_id]) AS [database_name], 
  [state_desc] AS  OnlineStatus,
  [file_id],
  [type_desc] AS [file_type], 
  [name] AS [logical_name], 
  [physical_name]
FROM sys.[master_files]
WHERE [database_id] IN (DB_ID('AdventureWorks2014V2'))
ORDER BY [type], DB_NAME([database_id]);
[SQL] 還原參數 Move-5

沒有留言:

張貼留言