Moving K2 database files

Introduction

When K2 is installed, upon K2 database creation, the associated database files created actually follows the master database’s file directory. A typical file directory will be something like C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA.

As of now, K2 does not allows you to specify the database files directory via the wizard. In order to move the files, you will need to do it manually via SQL management studio.

To begin this, please do ensure that you do have some knowledge in the MS SQL server.

Preparation

  1. Ensure you have SQL Management Studio access
  2. Ensure you have created the path that you will want your files to move to.
  3. Ensure the created path has the correct permission. (This is very important!)
  4. Scripts provided below are based on my environment. My database name is called K2 and my new directory to store my database files is “C:\SQL\K2 DB Files”

Steps

* Before you begin, please ensure you do a snapshot and database backup in your database server before you execute the below steps.

** You are advise to execute this in your staging/testing environment first before doing it in the production.

  1. Stop K2 service
  2. Open the SQL Management Studio
  3. Go to K2 database > Right click > Select PropertiesDB1
  4. Based on the above values, form the script like this:
    • ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = ‘new_path\os_file_name’ );
    • Based on the above script, following are the scripts that i have:DB2
  5. Run the above script to change the database file path
  6. Run the below script to take the K2 database offline
    • ALTER DATABASE K2 SET OFFLINE;
  7. Manually move the files from your current file directory to the new file directory. For my scenario, i moved the files from C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA to C:\SQL\K2 DB Files
  8. Run the below script to take the K2 database online
    • ALTER DATABASE K2 SET ONLINE;
  9. Run the below scripts to check on the database files
    • SELECT name, physical_name AS CurrentLocation, state_desc
      FROM sys.master_files
      WHERE database_id = DB_ID(N’K2′);
    • After running the script, it should show you the new location of your filesDB3
  10. Verify
    • Execute a select statement to one of your K2 database tables, ensure no error occurred.
    • Start the K2 service, ensure K2 service can be started
    • Go to K2 sites (Management, workspace, runtime and etc) to ensure no error displayed.

Reference

Moving database files – https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-2017

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s