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:
- Run a normal log backup to a file, then delete the .trn file.
- Switch to simple mode
- 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.