Solution
The following method is best used if you have an audit history, saved cluster sites, and saved search results (Named Collision Collections) 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 who are comfortable administering Microsoft SQL Server.
If you do not understand any of the information below, please contact supportuk@transoftsolutions.com or call 03451 303040 and we can help talk you through it, or offer a live support session.
If you have moved KeyACCIDENT’s databases from one SQL instance to another, you will need to follow the process below 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).
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.
Note - ‘KtsUSERS’ shown in image - this may need changing to the database you are wanting to query.
7. 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]
8. 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’;
9. Now all the databases are set up in SQL server, you need to connect them into KeyACCIDENT
10. Connect the databases under the “Tools” ribbon by selecting “Change Database”
11. 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