none
完整差異備份的還原問題 RRS feed

  • 一般討論

  • 您好,請問我用以下步驟測試
    完整差異備份的還原
    --建立測試資料庫,一個資料檔案與交易記錄檔
    CREATE DATABASE [SalesDB] ON  
    PRIMARY ( NAME = N'SalesDB', FILENAME = N'C:\gp\SalesDB.mdf' )
    LOG ON  ( NAME = N'SalesDB_log', FILENAME = N'C:\gp\SalesDB_log.LDF' )
    GO
    --設定復原模式為完整
    ALTER DATABASE SalesDB SET RECOVERY FULL
    --進行完整檔案備份當成基底備份
    BACKUP DATABASE SalesDB  
    TO disk='C:\Backup\SalesDB.bak'
    
    
    ---產生第一份資料表
    CREATE TABLE SalesDB.dbo.trxT1(c1 varchar(20))
    INSERT INTO SalesDB.dbo.trxT1 VALUES('TABLE1')
    --模擬星期一的完整差異備份
    BACKUP DATABASE SalesDB  
    TO disk='c:\Backup\SalesDBDIFF.bak'
    WITH DIFFERENTIAL
    
    ---產生第二份資料表
    CREATE TABLE SalesDB.dbo.trxT2(c1 varchar(20))
    INSERT INTO SalesDB.dbo.trxT2 VALUES('TABLE2')
    --模擬星期二的完整差異備份
    BACKUP DATABASE SalesDB  
    TO disk='c:\Backup\SalesDBDIFF.bak'
    WITH DIFFERENTIAL
    
    
    ---產生第一份資料表
    CREATE TABLE SalesDB.dbo.trxT3(c1 varchar(20))
    INSERT INTO SalesDB.dbo.trxT3 VALUES('TABLE3')
    --模擬星期一的完整差異備份
    BACKUP DATABASE SalesDB  
    TO disk='c:\Backup\SalesDBDIFF.bak'
    WITH DIFFERENTIAL
    模擬資料庫損毀,所以像要回復資料庫
    --備份結尾交易
    BACKUP LOG SalesDB to disk='c:\Backup\tail_log.bak' 
    with no_truncate
    
    
    --還原完整備份--NORECOVERY
    RESTORE DATABASE SalesDB
    FROM disk='c:\Backup\SalesDB.bak'
    WITH NORECOVERY
    
    
    --還原星期二的完整差異備份
    RESTORE DATABASE SalesDB
    FROM disk='c:\Backup\SalesDBDIFF.bak'
    WITH NORECOVERY,FILE=3 
    想請問
    Q1.FILE=3  是根據我由 RESTORE HEADERONLY FROM DISK='c:\Backup\SalesDBDIFF.bak'
    查出 position=3 而來的嗎?

    Q2.我測過 FILE=2 與 FILE=3 結果 似乎都可以達到還原作用?
        所以,這部份作用為何呢?
       謝謝!


    • 已變更類型 Alex ChuoModerator 2010年3月1日 上午 12:48 提問者未有後續回應
    2010年2月12日 上午 09:46

所有回覆

  • A1.
    是,亦可用 RESTORE FILELISTONLY ... 、RESTORE VERIFYONLY ...

    A2.
    您的 T-SQL 陳述式最後沒看到有執行下面的 2 道:
    RESTORE LOG ...  FROM ...  WITH NORECOVERY,FILE = 4
    RESTORE DATABASE ... WITH RECOVERY

    亦可將上述 2 道陳述式精簡成 1 道:
    RESTORE LOG ...  FROM ...  WITH RECOVERY,FILE = 4

    ☞ 這裡是「免費的討論區」付費支援服務請造訪 此處享受尊榮服務
    如果回覆對您有幫助,請記得按下標示為解答」
    在本討論區使用正體中文(即繁體中文),是對參與的朋友的一種尊重,因此請用本討論區的語言:正體中文。
    2010年2月12日 下午 03:48
    版主
  • Q1. yes, filelistonly doesn't give you backup position.
    Q2. you can get details of each backup with 'restore headeronly', both work if they are same kind of backup.
    2010年2月12日 下午 07:42
  • 您好:
    我最後是有

    RESTORE DATABASE SalesDB

    FROM DISK = 'c:\Backup\tail_log.bak'

    WITH RECOVERY

     

    沒有用 FILE=4,這樣沒差吧?

     

    另外,


    --還原星期二的完整差異備份
    
    
    
    RESTORE DATABASE SalesDB
    FROM disk ='c:\Backup\SalesDBDIFF.bak'
    WITH NORECOVERY,FILE =3
    這用FILE=2 或 FILE=3,似乎都沒有差別,這樣是否可以不要用FILE=?
    • 已編輯 softballnow 2010年2月13日 上午 06:10 修正
    2010年2月13日 上午 02:22
  • Can you post result of 'restore headeronly'?
    2010年2月13日 上午 02:57
  • 如下:
    <!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--> <!-- /* Font Definitions */ @font-face {font-family:新細明體; panose-1:2 2 5 0 0 0 0 0 0 0; mso-font-alt:PMingLiU; mso-font-charset:136; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611969 684719354 22 0 1048577 0;} @font-face {font-family:"\@新細明體"; panose-1:2 2 5 0 0 0 0 0 0 0; mso-font-charset:136; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611969 684719354 22 0 1048577 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:none; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:新細明體; mso-font-kerning:1.0pt;} /* Page Definitions */ @page {mso-page-border-surround-header:no; mso-page-border-surround-footer:no;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> <!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:表格內文; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

    RESTORE HEADERONLY FROM DISK = 'c:\Backup\SalesDBDIFF.bak'


    BackupName                                                                                                                       BackupDescription                                                                                                                                                                                                                                               BackupType ExpirationDate          Compressed Position DeviceType UserName                                                                                                                         ServerName                                                                                                                       DatabaseName                                                                                                                     DatabaseVersion DatabaseCreationDate    BackupSize           FirstLSN                                LastLSN                                 CheckpointLSN                           DatabaseBackupLSN                       BackupStartDate         BackupFinishDate        SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName                                                                                                                      Flags       BindingID                            RecoveryForkID                       Collation                                                                                                                        FamilyGUID                           HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID                  ForkPointLSN                            RecoveryModel                                                DifferentialBaseLSN                     DifferentialBaseGUID                 BackupTypeDescription                                                                                                            BackupSetGUID
    -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------------------- ---------- -------- ---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------- ----------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------- ----------------------- --------- -------- --------------- ---------------------- ------------------ ---------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------ ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ ----------------- ---------- ---------- ------------ ------------------ --------- -------------- --------------------- -------------- ---------- ------------------------------------ --------------------------------------- ------------------------------------------------------------ --------------------------------------- ------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------
    NULL                                                                                                                             NULL                                                                                                                                                                                                                                                            1          NULL                    0          1        2          sa                                                                                                                               SQL2005                                                                                                                          SalesDB                                                                                                                          611             2010-02-14 15:34:01.000 1520128              26000000010800170                       26000000017800001                       26000000010800170                       26000000005700037                       2010-02-14 15:34:23.000 2010-02-14 15:34:23.000 0         0        1028            196609                 90                 4608             9                    0                    1399                 SQL2005                                                                                                                          512         AB7EF624-AEF9-4A47-876D-9C3ABEF1CC07 96F8724A-DCE9-48F8-85FA-C9028E90D4C3 Chinese_Taiwan_Stroke_CI_AS                                                                                                      96F8724A-DCE9-48F8-85FA-C9028E90D4C3 0                 0          0          0            0                  0         0              0                     0              0          96F8724A-DCE9-48F8-85FA-C9028E90D4C3 NULL                                    FULL                                                         NULL                                    NULL                                 Database                                                                                                                         1427B7F3-A6F4-4525-9CF0-1015F1724731
    NULL                                                                                                                             NULL                                                                                                                                                                                                                                                            5          NULL                    0          2        2          sa                                                                                                                               SQL2005                                                                                                                          SalesDB                                                                                                                          611             2010-02-14 15:34:01.000 863232               26000000019600167                       26000000026600001                       26000000019600167                       26000000010800170                       2010-02-14 15:34:35.000 2010-02-14 15:34:35.000 0         0        1028            196609                 90                 4608             9                    0                    1399                 SQL2005                                                                                                                          2560        AB7EF624-AEF9-4A47-876D-9C3ABEF1CC07 96F8724A-DCE9-48F8-85FA-C9028E90D4C3 Chinese_Taiwan_Stroke_CI_AS                                                                                                      96F8724A-DCE9-48F8-85FA-C9028E90D4C3 0                 0          0          0            0                  0         0              0                     0              0          96F8724A-DCE9-48F8-85FA-C9028E90D4C3 NULL                                    FULL                                                         26000000010800170                       1427B7F3-A6F4-4525-9CF0-1015F1724731 Database Differential                                                                                                            2A6FE71F-B3C2-49F3-A05C-9E5F63A5E726
    
    再麻煩了
    2010年2月14日 上午 07:37
  • There are only 2 backups in the file, how can you restore with 'file = 3' option?
    2010年2月14日 下午 09:54
  • 不好意思,因為原始範意指有2個
    所以之後我又陸續增加地3個
    想試試看,結果是否一樣
    在回程以上回覆 我始有呈現2個資料!
    2010年2月15日 上午 02:34