Moving existing “Data” and “Log” onto two separate disks

Lately one of my customers asked me whether they can move their existing "Data" and "Log" (i.e., Transaction Log) onto two separate disks. The answer is "yes. you can.". You need to follow the following 2 steps:
 
Step 1: Use sp_detach_db to detach an existing database from a specific SQL Server instance. To perform this step, you type the following command (at the SQL Server Management Studio):
 
USE master;
GO
EXEC sp_detach_db @dbname = N’YourDatabase’;
GO
 
Step 2: Manually move your "Data" and "Log" files to your target directory(directories). I would strongly recommend that you place your "Data" and "Log" files each on a separate disk. Then you type the following command (at the SQL Server Management Studio):
 
USE master;
GO
CREATE DATABASE YourDatabase
ON (FILENAME = ‘D:\Data\YourDatabase_Data.mdf’), 
(FILENAME = ‘E:\Log\YourDatabase_Log.ldf’)
FOR ATTACH;
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: