Need to create and access a SQL server database that will reside on an external hard drive

I need to create a SQL server database and add some tables to it. Then access it with a C# application. The problem is that the new SQL server database and it's tables must reside on an external hard drive. How do I point SQL server to this external drive, so that I can create a database on this drive and then create tables and access data on it?

Here are two articles with examples.

http://support.microsoft.com/default.aspx?scid=kb;en-us;q307283&id=kb;en-us;q307283&ln=en-us&rnk=1&sd=msdn&fr=0&qry=q307283&src=dhcs_mspss_msdn_srch&spr=msall

http://www.codeproject.com/cs/database/CreateDB.asp

|||

Hi Steve,

I am interested in setting this up through SQL Server management studio. Your refered links talk about doing it programatically. Neat stuff, but I need to do this through SQL Server management studio. Possibly someone has some more information on this?

|||

You can create a database locally, copy the mdf and ldf files to where you want them, and then attach to the files in the new location. Here is a link in the online help:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6732a431-cdef-4f1e-9262-4ac3b77c275e.htm

Or, right click the server icon and select properties.

Select the Database Settings Page. On that page you can specify what directories you want your data to be in.

|||Once the database and tables have been set up on the external drive, do you know if they can then be viewed and queried through SQL Server Query Analyzer?|||Once the database and tables have been set up on the external drive, do you know if they can then be viewed and queried through SQL Server Management Studio?|||

I tried what I suggested and it didn't work. The Management studio would not let me browse away from the PC it was installed on. I tried hard coding some paths to a remote drive but that didn't work.

Finally, I got this to work (from articlehttp://www.sqlteam.com/item.asp?ItemID=128)

DBCC TRACEON (1807);
GO

USE master;

GO
CREATE DATABASE Test
ON (FILENAME = '\\oak\C\temp\DataBase\SGWTest.mdf'),
(FILENAME = '\\oak\C\temp\DataBase\SGWTest_Log.ldf')
FOR ATTACH;
GO

Once attached, the database will act like any other database. However, I'm beginning to think Microsoft wants the engine and files to be on the same machine. It probably has something to do with performance and reliability :)

|||

Hi Steve. I am just getting back to your post. Thank you for your research and help on this. I am really greatfull to you. You have my vote for a Microsoft MVP! If I knew where to vote, I would vote for you.

I will read the article you suggested. With regards to the above script DBC TRACEON (1807) ...etc. Did you execute this as a SQL stored procedure?

If speed is an issue then I suppose I could always go with an external drive. It appears to me that possibly I can have a seperate instance of SQL server stored on this external drive, and then run that instance off of the external drive. The problem with this though is that I would have to power it up and down. Necessary I suppose if I want to have an external database.

Here is an example of a drive I was thinking about, if I had to go the route of getting an external hard drive.http://www.microcenter.com/byos/byos_single_product_results.phtml?product_id=249658

Talk to you later,

Ralph

sql