USB External HD for sql data files?

[jsteph]

I have a non-critical database that collects large amounts of data, which we at different times, may need to detach, move the files, and re-attach on another server in another physical location. The .mdf files total around 200 gigs, and it's not time-effective to spin those to tape to move the data. I would like to point the .mdf files to an external USB drive. Is this supported by sql-2000?

I'm aware of the possible performance issue and it doesn't concern me, also the data is not so critical that it needs to be on an ultra-reliable disk system. It's just scads and scads of data that I'd like to pick up, throw in a breifcase and take across town in 20 minutes instead of waiting hours for tape or even more for lan transfer.
--Jim

 

[ptheriault]

I don't think that is going to work. SQL Server only supports mounted drives. Here is a KB article.

http://support.microsoft.com/default.aspx/kb/819546

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Jim,
let me correct myself. I have SQL 2005 Dev edition installed on my laptop. I plugged in my 50GB usb hard drive. I detached and moved the Order db to the USB drive. I was then able to attach the Orders database.
So I would say that you can do that...
However, somethings to consider. If you remove the USB drive while the database is active it generates lots of errors when you try to access the db. Also, what if the drive letter that is assigned to your USB device changes? That will cause some problems too. So I'm not sure that this is the best approach, but it will work.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jsteph]

Paul,
Thanks very much, I'm going to give that a try, I'll let you know what happens
--Jim