Post reply

Database Back-up / Restore

  • May 21, 2008 at 7:26 am

    #191349

    Hi everyone,

    When I make a Full Back-up (and recovery model = FUll) it will generated a BAK file of the LDF & MDF file

    (the sizes are: LDF => 150 MB and MDF => 30 MB)

    If I restore this BAK File into our test environment, the LDF and MDF files are the same size as the original.

    The Data (MDF) is restored, no problem and is ok. But... I have my questions about the LDF File. I can't browse with Lumigent Log Explorer the LDF File in our test environment and with the query "DBCC LOG (DB_name)" I see 2 records (Begin and End)

    I looked every where, but I can't find why the LDF file is 150 MB and nothing is in it.

    Or is it possible, to restore the LDF file in our test environment that it is equal to our production environment. So that I can browse with Lumigent Log Explorer in our test environment through the LDF file.

    thinks and with kind regards,

    Johan Schoolderman,

    System Administratos

    EPOS BV

  • May 21, 2008 at 8:02 am

    #818581

    It may be empty, but still physically the same size.

    Do dbcc sqlperf(logspace) to see how full the log is. If it's showing nothing, then there's your answer.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • May 21, 2008 at 8:32 am

    #818601

    The log file is set to a certain size, not grown from 0. A restore restores the data, not the log. It sets the log file size, but it doesn't restore the transactions from the original database.

    A log restore might possibly restore some log entries as they are replayed against the new log, but I'm not sure. that would be an interesting test.

  • May 21, 2008 at 9:01 am

    #818632

    Since backups are just from committed transactions, I doubt you can move the log in that manner. Can't the Log Explorer read from log backups directly? I'm pretty sure the Red Gate tool does.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 21, 2008 at 9:52 am

    #818688

    ok - a few misconceptions need to be corrected here.

    "Since backups are just from committed transactions"

    "A restore restores the data, not the log. It sets the log file size, but it doesn't restore the transactions from the original database."

    Backups are not limited to committed transactions. A full or differential database backup will contain enough transaction log to restore the database to a transactionally consistent point in time (the time at which the data read portion of the backup operation completed). It will also contain any uncommitted transactions up to that point - they will be rolled back as part of restore.

    If a restore did not restore transactions then it would be impossible to get a transactionally consistent restored database.

    For an in-depth discussion of this, see:

    http://www.sqlskills.com/blogs/paul/2007/10/25/DebunkingACoupleOfMythsAroundFullDatabaseBackups.aspx

    http://www.sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx

    A backup will not necessarily backup the *entire* log - only the parts that are needed. A log backup will only backup the log that has happened since the last log backup occured (or since the database was switched to FULL/BULK_LOGGED and a full database backup taken). This is why a log analysis tool may not see as much on a restored database as on the original. The only way to get a complete copy of the entire log, regardless of what backups have been done is to make a physical copy of the files and then attach them on the test system.

    Hope this helps.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • May 22, 2008 at 3:28 am

    #819028

    Hi Everyone,

    Thanks for your reply's!

    And I have finally my answer, thanks!

    @crever: I don't forget "dbcc sqlperf(logspace)" 🙂

    @Grant Fritchey: Log Explorer can read an back-up file of the LOG

    @paul-2 Randal: Thanks for your information, very use-full!

    with kind regards,

    Johan Schoolderman

    System Administrator

    EPOS BV

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

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