Taking backup with option "Disk = nul"

  • Bhuvnesh

    SSC Guru

    Points: 59344

    hi ,

    can anybody tell me what exactly below backup query does ? and where it keep the backup file after taking backup with this option

    BACKUP DATABASE DB_PROD TO DISK = N'nul'

    And why does it work with "nul" rather than with "null" keyword ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Gail Shaw

    SSC Guru

    Points: 1004424

    NUL is a special 'file' in the file system (same as LPT1, COM1, CON if you remember back to the DOS days). It's the nul device, the trash bin, the black hole of the file system.

    Anything written to nul is discarded. Hence you're not writing the backup file anywhere, it's much the same as backing the database up to a file, then deleting the file (except the file is never written in the first place)

    It's not NULL, because NULL is a SQL Server keyword, not a file system concept.

    May help - http://sqlinthewild.co.za/index.php/2009/08/31/backing-up-to-nul-vs-backup-with-truncate-only/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Bhuvnesh

    SSC Guru

    Points: 59344

    Thanks

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Ray Mond

    SSCertifiable

    Points: 7099

    We use that to determine the theoretical maximum backup throughput of the database, since your backup can only be as fast as what SQL Server can read from disk during the backup.

    More details here: http://www.sqlbackuprestore.com/speedingupbackups.htm

    The thing to remember is that while your backup data goes nowhere, SQL Server still recognises it as a 'valid' backup. So if you are performing a full backup of your production database to a nul device, all subsequent differential backups will be useless, until you perform a non-nul full backup again. To avoid this, if you're using SQL Server 2005 or later, use the COPY_ONLY option.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Ray Mond (7/26/2010)


    The thing to remember is that while your backup data goes nowhere, SQL Server still recognises it as a 'valid' backup.

    Yup. SQL doesn't care what the file system does with the data it's been given. As far AS SQL is concerned, NUL is a file, it's the filesystem's job to deal with the specifics of where and how. Imagine SQL handing the file system chunks of data and the file system throwing them away.

    Backup Log ... to disk = 'Nul' is a really nasty way to break the log chain and not have it easily noticeable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • forsqlserver

    SSCoach

    Points: 18900

    Will it dscard only inactive logs...

    or

    the logs which have not backed up?

    Thanks

  • Gail Shaw

    SSC Guru

    Points: 1004424

    It'll do exactly the same thing as taking a normal log backup, then deleting the .trn file.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Brandie Tarvin

    SSC Guru

    Points: 172462

    Oh, this sounds like a Question of the Day scenario. One of you should totally send this in.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Rhox

    Hall of Fame

    Points: 3402

    And can we use this command on a mirrored db?

    Or do we need to take other actions to shrink the log-file of a mirrored db?

  • Brandie Tarvin

    SSC Guru

    Points: 172462

    Rhox (3/29/2011)


    And can we use this command on a mirrored db?

    Or do we need to take other actions to shrink the log-file of a mirrored db?

    I'm curious where you got the idea that this thread had anything to do with shrinking. The command being discussed has the effect of turning the database (temporarily) to SIMPLE mode because it throws out the transaction log backup. It doesn't shrink the log file at all.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Brandie Tarvin (3/29/2011)


    The command being discussed has the effect of turning the database (temporarily) to SIMPLE mode because it throws out the transaction log backup

    No it doesn't. It throws away the log backup, but SQL thinks that the log backup was written, it doesn't know that it was written to nowhere. The DB remains in full recovery, keeps retaining the logs. However all log backups past that point are totally useless because of the missing log backup file.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Brandie Tarvin

    SSC Guru

    Points: 172462

    GilaMonster (3/29/2011)


    Brandie Tarvin (3/29/2011)


    The command being discussed has the effect of turning the database (temporarily) to SIMPLE mode because it throws out the transaction log backup

    No it doesn't. It throws away the log backup, but SQL thinks that the log backup was written, it doesn't know that it was written to nowhere. The DB remains in full recovery, keeps retaining the logs. However all log backups past that point are totally useless because of the missing log backup file.

    Then I misread your blog post. Why did you stick a comment about Simple recovery in that?

    EDIT: Nevermind. I skimmed and missed the "does NOT" part of the post. Sorry.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Rhox

    Hall of Fame

    Points: 3402

    Why should you use this command for?

  • Brandie Tarvin

    SSC Guru

    Points: 172462

    Rhox (3/29/2011)


    Why should you use this command for?

    Honestly, I wouldn't use this command. I can't think of one good reason why I would need it in any production environment I manage.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Rhox (3/29/2011)


    Why should you use this command for?

    You wouldn't in any reasonable situation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 20 total)

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