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

ALTER DATABASE xx SET RECOVERY SIMPLE

ALTER DATABASE xx SET RECOVERY FULL

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.