0

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:

  1. Take backups of all systems databases and user databases and save it in a separate box.
  2. Move MDF and LDF to a separate box for few hours

So after the partitioning/merging of disk drives are completed i can either:

  1. Restore the backups.
  2. 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.?

user9516827
  • 1,345
  • 3
  • 19
  • 41

1 Answers1

0

You should don't have any issues I have done this many times before with no big issues.

One thing to consider is that if you have binaries in E drive, you may have some issues, otherwise should be ok to go.

From what I remember in the past you should check OS privileges, once I had issues with the SQL Server account access to the drives.

Another tip: If you plan to go with the move of the datafiles, you may want to try to shrink the tlog files before put offline, this way you will save some MB/GB and copy time will be faster.

dbamex
  • 470
  • 2
  • 8