Migrate all Database on a local server from one disk to another

SQL Server Administration
1

Hello

For storage capacity reason I want to move all the Databases on my Local Laptop server from Drive C to Drive E
Can I simply change the Log and Data path in SSMS and move the files from one drive to another ?
N.B: I am the single and only user

Or is it more tricky ?
Thanks for your Help

image
image935×655 25.6 KB

2

Probably the easiest way:

  1. Detach the database (SSMS, right-click DB, Menu will have detach option)

  2. Copy data and log files to new E: location

  3. Attach database files (SSMS, right-click Server, Attach option). You will need to find the files on the new drive location. Double check that the log file is selected from the new location, it may default to the old one.

3

Start here: Move Database Files - SQL Server | Microsoft Learn

For normal user database files - the basic process is to update the system with the new name, take the database offline, move the file, bring the database online. For system databases it can get tricky - depending on what files you are moving.

4

Hello Robert_Volk
I've tried your suggestion (Moving MDF and LDF) but it gives an error
I'm still wondering if I don't have to simply move the WHOLE DATA Folder to the new drive and change the path in SSMS

Here is the message

TITLE: Microsoft SQL Server Management Studio
------------------------------

An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details.

------------------------------
BUTTONS:

OK
------------------------------
TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'localhost\NEWMS15UP'.  (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Unable to open the physical file "E:\MSSQL15.NEWMS15UP\MSSQL\DATA\bcTracking.mdf". Operating system error 5: "5(Access is denied.)".
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5120)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5120-database-engine-error
5

You may be able to try again by running SSMS as Administrator. Right-click the SSMS icon and choose "Run as Administrator", then try to attach.

Also, you might want to put the MDF and LDF files in another directory other than the MSSQL instance directories. That way you can assign the necessary folder permissions without impacting the SQL system files and databases.

6

Hello Robert
Thank you, running SSMS in ADMIN mode was the trick :slight_smile:
About the directory, it was obviouly already appart of the MSSQL Instance because the intent was to precisely to move to another drive to gain storage space