Can I install SQL Server on a USB drive?

Question

I'm a developer and I often work remotely when I can rather than commuting to work. I would like to be able to take the dev database environment with me. I know I can access the database remotely, but it slow. I sometimes work where there's no wireless connection. Any ideas?

Marybelle

Can you RDP to your work pc or a pc on site? – Sunny

Answer 1

Yes, it's possible. See this thread on MSDN.

Lawhorn

I guess I assumed that SQL CE wasn't an option. If so, then this is great! – Levins

Answer 2

You can store the data files on an external drive. The server software itself, though, I'd install naively on each machine you use.

This method assumes that your database is small and or you don't care about performance that much.

For SQLServer, you'll probably have good luck if you set it to "autoclose". This will unlock the data files when the db isn't in use for some time. Otherwise, manually detaching the database or stopping SQL Server is advised when you pull the drive.

This is almost certainly an unsupported use case so I'd make sure to keep regular backups in place in case something goes wrong.

Levins

Answer 3

your best bet is to backup the database to a file (on the USB drive) and restore it to a local machine.

You could also copy the file to USB and run the attach file command. I dont know if USB is going to be much better than a network connection.

Sunny

Answer 4

While I'm sure this can be done, depending on how much IO goes on with the DB, you probably don't want to. Flash devices have a limited number of reads/writes until they fail. More specifically, this limit is within the realm of possibility when it comes to running systems off of a flash device (since, technically, all devices have a limited number to failure).

If your solution is to simply port the data around and export/import when you change sites, I'm sure that would work fine. Additionally, a lot of software is released with a "portable device" version, which makes heavy use of system RAM, and greatly decreases the need for device IO. Maybe SQL Server has a version like that?

Contaminate