Moving SQL Server Database files

Process for moving SQL database files:

To optimize your drive space utilization and better capacity planning we need to re shuffle our drives and databases on it. Also this is the most common task when you are decommissioning your old servers and need to change the location of the database files to point it to the drives you want.

Detach the database, copy the files to new location and attach the database is an option. The below steps are other way of doing it.

1)     Set the database in single user mode

2)     Take the database offline.

3)     Change all the files (primary, log) location using the alter statement.

4)     Bring the database online

5)     Set the database to Multi user mode.

Considering student is my database name and present location of files is in C drive. Now moving data file to D drive D-> DATA and log file to D->LOG.

ALTER DATABASE STUDENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE STUDENT SET OFFLINE

ALTER DATABASE STUDENT MODIFY FILE (NAME= Student_data, Filename = 'D:\DATA\student_data.MDF')

ALTER DATABASE STUDENT MODIFY FILE (NAME= Student_log, Filename = 'D:\LOG\student_log.LDF')

ALTER DATABASE STUDENT SET ONLINE

ALTER DATABASE STUDENT SET MULTI_USER