Migrating Sophos to another server

Question

Hello,

I'm in the process of migrating our Sophos installation from Windows 2003 to a Windows 2012 Server platform. All seem to have gone well until it I got to the point of restoring the database to the new server. To this end, I received a "RESTORE DATABASE is terminating abnormally" error.

Below is a copy of the command log for this process.

The old server had Sophos Enterprise Console 5.2.1 r2 installed; while the new one will have the Enterprise console 5.2.2.

Any ideas please?

Thanks in advance for your kind help.

=======================================

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>DataBackupRestor
e -Action=Restore -DataSourceType=Database
Are you sure you want to Restore Database in all? (Y/N)
y
Microsoft (R) Build Engine version 4.0.30319.18408
[Microsoft .NET Framework, version 4.0.30319.18449]
Copyright (C) Microsoft Corporation. All rights reserved.

Build started 28/11/2014 12:52:25.
Copy file C:\ProgramData\Sophos\ManagementServer\Backup\Databases\SOPHOSENC52.ba
k successful.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(l
ocal)\SOPHOS" -d "master" -b -Q "IF EXISTS (SELECT name FROM master.dbo.sysdatab
ases WHERE name = N'SOPHOSENC52') BEGIN ALTER DATABASE SOPHOSENC52 SET OFFLINE W
ITH ROLLBACK AFTER 5 END"
Attempting restore with SQL2012 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(l
ocal)\SOPHOS" -d "master" -b -Q "CREATE TABLE #backupSetInfo (BackupName NVARCHA
R(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate dateti
me,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128
),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,Databa
seCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN
numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),Backup
StartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallin
t,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,Soft
wareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersi
onBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,Recov
eryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,H
asBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChe
cksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOff
line INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeri
c(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),Differenti
alBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID un
iqueidentifier,CompressedBackupSize INT,containment tinyint); INSERT #backupSetI
nfo EXEC('RESTORE HEADERONLY FROM DISK=N' + '''C:\ProgramData\Sophos\TempData\SO
PHOSENC52.bak'''); DECLARE @backupsetnumber smallint; SET @backupsetnumber = (SE
LECT TOP(1) Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC); RESTOR
E DATABASE SOPHOSENC52 FROM DISK = 'C:\ProgramData\Sophos\TempData\SOPHOSENC52.b
ak' WITH FILE = @backupsetnumber, REPLACE, RECOVERY; DROP TABLE #backupSetInfo;"

Msg 213, Level 16, State 7, Server LYNDON-SOPHOS\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 3250, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2008/SQL2008R2 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(l
ocal)\SOPHOS" -d "master" -b -Q "CREATE TABLE #backupSetInfo (BackupName NVARCHA
R(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate dateti
me,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128
),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,Databa
seCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN
numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),Backup
StartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallin
t,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,Soft
wareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersi
onBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,Recov
eryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,H
asBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChe
cksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOff
line INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeri
c(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),Differenti
alBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID un
iqueidentifier,CompressedBackupSize INT); INSERT #backupSetInfo EXEC('RESTORE HE
ADERONLY FROM DISK=N' + '''C:\ProgramData\Sophos\TempData\SOPHOSENC52.bak'''); D
ECLARE @backupsetnumber smallint; SET @backupsetnumber = (SELECT TOP(1) Position
FROM #backupSetInfo ORDER BY BackupFinishDate DESC); RESTORE DATABASE SOPHOSENC
52 FROM DISK = 'C:\ProgramData\Sophos\TempData\SOPHOSENC52.bak' WITH FILE = @bac
kupsetnumber, REPLACE, RECOVERY; DROP TABLE #backupSetInfo;"

(1 rows affected)
Msg 5133, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SOP
HOS\MSSQL\DATA\SOPHOSENC52.mdf" failed with the operating system error 3(failed
to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Server LYNDON-SOPHOS\SOPHOS, Line 1
File 'SOPHOSENC51' cannot be restored to 'C:\Program Files\Microsoft SQL Server\
MSSQL10.SOPHOS\MSSQL\DATA\SOPHOSENC52.mdf'. Use WITH MOVE to identify a valid lo
cation for the file.
Msg 5133, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SOP
HOS\MSSQL\DATA\SOPHOSENC52.ldf" failed with the operating system error 3(failed
to retrieve text for this error. Reason: 15105).
Msg 3156, Level 16, State 3, Server LYNDON-SOPHOS\SOPHOS, Line 1
File 'SOPHOSENC51_log' cannot be restored to 'C:\Program Files\Microsoft SQL Ser
ver\MSSQL10.SOPHOS\MSSQL\DATA\SOPHOSENC52.ldf'. Use WITH MOVE to identify a vali
d location for the file.
Msg 3119, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
Problems were identified while planning for the RESTORE statement. Previous mess
ages provide details.
Msg 3013, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2005 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(l
ocal)\SOPHOS" -d "master" -b -Q "CREATE TABLE #backupSetInfo (BackupName NVARCHA
R(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate dateti
me,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128
),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,Databa
seCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN
numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),Backup
StartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallin
t,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,Soft
wareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersi
onBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,Recov
eryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,H
asBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChe
cksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOff
line INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeri
c(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),Differenti
alBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID un
iqueidentifier); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N' + '
''C:\ProgramData\Sophos\TempData\SOPHOSENC52.bak'''); DECLARE @backupsetnumber s
mallint; SET @backupsetnumber = (SELECT TOP(1) Position FROM #backupSetInfo ORDE
R BY BackupFinishDate DESC); RESTORE DATABASE SOPHOSENC52 FROM DISK = 'C:\Progra
mData\Sophos\TempData\SOPHOSENC52.bak' WITH FILE = @backupsetnumber, REPLACE, RE
COVERY; DROP TABLE #backupSetInfo;"
Msg 213, Level 16, State 7, Server LYNDON-SOPHOS\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 3250, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server LYNDON-SOPHOS\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Failed to restore

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(l
ocal)\SOPHOS" -d "master" -b -Q "ALTER DATABASE SOPHOSENC52 SET ONLINE"

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(l
ocal)\SOPHOS" -d "SOPHOSENC52" -b -i ResetUserMappings.sql
Process 'C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore\TRS.bat
(local)\SOPHOS SOPHOSENC52 "C:\ProgramData\Sophos\TempData\SOPHOSENC52.bak"' re
turned Error 1

Build FAILED.

Time Elapsed 00:00:01.16
Process 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\msbuild.exe "C:\Program
Data\Sophos\ManagementServer\Backup\DataBackupRestore"\BackupRestore.proj /t:Res
tore /clp:NoSummary /p:SubSystem=all;DataSourceType=Database;ExcludeDB=False;Loc
ationSpecific=False;SlientMode=False;DBServerInstance=' returned Error 1

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>

This thread was automatically locked due to age.

QC · Accepted Answer

Hello BritVic88, please have a look at the recent threads/posts by TheKiwiGeek and RBGE. Christian :54965

RBGE · Answer

Hi BritVic88, 
 To add to QC's post, you're making things a lot more difficult for yourself if you try to migrate from one version to another. I'd strongly advise you either upgrade the source server beforehand, or migrate to 5.2.1R2 on the new server and then upgrade to 5.2.2 after the migration process has completed. Seriously - this was one of the most stressful migrations I've ever been involved with, and you don't want to make it any more difficult than it already is :smileyindifferent:

Basically the official documentation doesn't work if Enterprise Console has ever been upgraded on the old server. TheKiwiGeek covered everything in this post: /search?q= 54667 but the part you're looking for at this stage is the second bullet point (and the third to get things back on track after) regarding database filesystem paths.

If you read my post you'll find a few other problems I mentioned, but I think they may have been specific to my situation. I'll update it shortly with solutions to all the minor issues I had anyway, just in case the same happens to you. :54966