SQL Server Express 2008: Can I store database files (.mdf, .ldf) in directory other than the default DATA directory? I have a SQL Server Express 2008 instance of which I am attaching databases from development systems quite often. Typically I transfer the .MDF and .LDF files to the instance's DATA folder and reattach, and everything is fine. In the past when we used Access instead of SQL Server Express, I would keep the database file on a separate drive (SSD) which tended to increase performance, as well as let me use BitLocker drive encryption on the whole SSD for easy encryption and whole drive backups. I would like to do the same with SQL Server Express.

Can I put the .mdf and .ldf files on a separate drive (not in the instances DATA folder) without any ill effects? Do I need to change settings through the Management Studio to do this? Can I still use BitLocker Encryption?

Thanks very much!
You can place your database files anywhere you want, you just have to specify these locations within SQL Server itself.  There are two ways to relocate those files.

Create in a new location

When you create a database or add a file to the database, you can specify the new location and the file will be created there.  For example, if I wanted to add a new data file to my database, I would do the following

ALTER DATABASE [foo]
ADD FILE (name=foo_data_02, filename='X:\YourCustomLocation\foo_data_02.ndf');


As long as your SQL Server Service Account has access to the path, whatever you put after 'filename' will be where your file is created.

Move to a new location

You can also move an existing data file to a new location.  This requires a couple steps.  The first is to change the path within SQL Server first, like so:

ALTER DATABASE [foo]
MODIFY FILE (name=foo_data_01, filename'X:\YourCustomLocation\foo_data_01.mdf`)


By modifying this, the next time this database starts up it will look for the file in that location.  Once you've changed it, you will actually need to move the file.  To do this, take the database offline:

ALTER DATABASE [foo]
SET OFFLINE WITH ROLLBACK IMMEDIATE;


Once the database is offline, then copy the file to the new location using your preferred method.  Once it has been copied, bring your database online:

ALTER DATABASE [foo]
SET ONLINE;


At this point, your database will now be running with your files in the new location.