Backup Log to Nul– #SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

There are times when you might be working on your demo/lab system and you generate a lot of tranasaction log activity. This isn’t data you want to save, so perhaps you want to remove the activity without saving it.

There are a few choices:

  1. Run a normal log backup to a file, then delete the .trn file.
  2. Switch to simple mode
  3. Run a log backup to nul

The first one is easy, but it’s a pain. I have to go to explorer, or open a VM, delete the file once I find it. The second one is what I’d suggest. In fact, as soon as you install SQL on a lab system, set model to Simple.

The third item is valid, and I ran across this recently. When you use this syntax, make sure you use “nul” and not “null”. We are trying to send to /dev/nul, which is nowhere. If you backup here, then nothing happens. You can use this command:

BACKUP Log sandbox2 TO DISK = N’nul’

This will run a backup, and discard all of the backup data. When I say discard, I mean it’s not written anywhere.

However, this is a real backup. It’s marked as such. This breaks a log chain, and you can do this with a full database backup as well, which means you really need another full backup after this to reestablish a baseline.

Again, I ran across this, but it’s not what you want to do. If you need to clear the log, use



and take a full backup.

More thoughts from Gail Shaw.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

4 Responses to Backup Log to Nul– #SQLNewBlogger

  1. Ian Posner says:

    Another really great use of the nul device is in calculating the throughput for a required backup device: Backup your source database to nul (perhaps using striped backups to increase performance) in order to first get a benchmark of how fast your backup could complete. Then backup to your intended device and compare the two times – if your intended device performs poorer than the nul device, you know the bottleneck is on the backup device. You can also use the size of the backup file/s combined with the backup time to the nul device to calculate your required backup device write speed.

  2. Sean McCown says:

    The article states that you need to use NUL and not NULL, when in reality both will work just fine.

    • Ian Posner says:

      ‘nul’ works on the filesystem – “null” doesn’t. Try it out yourself:

      C:\>dir > nul

      C:\>dir nul

      Directory of \\.

      File Not Found

      C:\>dir > null

      C:\>dir null

      Directory of C:\

      21/08/2017 15:38 1,345 null
      1 File(s) 1,345 bytes

    • midnightdbasean says:

      While that’s true, they both work in SQL, which is what the article is talking about.
      BACKUP DATABASE AdventureWorks
      TO DISK = ‘NULL’;

Comments are closed.