March 17, 2009 at 10:37 am
How do I perform a SQL Server backup to a SQL Server NULL device. I have a need to
perform SQL Server backups, but I don't want to send them to tape or a disk file. I want
them to go to SQL Server's equivilent of the NULL device.
Thanks
March 17, 2009 at 10:52 am
what's the purpose of doing that ?
If you don't want backups and you don't want PIT recovery, just put your database in simple recovery.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 17, 2009 at 6:31 pm
There's no point in sending a backup to NULL. It doesn't clear the log, doesn't help space or anything else.
May 14, 2009 at 4:29 pm
Backing up to a nul device can be used as a quick test for SAN throughput. By using a database that is larger than the controller cache and backing up to a nul device, the backup becomes a continuous read of the database files from the SAN. At the end of this backup a number for so many MBs backed up per sec is provided and you can then compare that to the speed promised by the SAN admins.
The command is:
backup database yourdatabase to disk = ‘NUL:’ with stats = 1
June 1, 2009 at 4:31 pm
Backup to Null also allows you to perform a lighter Differential Backup when you use SAN Snapshot based backup.
May 31, 2011 at 12:37 pm
I've just run a backup to the nul device and it does clear the log.
I run this on sql server 2008 R2:
BACKUP LOG mydb TO DISK = N'NUL' WITH NOFORMAT, NOINIT, NAME = N'mydb-LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
A series of backups to the null device may prove usefull with a mirrored configuration (where recovery mode cannot be set to simple) to prevent log from growing during massive updates to the database.
Anybody tried this in a production environment?
Charly
May 31, 2011 at 12:41 pm
carloscolombo 39235 (5/31/2011)
A series of backups to the null device may prove usefull with a mirrored configuration (where recovery mode cannot be set to simple) to prevent log from growing during massive updates to the database.
Sure, if you're willing to toss recoverability out of the door as that breaks the log chain in a subtle way.
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
May 31, 2011 at 1:05 pm
Definetively, not something to do on a daily basis!
Of course, as soon as the massive updates are done a full backup is needed... I mean, one that doesn't go to nul 🙂
May 31, 2011 at 1:21 pm
By the way, what you're proposing is no different from taking regular log backups and deleting the resultant log backup files.
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
August 16, 2011 at 12:52 am
i googled an article about how to backup server.
hope it can help.
September 7, 2011 at 5:40 am
Figured it was worth adding. I actually came across an instance where I found a valid use for a regular backup to the nul: device.
I have a database where the database by itself only needs to be in simple recovery. We can tolerate loss of data, but NOT the loss of the database itself, so we have it mirrored to an alternate system (in another state).
However, mirroring requires full recovery mode. So, we simply backup the logs to the nul: device.
September 7, 2011 at 5:46 am
Sorry Carlos.. missed your post saying pretty much the same thing.
September 7, 2011 at 5:53 am
One instance I found where this should have been the course of action recently.
We have SQL instances that are part of a critical application that we don't manage. Since they maintain, I never caught the fact that they didn't have regular log backups for an instance we setup for mirroring.
Their normal backup schedule is to backup to disk, copy those backups to another server, purge old ones.
Their fix recently for a log file out of space issue, was to increase logs... When they physically ran out of space, since they now couldn't run a backup to disk (no space), the fix was to break the mirror and truncate the log.
October 15, 2023 at 6:43 pm
I realize that this is a wicked old post but thought I'd add why I use a backup to NUL.
I do it for testing code demonstrations, especially demonstrations concerning "Minimal Logging". I don't really want to backup a (for example) 54GB test database on my laptop but I do need to do a backup to simulate a production database that's in the FULL Recovery Model. Doing a backup to the NUL device does the trick rather nicely.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2023 at 8:01 pm
I'll add one more. I have a customer that has some databases that don't need to be backed up (QA or similar purposes). If the db were lost, they'd recreate it from another source. However, they don't want to get alerts from the monitoring systems about missing backups.
In this case, they have a loop for backups. Since they skip some databases (model, tempdb), it was easy to add logic to back up some dbs to NUL, which means the monitoring system doesn't alert.
They could alter the monitoring system, but that's a more complex thing to do, and easy for DBAs to make a mistake in there. Easier to modify the backup process.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply