Moving Database File to Drives

The best practice for SQL databases is to place the database data files (.mdf) and the database log files (.ldf) on separate drives.

This topic describes how to move the ObserveIT database files to designated drives.

The following steps assume two designated drives are present at the SQL machine. In the example, the database drive is assigned the drive letter E:, while the log drive is assigned the drive letter F:.

1. Connect to the SQL server or to a computer with SQL Server Management Studio installed.

2. Open Microsoft SQL Server Management Studio.

   The Connect to server window opens.

   

3. Type in the SQL server's FQDN or IP address into the Server name field.

4. Select Windows Authentication if your account has sysadmin permissions on the SQL server. Otherwise, choose SQL Server Authentication and log in with a sysadmin-level account.

5. Click Connect.

6. Paste the following code into the New Query window:

   This action will stop all ObserveIT databases and will cause downtime for all ObserveIT services.

   USE MASTER;
                   

   GO
                   

   ALTER DATABASE ObserveIT_Data
                   

   SET SINGLE_USER
                   

   WITH ROLLBACK IMMEDIATE;
                   

   GO
                   

   EXEC MASTER.dbo.sp_detach_db @dbname = N'ObserveIT_Data'
                   

   GO
                   

   USE MASTER;
                   

   GO
                   

   ALTER DATABASE ObserveIT
                   

   SET SINGLE_USER
                   

   WITH ROLLBACK IMMEDIATE;
                   

   GO
                   

   EXEC MASTER.dbo.sp_detach_db @dbname = N'ObserveIT'
                   

   GO
                   

   USE MASTER;
                   

   GO
                   

   ALTER DATABASE ObserveIT_Archive_1
                   

   SET SINGLE_USER
                   

   WITH ROLLBACK IMMEDIATE;
                   

   GO
                   

   EXEC MASTER.dbo.sp_detach_db @dbname = N'ObserveIT_Archive_1'
                   

   GO
                   

   USE MASTER;
                   

   GO
                   

   ALTER DATABASE ObserveIT_Archive_Template
                   

   SET SINGLE_USER
                   

   WITH ROLLBACK IMMEDIATE;
                   

   GO
                   

   EXEC MASTER.dbo.sp_detach_db @dbname = N'ObserveIT_Archive_Template'
                   

   GO
                   

   USE MASTER;
                   

   GO
                   

   ALTER DATABASE ObserveIT_Analytics
                   

   SET SINGLE_USER
                   

   WITH ROLLBACK IMMEDIATE;
                   

   GO
                   

   EXEC MASTER.dbo.sp_detach_db @dbname = N'ObserveIT_Analytics'
                   

   GO
                   

7. Click Execute. Wait for the query to finish.

8. Format 2 new disks in the machine. See Formatting a Disk for Graphic Images Storage and the Database

   In the example below: disk E: for the database data files and disk F: for the database log files.

   From File Explorer, navigate to disk E:.

9. Create a new folder, MSSQLDATA.

10. Navigate to disk F:.

11. Create a new folder, MSSQLLog.

12. Open PowerShell and run as an administrator.

13. If prompted Do you want to allow this app to make changes to your device? click Yes.

14. Paste the following code into the PowerShell window:

    Get-ChildItem 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\' | Where-Object {$_.Name -like "*observeit*" -and $_.Name -like "*mdf"} | Move-Item -Destination E:\MSSQLDATA\
                    

    Get-ChildItem 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\' | Where-Object {$_.Name -like "*observeit*" -and $_.Name -like "*ldf"} | Move-Item -Destination F:\MSSQLLog\
                    

15. Enter key at the final prompt.

16. Return to the SQL Server Management Studio.

17. Click New Query.

18. Paste the following code into the New Query window:

    CREATE DATABASE [[observeIT_Archive1] ON
                    

    ( FILENAME = N'E:\MSSQLDATA\ObserveIT_Data_Data.mdf' ),
                    

    ( FILENAME = N'F:\MSSQLLog\ObserveIT_Data_Log.ldf' )
                    

    FOR ATTACH
                    

    GO
                    

    CREATE DATABASE [ObserveIT] ON
                    

    ( FILENAME = N'E:\MSSQLDATA\ObserveIT_Data.mdf' ),
                    

    ( FILENAME = N'F:\MSSQLLog\ObserveIT_Log.ldf' )
                    

    FOR ATTACH
                    

    GO
                    

    CREATE DATABASE [ObserveIT_Analytics] ON
                    

    ( FILENAME = N'E:\MSSQLDATA\ObserveIT_Analytics_Data.mdf' ),
                    

    ( FILENAME = N'F:\MSSQLLog\ObserveIT_Analytics_Log.ldf' )
                    

    FOR ATTACH
                    

    GO
                    

    CREATE DATABASE [ObserveIT_Analytics] ON
                    

    ( FILENAME = N'E:\MSSQLDATA\ObserveIT_Archive_1_Data.mdf' ),
                    

    ( FILENAME = N'F:\MSSQLLog\ObserveIT_Archive_1_Log.ldf' )
                    

    FOR ATTACH
                    

    GO
                    

    CREATE DATABASE [ObserveIT_Archive_Template] ON
                    

    ( FILENAME = N'E:\MSSQLDATA\ObserveIT_Archive_Template_Data.mdf' ),
                    

    ( FILENAME = N'F:\MSSQLLog\ObserveIT_Archive_Template_Log.ldf' )
                    

    FOR ATTACH
                    

    GO
                    

19. Click Execute. Wait for the query to finish.

20. Close the SQL Server Management Studio.

Related Topics:

Installing and Configuring Databases

Installing the ObserveIT Databases

Verifying Database Installation

Installing Database Maintenance

** Go to Custom Installation Steps.