In this post I will cover the procedure to move the SCCM database to another drive. I will be moving the Configuration Manager database from C:\ drive to another drive on same server.
My lab setup is simple, the SCCM and SQL server are installed and running on same server. I am currently running Configuration Manager version 1910.
When I logged in to my lab today, I noticed that I didn’t have much free space left on my C: drive. And since long time I always wanted test the SCCM database move from C: drive to another drive. So I think this is the right time to do this. Let’s get started.
First of all, you must backup your Configuration Manager server before moving the database to other drive. Also have a rollback plan if something goes wrong during database move process.
Step 1 – Stop All Configuration Manager services
Locate the Preinst.exe application. You can find Preinst.exe in the <SCCM Installation path>\bin\X64\00000409 on the site server. You can also find the file here – \<SiteServerName>\SMS_<SiteCode\bin\X64\00000409.
Run the command prompt as administrator. Type preinst.exe /STOPSITE and hit enter key.
All the Configuration Manager services are stopped in order. Wait until you see the line All site services are stopped.
Step 2 – Find the SCCM Database Location
If you are not sure about the location of your SCCM database, you can find that out from database properties. Launch SQL server management studio and connect to database engine.
Right-click Configuration Manager database and click Properties.
Under the database properties, click Files. On the right pane, look for the database path and log file path.
Step 3 – Move the SCCM Database to Another drive
In this step we will first detach the SCCM database. Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files.
Right click SCCM database and click Tasks > Detach.
On the Detach Database box, check the box “Drop Connections“. Click OK.
Finally use the below command to move the SCCM database from C:\ drive to another drive which is E:\ in my case. Create a new folder within the new drive, for example SQLDB.
Replace the CM_P01 with your site code.
Copy "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\CM_P01*.*" E:\SQLDB
Go to the destination drive and verify the SCCM database file and log file.
Step 4 – Attach the Configuration Manager Database
Now that we have moved our SCCM database to another drive, we must attach this database. In the SQL server management studio, right click Databases and click Attach.
In the Attach Databases window, click Add and browse to the drive that contains your SCCM database. Select the Database. The database and log file should appear under Database Details. Click OK.
Notice that the database shows Read-Only. Right click SCCM database and run the below query.
Note – After you move the SCCM database, if you don’t set the SQL TRUSTWORTHY property to ON, <ConfigMgr_Install>\bin\x64\CryptoUtility.dll fails to load into SQL Server, and you receive the “invalid cursor state” error message.
ALTER DATABASE CM_P01 SET TRUSTWORTHY
Next, go to SCCM database properties and click Options. Set the drop-down for Database Read-Only and change it from True to False. Click OK.
Right click SCCM database and run the below query.
EXEC sp_changedbowner 'sa'
Ensure that Isolation Level is set to read committed snapshot by running the below query.