Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thank you for the response, this is helpful. Do you happen to have experience with encryption on SQL Server Express database files? Should I expect a substantial performance hit? –  Hammie4 Mar 7 at 18:37
    
Depends on your encryption method. Bit Lockeris tricky, as there are documented performance concerns. If your database remains small (Express is limited to 10 GB), then it probably won't matter. However, if it gets bigger you might want to consider the Enterprise level feature of Transparent Data Encryption. Keep in mind you will always take some overhead for encryption, though. –  Mike Fal Mar 7 at 18:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.