If the SQL databases are in simple recovery

Backup

ALTER DATABASE dbname

SET READ_ONLY;

GO
 

Backup database dbname

to disk = 'C:\...\dbname.bak'

 
Restore


Restore database dbname

from disk =  'C:\...\dbname.bak'

with recovery, replace

 

ALTER DATABASE dbname

SET MULTI_USER;

GO

If the databases are in full recovery model

Backup

Backup database dbname

to disk = 'C:\...\dbname.bak'

Backup log dbname

to disk = 'C:\...\dbname.trn'

with norecovery

 
Restore

 
Restore database dbname

from disk = 'C:\...\dbname.bak'

with norecovery, replace

Restore log test2

from disk = 'C:\...\dbname.trn'

with recovery
 

• No Comments

Here are the steps to change the owner of Workspace/Folder in Worksite 10

–To change the owner of a Workspace/Folder, first we need to find the prj_id of the Workspace/Folder 

select * where prj_name LIKE ‘%FOLDER\WORKSPACE_NAME%’

–To change it to a different owner…

update mhgroup.projects set prj_owner = ‘NEW_USERID’ where prj_id = ‘WORKSPACE_ID’

After changing the owner of the workspace, the author in the docmaster table for this workspace needs to change as well. The owner is still shown as the old one, but after updating the author, the new owner should appear on the workspace profile.

Please run the following additional query:

– update mhgroup.docmaster set author =’new userID’ where docnum = ‘you can get this from the query below ‘

–also, to change the underlying Workspace/Folder document’s owner update mhgroup.docmaster set operator = ‘NEW_USERID’, author = ‘NEW_USERID’ where docnum = ‘DOCNUM’

Please test this against a backup copy of the database. Also make sure you backup the database before running this against production database.

To find the docnum of a workspace: select * from mhgroup.projects where prj_name = ‘Exact Name of Workspace’ and subtype = ‘work’

• No Comments

So you’ve found an issue in a Package in a Project for a DataSynchronization in a Integration Services Catalogs ( .dtsx file ) that you want to edit?

Install if its not already on it and open SQL Server Data Tools

Click on File and New Project

Choose this Project

Choose “Intergration Servies Catalog” and Open the project

You should see the .dstx files on the right under

• No Comments

Login to the Nable Administrator Report Manager ( http://%reportmanagerserver%/Interface/Pages/menu.aspx ) 

You will need to use the username : reportsadmin@n-able.com ( You can reset this password ) 

Navigate to the following

1. Select System Setup and Logs
2. Select Data Retention settings

Depending on your retention modify the below per here

Open SQL Server Management Studio make sure you have maintenance plan set up on the ODS Database

• Set the following in the Maintenance Plan Wizard of Microsoft SQL Server Management Studio:
•  Set the plan properties Name to Report Manager and Daily Run out of hours 
  
  2. Select the following maintenance tasks for the ODS databases and the warehouse database
• Check Database Integrity
• Reorganize Index
• Update Statistics

If you backup via another utility you can cancel the below : 

• Back Up Database (Full)
• Maintenance Cleanup Task

After the maintenance plan ran , we ran a Shrink on the database to reduce a 400GB Database file back down to 50GB for 3000 Devices

*SolarWinds MSP does not recommend shrinking your database.* however this was needed due to the ODS build up

• No Comments

https://sqlbolt.com/

https://regexone.com/

• No Comments

A Server 2012 R2 started getting VSS Writer Errors randomly in Veeam backup job. The server is running AADconnect which is set to autoupdate

A restart of the VSS SQL Writer which usually fixes this did not resolve the issue.

A look in the event log shows

A VSS writer has rejected an event with error 0x800423f4, The writer experienced a non-transient error. If the backup process is retried,
the error is likely to reoccur.
. Changes that the writer made to the writer components while handling the event will not be available to the requester. Check the event log for related events from the application hosting the VSS writer.

Operation:
PrepareForSnapshot Event

Context:
Execution Context: Writer
Writer Class Id: {a65faa63-5ea8-4ebc-9dbd-a0c4db26912a}
Writer Name: SqlServerWriter
Writer Instance Name: SQL Server Code-Named ‘Denali’ CTP2:SQLWriter
Writer Instance ID: {6c73bfe9-f82f-4854-bec4-4382c314a583}
Command Line: “C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe”
Process ID: 4292

With some

SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=4292. Thread=3940. Client. Instance=LOCALDB#SHDA41B2. VD=Global\{9C831400-DE4D-4364-BA22-A8299CF545FC}4_SQLVDIMemoryName_0.

Looks like you need to download the SQL Fix from here : https://support.microsoft.com/en-us/help/2983175/cumulative-update-package-2-for-sql-server-2012-sp2

Mirror Here : https://mega.nz/#!3AZ3gK7I!kDPhnAlur4XtslKxGXwmbnLitJiwN9R6rK-z4Rh0N-s

**Update , fix does not seem to work

A repair on the SQL database and then run the below script

Looks like this might be an issue with AADcoonect

https://github.com/pariswells/pariswells.com/blob/master/fixaadconnectvsswriter.ps1

**Update

 ADSync launches an SQL Server Local DB under it’s own user account
– The User Profile Service thinks ADSync is no longer logged on, and unloads the registry
– SQL Server though still has handles to the registry, but they’re invalid now

Detailed explanation:
https://support.microsoft.com/en-us/help/2287297/a-com-application-may-stop-working-on-windows-server-2008-when-a-user

In short: Computer Configuration->Administrative Templates->System->User Profiles->Do not forcefully unload the user registry at user logoff

• No Comments

For some reason I couldn’t delete credentials from Veeam due to them being used on a Shared Folder that did not existing in a backup repository or Tape to Folder. In the end I had to use SQL remove this.

1. Verify the record to be deleted by running the following query against the configuration database (VeeamBackup by default);
   
   SELECT * FROM [backup.model.mrulist]
2. Run the following query, changing <share path> to match the record to be deleted.
   
   DELETE FROM [backup.model.mrulist] WHERE url = ‘<share path>’

• No Comments

As there is no notification with the iManage Email Filing Server when folder status turns from Succeeded to reset the folder , we use the following SQL Statement to notify us via email when there are problems

Source code

$PSEmailServer = "smptserver"
 
##SQL Query Function no SQL installs needed all powershell baby
function Invoke-SQL ($SQLServer, $SqlQuery) {
#Uncomment below to double check the statement
#Write-Host $SqlQuery
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Integrated Security = True"
 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter.Fill($DataSet)
 
$SqlConnection.Close()
return $dataset.tables
}
 
$sqlresult = Invoke-SQL imanagesqlserver "select COUNT(1) from [imanagedatabase].[MHGROUP].EM_PROJECTS where ENABLED='Y' AND (STATUS=-1 or STATUS=-3)"
 
$DocGenCount = $sqlresult| select -expand Column1
 
if ($DocGenCount -gt 0 ) {
 
Send-MailMessage -From "email@domain.com.au" -to "email@domain.com" -Subject "EFS Problem Database Support" -Body "Current number of Issues $DocGenCount "
 
}

We recently got alerts through however logging into the Worksite Email Filing Server Management GUI there were no marked folders for resetting. This was due to the email address of the iManage user not matching the users filing email

• No Comments

It seems the iManage Inbox has difficulties filing items in a folder with a Custom24 Attribute attached to it. Here’s how to search imanage folders and list Workspaces/Folders with this attribute to remove where required.

Source code

SELECT PF.PRJ_NAME AS 'Folder Name', PWS.PRJ_NAME AS 'Workspace Name', PN.VALUE AS 'Cust 24 Value'
FROM MHGROUP.PROJECTS PF
JOIN MHGROUP.PROJECT_NVPS PN ON PF.PRJ_ID = PN.PRJ_ID
JOIN MHGROUP.PROJECTS PWS ON PF.TREE_ID = PWS.PRJ_ID AND PWS.PRJ_ID <> PF.PRJ_ID 
JOIN MHGROUP.DOCMASTER DWS ON DWS.DOCNUM = PWS.DOCNUM
WHERE PN.NAME = 'iMan___48'

• No Comments

Worksite/Imanage Monitor can export a document list based on History using the History search but it’s limited in the way it can display comments as well as duplicate Document Numbers. Below is SQL of how to get this direct in SSSM

Change the WorksiteDB to your DB and APPName to your application

Source code

SELECT DISTINCT [WORKSITEDB].[MHGROUP].[DOCHISTORY].[DOCNUM]
     ,[WORKSITEDB].[MHGROUP].[DOCHISTORY].[VERSION]
	 ,[WORKSITEDB].[MHGROUP].[DOCMASTER].DOCNAME
	 ,[WORKSITEDB].[MHGROUP].[DOCMASTER].T_ALIAS
      ,[ACTIVITY]
      ,[ACTIVITY_CODE]
      ,[ACTIVITY_DATETIME]
      ,[DURATION]
      ,[DOCUSER]
      ,[APPNAME]
      ,[LOCATION]
	  , CAST([WORKSITEDB].[MHGROUP].[DOCHISTORY].[COMMENTS] AS NVARCHAR(MAX))
  FROM [WORKSITEDB].[MHGROUP].[DOCHISTORY] JOIN [WORKSITEDB].[MHGROUP].[DOCMASTER] ON [WORKSITEDB].[MHGROUP].[DOCHISTORY].[DOCNUM]=[WORKSITEDB].[MHGROUP].[DOCMASTER].[DOCNUM] WHERE (APPNAME = 'APPLICATIONNAME') AND (ACTIVITY = 'Checkin')

• No Comments