locked
"RESTORE LOG" works even if filegroup doesn't exist? RRS feed

  • Question

  • Hello, everyone!

       I’m working on a Log Shipping scenario where a filegroup  called “MyDatabaseFileStream” only exists on the Primary database, not at the Secondary. The filegroup in question is huge, and I don’t need a copy on the Secondary. I just need a copy of the “PRIMARY” filegroup, so I use the “FILEGROUP” when I try to restore the database from a ".bak" file (command below)…

       RESTORE DATABASE MyDatabase

       FILEGROUP = 'PRIMARY'

       FROM DISK = N'D:\MyDatabase.bak'

       WITH

          STANDBY = N'D:\ROLLBACK_UNDO_MyDatabase.BAK'

    … and If the path of some file doesn't exist on the server, the restore fails:

    Msg 5133, Level 16, State 1, Line 1

    Directory lookup for the file "S:\ MyDatabase.MDF" failed with the operating system error 3(The system cannot find the path specified.).

    Msg 3156, Level 16, State 3, Line 1

    File ' MyDatabase_Data' cannot be restored to 'S:\ MyDatabase.MDF'. Use WITH MOVE to identify a valid location for the file. Msg 5133, Level 16, State 1, Line 1

    Directory lookup for the file "S:\ MyDatabase.LDF" failed with the operating system error 3(The system cannot find the path specified.).

    Msg 3156, Level 16, State 3, Line 1

    File ' MyDatabase_Log' cannot be restored to 'S:\ MyDatabase.LDF'. Use WITH MOVE to identify a valid location for the file. …

       Then, I use "WITH MOVE"...

     

       RESTORE DATABASE MyDatabase

       FILEGROUP = 'PRIMARY'

       FROM DISK = N'D:\MyDatabase.bak'

       WITH

          STANDBY = N'D:\ROLLBACK_UNDO_MyDatabase.BAK',

          MOVE N'MyDatabase_Data' TO N'D:\MyDatabase.mdf',

          MOVE N'MyDatabase_Log' TO N'D:\MyDatabase.ldf'

    ... and everything goes fine. The database is restored (database in "Standby/ReadOnly") on the Secondary, without the filegroup “MyDatabaseFileStream” (just the “PRIMARY”).

    Processed 536 pages for database 'MyDatabase', file 'MyDatabase_Data' on file 1.

    Processed 1 pages for database 'MyDatabase', file 'MyDatabase_Log' on file 1.

    RESTORE DATABASE ... FILE=<name> successfully processed 537 pages in 0.377 seconds (11.128 MB/sec).

       And finally, the question is: when a try to restore a log (from a ".trn" file) using the command below...

    RESTORE log MyDatabase

    FROM Disk='D:\MyDatabase_20150220184253.trn'

    WITH STANDBY = N'D:\ROLLBACK_UNDO_MyDatabase.BAK'

    ... and the restore succeeds. The command above does not have any filegroup specified, and the restore succeeds even when some transaction inserts, updates or deletes data from tables based on the “MyDatabaseFileStream” filegroup.

    Processed 0 pages for database ' MyDatabase', file 'MyDatabase_Data' on file 1.

    Processed 1 pages for database 'MyDatabase', file 'MyDatabase_Log' on file 1.

    RESTORE LOG successfully processed 1 pages in 0.212 seconds (0.020 MB/sec).

       OK, ok… Restoring a log will restore only the log file (nothing at the “PRIMARY”, neither at the “MyDatabaseFileStream”), but at the next log restore, the data inside the log file is moved to the data files, correct? The “MyDatabaseFileStream” related data (inside the transaction log) goes where? I was expecting an error at some point (like when restoring the database without the “WITH MOVE”).

       I'm not complaining. In fact, I'm glad it works. J  But, at the same time, I'm unsure... Can I rely on this behavior? Am I missing something?




    Monday, February 23, 2015 6:37 PM

Answers

  •    OK, ok? Restoring a log will restore only the log file (nothing at the ?PRIMARY?, neither at the ?MyDatabaseFileStream?), but at the next log restore, the data inside the log file is moved to the data files, correct? The ?MyDatabaseFileStream? related data (inside the transaction log) goes where? I was expecting an error at some point (like when restoring the database without the ?WITH MOVE?).

    The log records that apply to the PRIMARY filegroup are applied. Those for MyDatabaseFilestream are not applied anywhere.

    If you try to access data in MyDatabaseFilestream you will get an error. If you later restore the backup this filegroup, you will also have to reapply all transaction logs to get it sync with the primary filegroup.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Michelle Li Tuesday, February 24, 2015 1:50 PM
    • Marked as answer by Marcos Moran Thursday, February 26, 2015 5:43 PM
    Monday, February 23, 2015 10:54 PM
  • Yep, correct. Log records are only applied if they modify pages in existing files (filegroups).

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Marcos Moran Thursday, February 26, 2015 5:43 PM
    Thursday, February 26, 2015 5:05 PM

All replies

  • "but at the next log restore, the data inside the log file is moved to the data files, correct?"

    Above statement is confusing to me, and it seems to suggest something which is not how SQL Server work. Do you have a reference or elaboration for above statement?


    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, February 23, 2015 7:59 PM
  •    OK, ok? Restoring a log will restore only the log file (nothing at the ?PRIMARY?, neither at the ?MyDatabaseFileStream?), but at the next log restore, the data inside the log file is moved to the data files, correct? The ?MyDatabaseFileStream? related data (inside the transaction log) goes where? I was expecting an error at some point (like when restoring the database without the ?WITH MOVE?).

    The log records that apply to the PRIMARY filegroup are applied. Those for MyDatabaseFilestream are not applied anywhere.

    If you try to access data in MyDatabaseFilestream you will get an error. If you later restore the backup this filegroup, you will also have to reapply all transaction logs to get it sync with the primary filegroup.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Michelle Li Tuesday, February 24, 2015 1:50 PM
    • Marked as answer by Marcos Moran Thursday, February 26, 2015 5:43 PM
    Monday, February 23, 2015 10:54 PM
  • Thank you for your answer Erland.

       I really get an error (Msg 601, Level 12, State 3, Line 1 Could not continue scan with NOLOCK due to data movement.) when I try a select on the table that references the filegroup "MyDatabaseFileStream". And you are right about the backup, too: if I restore the "Primary" and "MyDatabaseFileStream" filegroups and apply the log backups, I get the database "full" synchronized. So, the "MyDatabaseFileStream" related data are actually inside the log backups, but as you said they are not applied anywhere if the filegroup is missing.

       Now I understanding better and feeling more confident about the solution for our scenario.


    • Edited by Marcos Moran Wednesday, February 25, 2015 5:19 PM
    Wednesday, February 25, 2015 3:41 PM
  • Hi Tibor, thank you for your reply.

       Indeed, I'm not sure about if it really works like that. I've found no references, but I will try to elaborate.

       So its how I think it works: when I do the first log restore, the transactions inside the log backup are written to the database log file, right? Then, when I do the second log restore, the transactions marked as committed (checkpointed) on the log file are applied to the data file, and replaced by the new ones (from the second backup).

       Again, its how I think it works. I'm not sure.

    Wednesday, February 25, 2015 5:47 PM
  • No, Marcus, not exactly. :-) Here is how it works:

    When you restore a log backup, first all log records in the backup are written to the ldf file.

    Then SQL Server does basically the same recovery process as it does when you start SQL Server. I.e., it read the ldf (log) forward (redo) and make sure that all operations are performed against the data (pages in mdf),

    And at the end it read the log backwards and undo all modifications that weren't committed when the backup was performed. This last step is not performed if you do the restore using the NORECOVERY option.

    I simplified it a bit regarding reading it forward and backwards etc, but I think you get the idea. 


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, February 25, 2015 6:08 PM
  •    Ah... So the whole process is done on the first log restore. Yeah, I get it. It's make more sense than a "two-step" process. Like you said "... basically the same recovery process as it does when you start SQL".

       And if some filegroup (like the "MyDatabaseFileStream", in my case) is missing at the database where I doing the restore? I mean, the filegroup is present on the database where the log backup is generated, but is missing where the log backup is restored. Restoring the log backup, the operations are performed against the data, and take place on the existing filegroup, and (without error) doesn't take place at the missing ones. Sorry if it sounds obvious, but is this how it works?

    Thursday, February 26, 2015 4:41 PM
  • Yep, correct. Log records are only applied if they modify pages in existing files (filegroups).

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Marcos Moran Thursday, February 26, 2015 5:43 PM
    Thursday, February 26, 2015 5:05 PM