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,315
  • 2
  • 15
  • 35
  • 1
    Moving system databases other then master is similar to moving user database. If you need to move master database. This MS link guide you step-by-step to move master and other system databases. https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017 – Rajesh Ranjan Jul 02 '19 at 04:56

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
  • 472
  • 2
  • 8