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
Detach the database (SSMS, right-click DB, Menu will have detach option)
Copy data and log files to new E: location
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.
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.
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
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.
Hello Robert
Thank you, running SSMS in ADMIN mode was the trick
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