Setting Up Data and Log Files For SQL Server

NOTE: As with all SQL Server ALTER DATABASE commands, I strongly suggest you back up your data before running these scripts.

Among the many questions and options when setting up a SQL database is where the data and log files should reside. I have always separated the data files from the log files for a couple of reasons.

  1. Redundancy. (If the data drive goes down then you could restore the log files from a different drive.
  2. Space. (If you separate the log files from the data files you can better allocate the hard drive space needed for both.
  3. Processing speed (If you place the data files on a faster driver (Solid state, etc) and the log files on a less than fast drive. You could afford faster drive space. Furthermore; a log file is not read intensive while your data file is.

The point is that there are good reasons to separate the log file from the data file. Hopefully, I have gotten you to think about why you put your SQL database files where you put them.

Now let’s assume you have decided to do that but you already have an existing database. No Problem. You can follow the steps below to implement this design without too much difficulty.

Step 1: Run this script to see where the files are actually located (result will be similar to figure 1)

SELECT name, physical_name AS Current_Loc, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'DatabaseName');

Step 2: Take the database offline with this statement. (will take a few minutes)

ALTER DATABASE database_name SET OFFLINE

Step 3:Run these for each one you want to move to a different location. (Skip the ones you do not want to move)

ALTER DATABASE database_name MODIFY FILE ( NAME = databaseName(ie. SQL_Database), FILENAME = 'new_location_name_data.mdf' )
ALTER DATABASE database_name MODIFY FILE ( NAME = databaseName(ie. SQL_Database_log), FILENAME = 'new_location_name_data.ldf' )

Step 4: Bring Database back online with this statement.

ALTER DATABASE database_name SET ONLINE

Step 4: Make sure that the file locations worked by running this statement

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'database_name');

Your result should show the new locations.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


7 − 4 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>