July 26, 2010 at 4:11 am
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;-)
July 26, 2010 at 4:48 am
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
July 26, 2010 at 5:30 am
Thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 26, 2010 at 6:21 am
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.
July 26, 2010 at 6:36 am
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
February 9, 2011 at 3:00 am
Will it dscard only inactive logs...
or
the logs which have not backed up?
Thanks
February 9, 2011 at 3:05 am
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
February 9, 2011 at 7:40 am
Oh, this sounds like a Question of the Day scenario. One of you should totally send this in.
March 29, 2011 at 6:13 am
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?
March 29, 2011 at 8:20 am
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.
March 29, 2011 at 8:23 am
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
March 29, 2011 at 8:25 am
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.
March 29, 2011 at 11:11 am
Why should you use this command for?
March 29, 2011 at 11:41 am
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.
March 29, 2011 at 11:51 am
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply