Answer

PROBLEM

When Archiver fails to create, attach or upgrade the schema of a Microsoft SQL Server based Archive Store the issue is most likely due to a query against the Microsoft SQL Server failing. Depending on the action being performed, you might see:

This error in the BulkSchemaUpgrader:
This error in the UI while going through the Archive Store wizard:

The debug log ..\Archiver\Store\DebugLogs\ManagementProviders.log lists further details:
 
Note that above is just an example. The actual error can differ.

ENVIRONMENT

• GFI Archiver
• Using Microsoft SQL Server as a database backend

SOLUTION

1. Review ..\GFI\Archiver\Store\DebugLogs\ManagementProviders.log and find the exact error
   • If the file does not exist or the lines at the end of the file are not showing a only old time stamps, run ..\GFI\Archiver\Enable Debug Logs.bat to enable the debug mode, then reproduce the issue once again
2. Address the error found in the previous step (the logged error normally indicates in clear words what the problem is)
3. Try the action again which previously failed (for example to upgrade the database schema by running ..\Archiver\BulkSchemaUpgrader\BulkSchemaUpgrader.exe)

Example #1
"error ","ManagementProviders","Error: (MSSql) UpgradeSchema. Rolling back 
;message: Invalid object name 'sys.columns'. 

or 

;message: Line 12: Incorrect syntax near '('. 

or

;message: Incorrect syntax near '@userHash'.

This error points towards these 2 scenarios:

a) A Microsoft SQL Server 2000 instance is being used which is not supported anymore by current versions of GFI Archiver. The Microsoft SQL Server instance would need to be upgraded to a supported version.

The version of the Microsoft SQL Server instance can be confirmed by running the following query against the instance using the Microsoft SQL Management Studio: SELECT @@VERSION

b) If the databases were hosted on Microsoft SQL Server 2000 in the past, but are currently running on a newer and supported version their compatibility level might still be set to 80 (which equals Microsoft SQL Server2000).

To identify the compatibility level of the databases:

1. Open the Microsoft SQL Management Studio
2. Connect to the instance of Microsoft SQL Server which holds the databases of GFI Archiver
3. Click on View > Object Explorer Details
4. Open the Databases object
5. A list of all databases is displayed including a column named compatibility level

The compatibility level of the database must be raised to a higher level (at least to level 90 which equals Microsoft SQL Server 2005).

To raise the compatibility level of a database:

1. Open the Microsoft SQL Management Studio
2. Connect to the instance of Microsoft SQL Server which holds the databases of GFI Archiver
3. Click on View > Object Explorer Details
4. Open the Databases object
5. Right click on a database and click on Properties
6. Under Select a page choose Options
7. Raise the Compatibility level to SQL Server 2005 (90) or higher

The following article explains how to view or change the compatibility level of a database: http://msdn.microsoft.com/en-gb/library/bb933794.aspx

Example #2
UpgradeSchema. Rolling back 
;message: Could not allocate space for object 'sys.syssoftobjrefs'.'nc1' in database '2012 Jan - Dec' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup

In this case the query failed as no further space was available, either on the disk itself, or because the database size limit was reached (Microsoft SQL Server Express) or because the size of the mdf- or ldf-files is limited within Microsoft SQL Server itself and the limit was reached.

Possible solutions:

• Increase disk space on the Microsoft SQL Server
• Remove autogrowth limits within the Microsoft SQL Server database

Example #3
Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10.FIRSTINSTANCE\MSSQL\DATA\2012 Jan - Dec.mdf' because it already exists. Change the file path or the file name, and retry the operation. 
;CREATE DATABASE failed. Some file names listed could not be created. Check related errors. 
;Database '2012 Jan - Dec' does not exist. Make sure that the name is entered correctly. 

In this case the mentioned mdf-file existed in the Microsoft SQL Server data path, but the database was not mounted within Microsoft SQL Server itself.

Possible solutions:

• Mount the database files in question as a database into the Microsoft SQL Server
• Move the mdf/ldf files out of the Microsoft SQL Server data folder

Example #4
"error ","ManagementProviders","Error: (MSSql) CreateDatabase(). Error execute: 
;message: MODIFY FILE encountered operating system error 112(failed to retrieve text for this error. Reason: 15105) while attempting to expand the physical file 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\2014 Apr.mdf'. 
;CREATE DATABASE failed. Some file names listed could not be created. Check related errors. 
;SqlException. Error No: 5149. Error Code: -2146232060. SeverityClass: 16

In this case the error indicates insufficient disk space on the drive on which the Microsoft SQL Server database is to be created or Microsoft SQL Server having insufficient access permissions to the folder in which the database is to be created.

Possible solutions:

• Increase disk space on the Microsoft SQL Server 
• Ensure that the account under which the Microsoft SQL Server service is running has sufficient NTFS permissions on the path in which the databases are to be created

Example #5
"error ","ManagementProviders","Error: (MSSql) CreateDatabase(). Error execute: 
;message: Could not obtain exclusive lock on database 'model'. Retry the operation later. 
;CREATE DATABASE failed. Some file names listed could not be created. Check related errors. 
;Could not locate entry in sysdatabases for database '2014 Oct - Dec'. No entry found with that name. Make sure that the name is entered correctly. 
;SqlException. Error No: 1807. Error Code: -2146232060. SeverityClass: 16 

This error indicates that another process has a lock on the Microsoft SQL Server's 'model' system database. Whenever Microsoft SQL Server creates a new database it uses the 'model' system database as a template requires an exclusive lock on it.

Possible solutions:

• Restart the Microsoft SQL Server database engine service
• If running Dell's AppAssure, it seems to be known to cause this situation: https://support.software.dell.com/kb/119742 
• Run the following query to identify which process has a lock on the 'model' database, then close this process (the hostname and program_name columns might be the best to look at): SELECT * FROM sys.sysprocesses WHERE DB_NAME(dbid)='model'

Example #6
"info ","ManagementProviders","(MSSql) Upgrading to version [x]. Number of steps to execute [y] >>" 
"info ","ManagementProviders","(MSSql) Step [1] Completed" 
"info ","ManagementProviders","(MSSql) Step [2] Completed" 
"info ","ManagementProviders","(MSSql) Step [3] Completed" 
...
"error ","ManagementProviders","Error: (MSSql) UpgradeSchema. Rolling back 
;message: Invalid operation. The connection is closed. 

This error indicates a possible corruption of the Microsoft SQL Server database.

Possible solutions:

• Check database integrity using DBCC CHECKDB 
• Restore a healthy backup of the database

Example #7
"error ","ManagementProviders","Error: (MSSql) CreateDatabase(). Error execute: 
;message: CREATE DATABASE permission denied in database 'master'. 
;SqlException. Error No: 262. Error Code: -2146232060. SeverityClass: 14

This error indicates that the SQL login (marc_admin) which GFI Archiver uses to create databases in Microsoft SQL Server does not have sufficient permissions.

Solution:

1. Open the SQL Management Studio
2. Connect to the SQL instance
3. Navigate to: \Security\Logins
4. Delete any login which begins with: marc_admin (do not delete any other login!)
5. Open the GFI Archiver web page
6. Navigate to Configuration > Archive Stores
7. Click on the New Archive Store Settings button
8. Go through the wizard
9. Ensure to use SQL authentication and authenticate with the SA account of the Microsoft SQL Server instance
10. Complete the wizard

CAUSE

An error occurred while trying to execute a query against the Microsoft SQL Server.