Backup database to disk ''NUL''

  • sivaprasad

    SSCertifiable

    Points: 6183

    I just tried this below backup statement.

    BACKUP DATABASE shrink_test TO DISK = 'NUL'

    Getting the following result.

    Result

    Processed 10128 pages for database 'shrink_test', file 'shrink_test_data' on file 1.

    Processed 1 pages for database 'shrink_test', file 'shrink_test_log' on file 1.

    BACKUP DATABASE successfully processed 10129 pages in 28.287 seconds (2.933 MB/sec).

    Database backed up: Database: shrink_test, creation date(time): 2005/04/15(08:13:12), pages dumped: 16603, first LSN: 302:422:1, last LSN: 302:567:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'d:\sqldata\MSSQL\BACKUP\NUL'}).

    Can any one explain what this backup statment actually does?

    When this statement would be useful?

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • Mike Metcalf

    SSCrazy Eights

    Points: 8763

    Doesn't it just backup the database to a file called 'NUL' in the default backup location?

    I've not had a play but off the top of my head that's the most logical answer to me.

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    It backs up the database to the NUL device, i.e. it throws the backup away, but as far as SQL Server is concerned, it has just done a full backup of the database.

  • sivaprasad

    SSCertifiable

    Points: 6183

    When this stament would be useful? for what?

    Please explain..

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    I've used it a few times when testing log shipping on a very large database, when I haven't had enough disk space to do a full backup.

    Backing up to the NUL device makes SQL Server think it has done a full backup, and you can then do Transaction Log backups.

  • Mike Metcalf

    SSCrazy Eights

    Points: 8763

    of course, what a plonker.

    I have no defense, I must have been having one of those days yesterday

  • Kishore-132325

    Hall of Fame

    Points: 3784

    wow...this is amazing..... never was aware of this before....thanks guys ...

     

  • sivaprasad

    SSCertifiable

    Points: 6183

    Ian Scarlett,

    Do you mean to say that when there is space constraint to take full backup it would be useful?

    But this make SQL Server to believe that full backup being taken, but the database full backup actually not available.

    In this scanario the transaction log backup followed by the 'Backup to NUL' would contain entries activities made after the last full backup.

    Is it possible to recover the database when something went wrong, without losing any data?

     

     

     

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    In this case, I was only testing the production of the transaction log backups, and transmission of the logs to another site, not the full recovery mechanism.

    As you suggested, it isn't possible to use any of the transaction logs after the NUL backup to actually recover the database.

  • tlbeta

    Valued Member

    Points: 64

    In your scenario, data loss is absolutely possible. But I think the "Backup to NUL" feature is not designed for real backup at all. It's to resolve space issue only. In some cases, it's very useful.

  • Beatrix Kiddo

    SSC-Dedicated

    Points: 32407

    Why have you bumped a 9 year old thread?

  • tlbeta

    Valued Member

    Points: 64

    Beatrix Kiddo (6/16/2014)


    Why have you bumped a 9 year old thread?

    Oh, sorry, not realized this is a very old thread. I googled this by chance. When I read through the thread, I clicked the "reply" anchor. Should have check the timestamp first. Thanks for the reminder.

  • Jeff Moden

    SSC Guru

    Points: 996826

    tlbeta (6/16/2014)


    Beatrix Kiddo (6/16/2014)


    Why have you bumped a 9 year old thread?

    Oh, sorry, not realized this is a very old thread. I googled this by chance. When I read through the thread, I clicked the "reply" anchor. Should have check the timestamp first. Thanks for the reminder.

    It's not a problem. Even 9 year old threads have good info in them. No harm in adding to them.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Rudyx - the Doctor

    SSC-Forever

    Points: 43696

    It's nice to know that Windows still supports 'old' DOS 5.0 'stuff' !!!

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

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