This question already has an answer here:
- How do I move SQL Server database files? 7 answers
I want to copy my database files(ldf and mdf) into a separate box for few hours as there is a disk space issue in my current sql server box. Currently i have mdf in one drive(:D) and ldf in another drive(:E). I have to move both mdf and ldf into same drive,say :D. I understand that it is recommended to have separate disk drives for LDF and MDF ,but this is a DEV box and to avoid purchase/delay client wants to go with this option.
I have two plans:
- Take backups of all systems databases and user databases and save it in a separate box.
- Move MDF and LDF to a separate box for few hours
So after the partitioning/merging of disk drives are completed i can either:
- Restore the backups.
- Move the MDF and LDF to the merged drive.
For user database ,i understand that the below steps will be sufficient:
- Bring the database offline
- Move the file(s) to the new location manually.
Update the system catalog settings:
ALTER DATABASE DBNAME MODIFY FILE ( NAME = 'DBNAME', FILENAME = 'D:\SQL_Data\DBNAME.mdf' ) GO
ALTER DATABASE DBNAME MODIFY FILE ( NAME = 'DBNAME_log', FILENAME = 'D:\SQLLog\DBNAME_log.ldf' )
Bring the database online
Can someone explain the dangers of second step or direct me how to do second step for system databases.?