Most of us are by now familiar with external hard drives that attach to a computer through a USB 2.0 or 1394 interface. The prices on these units have stayed stable or dropped slightly, while the amount of storage available in the average drive has exploded -- up to 250 GB or more. Some systems administrators use them as quick-and-dirty offline backup systems, either due to budget constraints or because they're operating in a small environment and don't need anything more robust.
As useful as these external drives may be, they have one major caveat that SQL Server administrators must remember. Most of them ship from the factory preformatted for immediate use; the administrator can plug them in and copy files over without having to partition and format the drive first. However, there's a good chance these preformatted drives are FAT32, not NTFS.
This is particularly important to database administrators for a number of reasons. NTFS drives provide better data integrity protection and some degree of built-in security (although it's no substitute for, say, a separate and robust on-disk encryption scheme). The bigger problem though is the amount of space any one file can take up on a FAT32 drive, which is not more than 2 GB. Since many databases run into the dozens of gigabytes when backed up, attempting to copy or directly back up such a file to a FAT32 drive will result in an error, which may be spuriously traced to other things.
To that end, if you use external drives as part of your SQL Server backup routine, make sure they're formatted with NTFS before you put them into use. The total amount of time spent doing this won't greatly impose on your schedule.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com
Step-by-Step Guide: Ensuring data integrity in SQL Server
Tip: Don't use backup and restore to distribute database tools
Topic: Look up SQL Server backup and restore best practices
