Moving Data Files to Another Disk/Location (MySQL & SQL Server)

From time to time it becomes necessary to optimize your system by changing where your data files and/or logs are located on the filesystem. While SQL Server is my primary platform, occasionally one has to use other systems; in this case mysql. Instructions are included for both database servers running on Windows OS.

Action Plan SQL Server

  1. Choose Method ( backup/restore OR detach/attach)
  2. Backup / Detach Database
  3. Restore Database / Move Data Files & Attach Database
  4. Test the Database

Action Plan MySQL

  1. Shutdown Service
  2. Change Configuration Options
  3. Restart Service
  4. Test the Service

This is how:

SQL SERVER

I opted to use the backup and restore method.

Backup the Database with the following command:

BACKUP DATABASE database_name TO DISK = N'T:\Backups\database_name.bak'
GO

Restore the database; the MOVE option will handle the copying of the files. You need to know the names logical names of the data files and log files. Use the sp_helpfile command to get this information prior to attempting the restore operation.

USE database_name
GO
sp_helpfile
GO

Restore the Database using the following syntax:
RESTORE DATABASE database_name
FROM DISK='T:\Backups\database_name.bak'
WITH MOVE 'database_name_data' TO 'D:\SQLData\database_name_data.mdf',
MOVE 'database_name_log' TO 'E:\SQLLogs\database_name_log.ldf',
REPLACE
GO

Test connectivity to the database.

MySQL

Shutdown the mysql server
Open the my.ini file found in the directory where you installed mysql usually “C:\Program Files\MySQL\MySQL Server 5.0\my.ini”
find the line
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
Comment out the line, by putting a # sign in front of it.
Add a comment to document the change(date, time, reason).
Add a new line with the desired configuration
datadir="D:/MySQL Datafiles/data/"
Save the file.
Move/Copy the files to the new disk/location.
xcopy /D /E /C /V /Y /Z /H / "C:/Program Files/MySQL/MySQL Server 5.0/Data/" "D:/MySQL Datafiles/data/"

Start the mysql service.
Test the service, to ensure that all the databases are up, and accessible.
Delete files from the original location, if you copied files instead of moving them.

Conclusion

For me the great perk of using SQL Server is that this configuration is done at the individual database level. Therefore, there is no downtime on other hosted databases during a move of a database file. With mysql this configuration is managed for the entire instance, therefore all files for the instance must be contained in the same folder, so the move requires all your files be moved, and in doing so, downtime on all hosted databases.

Advertisements

3 thoughts on “Moving Data Files to Another Disk/Location (MySQL & SQL Server)

  1. Sorry My site not live now. I want to take all databse from online server and install to local (I want to move new server) Because now i did not have backup of database only mysql files inside server by transfer mysql file is it posible to install table inside my new server

  2. I had to also change the following lines in the my.ini to get it to restart (for MySQL):

    innodb_data_home_dir = “D:/MySQL Datafiles/data”
    innodb_log_group_home_dir = “D:/MySQL Datafiles/data”

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