If you are using a bog-standard flash-based USB stick then performance will be much lower still, especially for writes - despite the lower latency of the solid-state storage, which will help to a degree, many bog-standard sticks won't read much faster than 10MByte/sec and write speeds below 4Mbyte/sec are far from uncommon. This is assuming you are using a SATA/PATA spinning-disk in a USB enclosure. That being said, if you have a lot happening on your internal drives (other DB access and such) you might actually find moving the database to a separate spindle, even one connected by a slower interface, could improve responsiveness (as your DB access is not competing for time on the same spindle with other active IO and so causing latency through extra head movements).
Most USB drives top out at around 25Mb/sec even if the drive within the enclosure is capable of far more due to the limitations of USB2 controllers. If you release the disk for removable by powering off the machine or shutting down SQL server, the drive needs to be plugged in before SQL server next starts.Īs other people have pointed out, you will get lower performance in most case. The drive must be locally mounted - SQL Server will not allow you to attach to a database on network storage.īefore unplugging the drive ensure that either the database is detached, or SQL Server is shut down (or the machine is fully powered off, of course). My experience of this is on SQL7, 20 only, using internal drives in USB enclosures, but I assume it is not something that will have changed in 2008 (and it should work with other USB mass-storage devices like flash sticks). (you can do this through the GUI tools too, attach and detach are usually found under the all tasks" heading on relevant right-click menus). reattach the database with exec sp_attach_db '', '', ''.
copy the files to the new location on the external drive.detach the database with exec sp_detach_db ''.
Moving as simple database to an external drive should be easy: