BACKUP / RESTORE

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    Comments posted to this topic are about the item BACKUP / RESTORE

    Jamie

  • kevriley

    SSCrazy Eights

    Points: 8907

    makes no difference to the answer, but the question states that the backup location is 'F:\......', but al the answers refer to a location at 'C:\......'

    probably just a typo

    Kev

  • ChiragNS

    One Orange Chip

    Points: 26137

    nice question

    "Keep Trying"

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714297

    Corrected the path typo.

  • Eric Mamet

    SSChampion

    Points: 11552

    Sorry to be a bit late but I don't see why the first one is incorrect.

    Could someone explain, please? :ermm:

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    This question was poorly written and since I first started writing them because I found poorly written questions could throw me off easily, I vowed to fix this. My resolve did not shine through on this one.

    The goal here was to make note of the compression option on SQL backup. http://msdn.microsoft.com/en-us/library/bb933863.aspx

    The fact is, there was a second more hidden goal here and this had to do with the LSN (Log Sequence Number). It is really simple to attach the database, but it gets more complicated if the database is not set to simple mode and there are trn files to consider for recovery purposes.

    Basically the first one doesn't work because the LSN is not taken into consideration. You will get an error if you attempt to restore it in the manner suggested:

    The tail of the log for the database "AdventureWorks" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

    To demonstrate this, backup adventureworks with compression:

    BACKUP DATABASE AdventureWorks

    TO DISK = 'C:\Backup\AdventureWorks.bak'

    WITH COMPRESSION, INIT, STATS = 10

    Run the first choice:

    RESTORE DATABASE AdventureWorks

    FROM DISK = N'C:\backup\AdventureWorks.bak'

    WITH MOVE N'AdventureWorks' TO N'C:\Data\AdventureWorks.mdf',

    MOVE N'AdventureWorks_Log' TO N'C:\LOG\AdventureWorks.ldf',

    RECOVERY, STATS = 10;

    Now use the second method (there is no mention of Recovery and thus no LSN required):

    RESTORE DATABASE [Adventureworks]

    FROM DISK = N'C:\Backup\Adventureworks.bak'

    WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

    GO

    Jamie

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply