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.
- Redundancy. (If the data drive goes down then you could restore the log files from a different drive.
- Space. (If you separate the log files from the data files you can better allocate the hard drive space needed for both.
- 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
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
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!