Useful SQL commands to use with memoQ Server

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