Backup database to disk ''NUL''
-
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?
-
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.
-
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.
-
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.
-
of course, what a plonker.
I have no defense, I must have been having one of those days yesterday
-
wow...this is amazing..... never was aware of this before....thanks guys ...
-
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?
-
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.
-
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.
-
Why have you bumped a 9 year old thread?
-
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.
-
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply