SQL Services News

SQL Services Hot Tip: Backing up to NUL device

06 Mar 2013
Occasionally I’ve come across a site where backups are taken to disk = ‘NUL’. Note that’s NUL with 1 L and not NULL with 2 L’s. This allows a database in full recoverability to perform a pretend log backup and therefore the log file to be cleared and re-used. No resultant file is placed on disk so I’ve seen it recommended in a few places online as a quick fix where a log file has grown out of control.
The important thing to know about this is that you have just broken your recoverability chain and have no point in time recoverability until a new full backup is taken. Therefore it should NEVER be part of a regular maintenance plan (especially in conjunction with a scheduled shrink….EK!). If point in time recoverability is not important to you – use SIMPLE recoverability mode and your transaction log will be managed by SQL Server itself. If you do require point in time recoverability then schedule regular log backups and if your transaction log is still growing larger than expected then look at the activity that is causing it rather than resorting to a sledgehammer fix like using backup to disk = ‘NUL’. If you use that you have achieved nothing more or less than taking a regular log backup and then deleting the file.

This Hot Tip was provided by Rob, one of our Christchurch based SQL Server consultants.
Click here to return to the news items...

Microsoft Gold Data, Silver BI partner © Copyright SQL Services Limited 2019. For information on how SQL Services processes personal data please refer to our group privacy statement on our Group Website URL Privacy Statement Oracle Partner Logo