January 11, 2018 at 11:34 pm
Comments posted to this topic are about the item Extremely Quick Backups
January 11, 2018 at 11:40 pm
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.
January 12, 2018 at 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.
January 12, 2018 at 1:30 am
Rune Bivrin - Friday, January 12, 2018 12:32 AMInteresting. 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 }
January 12, 2018 at 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)?
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
January 12, 2018 at 4:09 am
Stewart "Arturius" Campbell - Friday, January 12, 2018 2:39 AMInteresting 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
January 12, 2018 at 6:24 am
sqldoubleg - Friday, January 12, 2018 4:09 AMStewart "Arturius" Campbell - Friday, January 12, 2018 2:39 AMInteresting 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.
January 12, 2018 at 8:29 am
This really should include the WITH COPY_ONLY in the command. Not using this breaks your backup chain.
January 12, 2018 at 8:49 am
Bobby Russell - Friday, January 12, 2018 8:29 AMThis 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.
January 12, 2018 at 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.
January 12, 2018 at 4:30 pm
Interesting, and answers are so far only 37 percent correct. Steve, you hit the nerve. 😉
Thanks!
January 12, 2018 at 5:07 pm
Steve Jones - SSC Editor - Friday, January 12, 2018 8:50 AMYou 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.
January 12, 2018 at 5:39 pm
RandomStream - Friday, January 12, 2018 5:07 PMSteve Jones - SSC Editor - Friday, January 12, 2018 8:50 AMYou 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
January 14, 2018 at 5:18 pm
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