Moving KeyACCIDENT SQL Database

Issue

To move the KeyACCIDENT Database, please follow the steps below

Solution

The following method is best used if you have an Audit history that you want to retain, if all you want to move is the data, we would recommend the ‘Basic’ option

 

Note - This guide is meant for users that are comfortable administering Microsoft SQL Server, if you do not understand any of the below, please contact supportuk@transoftsolutions.com or call 03451 303040 and we can help talk you through it, or offer a live support session via a live support session.If you have moved KeyACCIDENT’s databases from one SQL instance to another, you will need to follow the below process to reset some SQL security items associated with the (old) database(s).

 

If you are not moving or restoring a KTSUsers database you can ignore the specific instructions relating to KTSUsers below.

 

At the end of the document are some examples where you can copy and paste the scripts straight into SQL Management studio (modifying appropriate aspects) if you desire.

  1. To start, treat the new instance as a clean install, setting KeyACCIDENT up with a blank database and getting it working, then moving on to step 2 to restore/move databases.
  2. Load Microsoft SQL Management studio
  3. Connect to the ‘new’ SQL instance as a user with SA permissions (or equivalent “sysadmin”)
  4. Attach the database files to the instance by using the ‘Attach Database’ command or, restore from a backup. (Right click on the “Databases” part of the tree and Attach or Restore can be found under the “Tasks” sub menu)
  5. Start a ‘New Query’
  6. Just to be safe, and to ensure that queries aren’t run on an incorrect database – change the ‘Selected available database’ to a KeyACCIDENT (or KTSUsers) database.

Insert Moving SQL Database Here

Note - ‘KTSUsers’ shown in image, this may need changing to the database you are wanting to query.

  1. Delete the KAUser (or the user that you setup as the KeyACCIDENT database admin user)

For KAUser:

USE [INSERT KEYACCIDENT DATABASE NAME HERE];

DROP USER [INSERT KEYACCIDENT SQL ADMIN USER (DEFAULT=KAUSER) HERE]

 

For KTSUsers: (optional if you are starting fresh)

USE [KTSUSERS];

DROP USER [KTSUSERS]

  1. Assign permissions to users:

For KeyACCIDENT Database

USE master; GRANT VIEW SERVER STATE TO [ INSERT KEYACCIDENT SQL ADMIN USER (DEFAULT=KAUSER) HERE];

USE [INSERT KEYACCIDENT DATABASE NAME HERE];

CREATE USER [INSERT KEYACCIDENT SQL ADMIN USER (DEFAULT=KAUSER) HERE] FOR LOGIN [INSERT KEYACCIDENT SQL ADMIN USER (DEFAULT=KAUSER) HERE];

USE [INSERT KEYACCIDENT DATABASE NAME HERE]; EXEC sp_addrolemember N'db_owner', N' ';

 

For KTSUsers: (optional if you want to start fresh anyway)

USE [KtsUSERS];

CREATE USER [KtsUSERS] FOR LOGIN [KtsUSERS];

USE [KtsUSERS];

EXEC sp_addrolemember N'db_owner', N'KtsUSERS';

  1. Now all the databases are set up in SQL server, you need to connect them into KeyACCIDENT
  2. Connect the databases under the “Tools” ribbon by selecting “Change Database”
  3. Click on “Connect to Existing” and enter the connection details of the databases you have moved/restored.

 

Summary

Run through KeyACCIDENT setup, achieve functional blank database Attach/Restore Database

Clean users out of database

USE [ ]; DROP USER [ ];

USE [KtsUSERS]; DROP USER [KtsUSERS];

Associate new users to restored database and set permissions

USE master; GRANT VIEW SERVER STATE TO [ ];

USE [ ]; CREATE USER [ ] FOR LOGIN [ ];

USE [ ]; EXEC sp_addrolemember N'db_owner', N' ';

Connect moved/restored databases to KeyACCIDENT