Extremely Quick Backups

  • Comments posted to this topic are about the item Extremely Quick Backups

  • Be careful with this command in production as you 'break' the log chain if in full or bulk logged recovery models.
    You can still use it specifying WITH COPY_ONLY though.

  • Interesting. If you look in the linked documentation, you're led to believe that the ... FILE = NUL syntax (without the single quotes) is the correct one, but clearly that doesn't work in practice.


    Just because you're right doesn't mean everybody else is wrong.

  • Rune Bivrin - Friday, January 12, 2018 12:32 AM

    Interesting. If you look in the linked documentation, you're led to believe that the ... FILE = NUL syntax (without the single quotes) is the correct one, but clearly that doesn't work in practice.

    Yes..
    https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
    This states NUL (not 'NUL')

    { DISK | TAPE | URL} = { 'physical_device_name' | @physical_device_name_var | NUL }

  • Interesting question to end the week on, thanks Steve.
    However, I wonder why anyone would need to do this (other than obtaining a benchmark)?

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Stewart "Arturius" Campbell - Friday, January 12, 2018 2:39 AM

    Interesting question to end the week on, thanks Steve.
    However, I wonder why anyone would need to do this (other than obtaining a benchmark)?

    The only use case I have for this, it is when you need to have databases in full recovery model, but you don't care about point in time recovery (sounds crazy, but keep reading), for instance if you have Availability Groups/Mirroring in DEV but you don't really care about transaction logs, you can clear the log using TO DISK = 'NUL'

    Cheers

  • sqldoubleg - Friday, January 12, 2018 4:09 AM

    Stewart "Arturius" Campbell - Friday, January 12, 2018 2:39 AM

    Interesting question to end the week on, thanks Steve.
    However, I wonder why anyone would need to do this (other than obtaining a benchmark)?

    The only use case I have for this, it is when you need to have databases in full recovery model, but you don't care about point in time recovery (sounds crazy, but keep reading), for instance if you have Availability Groups/Mirroring in DEV but you don't really care about transaction logs, you can clear the log using TO DISK = 'NUL'

    Cheers

    Doesn't sound crazy to me -- we have a Data Warehouse in an Availability Group in production, and that's exactly what we use this for. We have full or differential backups to recover us to any particular day's file load, and we have to keep the original files for other purposes, so we can always reload them if necessary.

  • This really should include the WITH COPY_ONLY in the command. Not using this breaks your backup chain.

  • Bobby Russell - Friday, January 12, 2018 8:29 AM

    This really should include the WITH COPY_ONLY in the command. Not using this breaks your backup chain.

    That's not always a concern for some people.

  • You can also use this with AGs. Create the db, do a nul backup, start direct seeding.

    It is useful to eliminate disk/network as a backup bottleneck if you need to test a system as well. If this is a production dB, then you do want copy_only if you need an intact chain.

  • Interesting, and answers are so far only 37 percent correct. Steve, you hit the nerve. 😉

    Thanks!

  • Steve Jones - SSC Editor - Friday, January 12, 2018 8:50 AM

    You can also use this with AGs. Create the db, do a nul backup, start direct seeding.

    It is useful to eliminate disk/network as a backup bottleneck if you need to test a system as well. If this is a production dB, then you do want copy_only if you need an intact chain.

    With all due respect, this QoD is a bad choice and I wouldn't have chosen it. IMHO, QoD should introduce interesting and challenging topics, not something about picking the right syntax.

    The title 'Extremely Quick Backups' is misleading since it clear to us now that it does not backup anything. And I also disagree with your statement 'It is useful to eliminate disk/network as a backup bottleneck if you need to test a system...'  What purpose does this command fulfil on a test system. Without actually writing to a file system how reliable and useful is the result of such a test?

    No offence, just a Friday nit-picking rant. Have a great weekend.

  • RandomStream - Friday, January 12, 2018 5:07 PM

    Steve Jones - SSC Editor - Friday, January 12, 2018 8:50 AM

    You can also use this with AGs. Create the db, do a nul backup, start direct seeding.

    It is useful to eliminate disk/network as a backup bottleneck if you need to test a system as well. If this is a production dB, then you do want copy_only if you need an intact chain.

    With all due respect, this QoD is a bad choice and I wouldn't have chosen it. IMHO, QoD should introduce interesting and challenging topics, not something about picking the right syntax.

    The title 'Extremely Quick Backups' is misleading since it clear to us now that it does not backup anything. And I also disagree with your statement 'It is useful to eliminate disk/network as a backup bottleneck if you need to test a system...'  What purpose does this command fulfil on a test system. Without actually writing to a file system how reliable and useful is the result of such a test?

    No offence, just a Friday nit-picking rant. Have a great weekend.

    Sorry to butt in, in the backup process there are 3 steps if you want, read, process and write. Usually you don't read and write to the same device, or at least you shouldn't.

    Eliminating the write part you can test how fast you do the read  and process parts, if you see that your read speed if good but you backup performance isn't, the problem must be elsewhere.

    True that it doesn't give you a complete picture, but I wouldn't say it's totally useless.

    Cheers

  • This gives you an upper bound on backup speed/lower bound on completion.

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

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