~楓花雪岳~: [SQL] 還原參數 Move

星期三, 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'

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

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'

利用上述備份還原已事先建立好的 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

最後利用 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]);

~楓花雪岳~

星期三, 10月 07, 2015

[SQL] 還原參數 Move

沒有留言:

張貼留言