Post reply

BACKUP / RESTORE

  • August 11, 2009 at 8:34 pm

    #209443

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

    Jamie

  • August 12, 2009 at 4:24 am

    #1037902

    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

  • August 12, 2009 at 6:17 am

    #1037930

    nice question

    "Keep Trying"

  • August 12, 2009 at 8:13 am

    #1038010

    Corrected the path typo.

  • August 25, 2009 at 7:21 am

    #1044149

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

    Could someone explain, please? :ermm:

  • September 1, 2009 at 7:50 am

    #1047527

    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 5 (of 5 total)

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