Useful SQL commands to use with memoQ Server

These commands can help you resolve issues with the SQL Server database that runs under memoQ server.

To use these commands, open the sqlcmd program. This is the SQL Server command line, which will be on the server computer if SQL Server is installed.

If SQL Server is on a different computer than memoQ server: It is recommended that you log on to the SQL server (rather than to the memoQ server) computer through Remote Desktop, and run the commands there.

Do not make changes to your system unless you know exactly what you are doing, and everything written here is clear to you.

Use these commands entirely at your own risk: memoQ Zrt. takes no responsibility for damages that occur when you use these commands. They are extremely dangerous in inexperienced hands. Also make sure to back up the database before you make any changes. It is also highly recommended that you take at least a crash course in SQL Server administration before you proceed.

sqlcmd –S .\<instancename>

Example: sqlcmd –S .\MEMOQSERVER

To connect to a default instance (which goes by the name MSSQLSERVER in the Services program), use this command: sqlcmd -S .\

Note: If you need to use a default instance on the same computer with memoQ server, change the connection string in the C:\ProgramData\MemoQ Server\Configuration.xml file. The connection string should be this: <SQLConnectionString>Data Source=localhost;Initial Catalog=MemoQServer;Integrated Security=True;Connect Timeout=300</SQLConnectionString>

Normally, with the MEMOQSERVER instance, the connection string is this:

<SQLConnectionString>Data Source=localhost\MEMOQSERVER;Initial Catalog=MemoQServer;Integrated Security=True;Connect Timeout=300</SQLConnectionString>)

Initial Catalog is the name of the database within SQL Server that memoQ server will use. Normally, it is MemoQServer.

You need this when you must restore a memoQ server database backup. Use this command to find out if you will have difficulties (and need to upgrade) because the database backup comes from a different version.

select @@version

go

The output looks like this:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (Intel X86)

       Mar 26 2015 21:49:16

       Copyright (c) Microsoft Corporation

       Express Edition on Windows NT 6.2 <X64> (Build 9200: ) (WOW64)

Connect to the SQL Server instance first. To learn how, read the section at the top of this topic.

This is highly recommended before you move the MemoQServer.mdf and MemoQServer_log.ldf files to a different folder, so that the database remains consistent after the move.

use master [press Enter]

go [press Enter]

sp_detach_db ’<databasename>’ [press Enter]

go [press Enter]

Example:

use master [press Enter]

go [press Enter]

sp_detach_db ’MemoQServer’ [press Enter]

go [press Enter]

(Press Enter after each line.)

use master  

go

sp_attach_db '<database name>','mdf path', 'ldf path'

go

Example_

use master

go

sp_attach_db ’MemoQServer’, ’C:\ProgramData\MemoQ Server\MemoQServer.mdf’, ’C:\ProgramData\MemoQ Server\MemoQServer_log.ldf’

go

You need to grant this role for the user account that represents memoQ server as well as the user who is running the Deployment tool.

Do this as someone who has the sysadmin role on the SQL Server.

use  <database name>

go

sp_addrolemember ’db_owner’, ’<computername or domain name\username>’

go

Example:

use MemoQServer

go

sp_addrolemember ’db_owner’, ’memoQ-PC\memoQ’

go

To remove someone from this role:

use  <database name>

go

sp_droprolemember ’db_owner’, ’<computername or domain name\username>’

go

Example:

use MemoQServer

go

sp_droprolemember ’db_owner’, ’memoQ-PC\memoQ’

go

You need to grant this role for the user account that represents memoQ server as well as the user who is running the Deployment tool.

Do this as someone who already has the sysadmin role on the SQL Server.

sp_addsrvrolemember ’<computername or domain name\username>’, ’role’

go

Example:

sp_addsrvrolemember ’memoQ-PC\memoQ’, ’sysadmin’

go

or

sp_addsrvrolemember ’memoQ-PC\memoQ’, ’dbcreator’

go

To remove someone from a server-level role:

Example:

sp_dropsrvrolemember ’memoQ-PC\memoQ’, ’sysadmin’

go

or

sp_dropsrvrolemember ’memoQ-PC\memoQ’, ’dbcreator’

go

To list users with the sysadmin role:

sp_helpsrvrolemember 'sysadmin'

go

Connect to a server instance first.

select name from sys.databases

go

select physical_name from sys.master_files where database_id = (select database_id from sys.databases where name = ’MemoQServer’)

go

Got nothing in return? You probably do not have sufficient privileges to run this command.

Method 1:

  1. Stop the memoQ server service.
  2. Stop the MS SQL database instance (use Services.msc for that).
  3. From the memoQ server data folder (normally C:\ProgramData\MemoQ Server), copy the MemoQServer.mdf and MemoQServer_log.ldf files to your backup folder.

    Method 2:

  4. Stop the memoQ server service.
  5. Connect to the database instance.
  6. Run the following command to make a full backup:

    backup database <database name> to disk=’<path>\<backup filename>.bak’

go

Example:

backup database MemoQServer to disk=’E:\mqdb_backup\MemoQServer_2012_04_18.bak’

go

Normally, the procedure is the following:

  1. Stop the memoQ server service.
  2. Connect to the database instance.
  3. Run the following command:

    restore database <database name> from disk=’<path>\<backup filename>.bak’

go

Example:

restore database MemoQServer from disk=’E:\mqdb_backup\MemoQServer_2012_04_18.bak’

go

If the logical name and path of the database logical name was different in the system where the backup is coming from, and you need to restore the backup on a freshly installed memoQ server, use this command:

restore database MemoQServer from disk = 'C:\backup_2016_08_26\MemoQServer_2016_08_26.bak' with replace, move 'name_on_cloud_server' to 'C:\testdb\MemoQServer.mdf', move 'name_on_cloud_server_log' to 'C:\testdb\MemoQServer_log.ldf'

go