Thread Info
  • State Verified Answer
  • Locked Locked
  • Replies 18 replies
  • Subscribers 2 subscribers
  • Views 5853 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Restore failure when migrating databases to new server - Following migration Guide for Sophos Enterprise Console

Cashfac IT

Hi All,

 

Receiving a failure when running the databackuprestore -action=restore -datasourcetype=database command from the Command prompt on our new server.

See log below

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>databackuprestore -action=restore -datasourcetype=database
Are you sure you want to restore database in all? (Y/N)
y
Microsoft (R) Build Engine version 4.7.3190.0
[Microsoft .NET Framework, version 4.0.30319.42000]
Copyright (C) Microsoft Corporation. All rights reserved.

Build started 10/01/2020 10:28:31.
Copy file C:\ProgramData\Sophos\ManagementServer\Backup\Databases\SOPHOSPATCH52.bak successful.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'SOPHOSPATCH52') BEGIN ALTER DATABASE SOPHOSPATCH52 SET OFFLINE WITH ROLLBACK AFTER 5 END"
Attempting restore with SQL2016 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "DECLARE @backup_path NVARCHAR(260); SET @backup_path = N'C:\ProgramData\Sophos\TempData\SOPHOSPATCH52.bak'; DECLARE @mdf_path varchar(260); SELECT @mdf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOSPATCH52') AND type = 0; DECLARE @ldf_path varchar(260); SELECT @ldf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOSPATCH52') AND type = 1; CREATE TABLE #backupSetInfo (BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier,CompressedBackupSize bigint,containment tinyint, KeyAlgorithm nvarchar(32), EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32)); CREATE TABLE #backupFileInfo (LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit ,TDEThumbprint varbinary(32),SnapshotURL nvarchar(360)); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N''' + @backup_path + N''''); INSERT #backupFileInfo EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @backup_path + N''''); DECLARE @backup_set_number smallint; SELECT TOP(1) @backup_set_number = Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC; DECLARE @mdf_name varchar(255); SELECT TOP(1) @mdf_name = LogicalName from #backupFileInfo where Type = 'D'; DECLARE @ldf_name varchar(255); SELECT TOP(1) @ldf_name = LogicalName from #backupFileInfo where Type = 'L'; IF @mdf_path IS NOT NULL BEGIN RESTORE DATABASE SOPHOSPATCH52 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY, MOVE @mdf_name TO @mdf_path, MOVE @ldf_name TO @ldf_path; END ELSE BEGIN RESTORE DATABASE SOPHOSPATCH52 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY; END; DROP TABLE #backupFileInfo; DROP TABLE #backupSetInfo;"
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE FILELIST is terminating abnormally.
Msg 8114, Level 16, State 12, Server COR01IILI1APP01\SOPHOS, Line 1
Error converting data type varchar to nvarchar.
Attempting restore with SQL2014 SP1 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "DECLARE @backup_path NVARCHAR(260); SET @backup_path = N'C:\ProgramData\Sophos\TempData\SOPHOSPATCH52.bak'; DECLARE @mdf_path varchar(260); SELECT @mdf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOSPATCH52') AND type = 0; DECLARE @ldf_path varchar(260); SELECT @ldf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOSPATCH52') AND type = 1; CREATE TABLE #backupSetInfo (BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier,CompressedBackupSize bigint,containment tinyint, KeyAlgorithm nvarchar(32), EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32)); CREATE TABLE #backupFileInfo (LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit ,TDEThumbprint varbinary(32)); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N''' + @backup_path + N''''); INSERT #backupFileInfo EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @backup_path + N''''); DECLARE @backup_set_number smallint; SELECT TOP(1) @backup_set_number = Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC; DECLARE @mdf_name varchar(255); SELECT TOP(1) @mdf_name = LogicalName from #backupFileInfo where Type = 'D'; DECLARE @ldf_name varchar(255); SELECT TOP(1) @ldf_name = LogicalName from #backupFileInfo where Type = 'L'; IF @mdf_path IS NOT NULL BEGIN RESTORE DATABASE SOPHOSPATCH52 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY, MOVE @mdf_name TO @mdf_path, MOVE @ldf_name TO @ldf_path; END ELSE BEGIN RESTORE DATABASE SOPHOSPATCH52 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY; END; DROP TABLE #backupFileInfo; DROP TABLE #backupSetInfo;"
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.

(2 rows affected)
Msg 3250, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2012/SQL2014 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "DECLARE @backup_path NVARCHAR(260); SET @backup_path = N'C:\ProgramData\Sophos\TempData\SOPHOSPATCH52.bak'; DECLARE @mdf_path varchar(260); SELECT @mdf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOSPATCH52') AND type = 0; DECLARE @ldf_path varchar(260); SELECT @ldf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOSPATCH52') AND type = 1; CREATE TABLE #backupSetInfo (BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier,CompressedBackupSize bigint,containment tinyint); CREATE TABLE #backupFileInfo (LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit ,TDEThumbprint varbinary(32)); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N''' + @backup_path + N''''); INSERT #backupFileInfo EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @backup_path + N''''); DECLARE @backup_set_number smallint; SELECT TOP(1) @backup_set_number = Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC; DECLARE @mdf_name varchar(255); SELECT TOP(1) @mdf_name = LogicalName from #backupFileInfo where Type = 'D'; DECLARE @ldf_name varchar(255); SELECT TOP(1) @ldf_name = LogicalName from #backupFileInfo where Type = 'L'; IF @mdf_path IS NOT NULL BEGIN RESTORE DATABASE SOPHOSPATCH52 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY, MOVE @mdf_name TO @mdf_path, MOVE @ldf_name TO @ldf_path; END ELSE BEGIN RESTORE DATABASE SOPHOSPATCH52 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY; END; DROP TABLE #backupFileInfo; DROP TABLE #backupSetInfo;"

(1 rows affected)

(2 rows affected)
Processed 309096 pages for database 'SOPHOSPATCH52', file 'SOPHOSPATCH52' on file 1.
Processed 1 pages for database 'SOPHOSPATCH52', file 'SOPHOSPATCH52_log' on file 1.
Converting database 'SOPHOSPATCH52' from version 655 to the current version 706.
Database 'SOPHOSPATCH52' running the upgrade step from version 655 to version 668.
Database 'SOPHOSPATCH52' running the upgrade step from version 668 to version 669.
Database 'SOPHOSPATCH52' running the upgrade step from version 669 to version 670.
Database 'SOPHOSPATCH52' running the upgrade step from version 670 to version 671.
Database 'SOPHOSPATCH52' running the upgrade step from version 671 to version 672.
Database 'SOPHOSPATCH52' running the upgrade step from version 672 to version 673.
Database 'SOPHOSPATCH52' running the upgrade step from version 673 to version 674.
Database 'SOPHOSPATCH52' running the upgrade step from version 674 to version 675.
Database 'SOPHOSPATCH52' running the upgrade step from version 675 to version 676.
Database 'SOPHOSPATCH52' running the upgrade step from version 676 to version 677.
Database 'SOPHOSPATCH52' running the upgrade step from version 677 to version 679.
Database 'SOPHOSPATCH52' running the upgrade step from version 679 to version 680.
Database 'SOPHOSPATCH52' running the upgrade step from version 680 to version 681.
Database 'SOPHOSPATCH52' running the upgrade step from version 681 to version 682.
Database 'SOPHOSPATCH52' running the upgrade step from version 682 to version 683.
Database 'SOPHOSPATCH52' running the upgrade step from version 683 to version 684.
Database 'SOPHOSPATCH52' running the upgrade step from version 684 to version 685.
Database 'SOPHOSPATCH52' running the upgrade step from version 685 to version 686.
Database 'SOPHOSPATCH52' running the upgrade step from version 686 to version 687.
Database 'SOPHOSPATCH52' running the upgrade step from version 687 to version 688.
Database 'SOPHOSPATCH52' running the upgrade step from version 688 to version 689.
Database 'SOPHOSPATCH52' running the upgrade step from version 689 to version 690.
Database 'SOPHOSPATCH52' running the upgrade step from version 690 to version 691.
Database 'SOPHOSPATCH52' running the upgrade step from version 691 to version 692.
Database 'SOPHOSPATCH52' running the upgrade step from version 692 to version 693.
Database 'SOPHOSPATCH52' running the upgrade step from version 693 to version 694.
Database 'SOPHOSPATCH52' running the upgrade step from version 694 to version 695.
Database 'SOPHOSPATCH52' running the upgrade step from version 695 to version 696.
Database 'SOPHOSPATCH52' running the upgrade step from version 696 to version 697.
Database 'SOPHOSPATCH52' running the upgrade step from version 697 to version 698.
Database 'SOPHOSPATCH52' running the upgrade step from version 698 to version 699.
Database 'SOPHOSPATCH52' running the upgrade step from version 699 to version 700.
Database 'SOPHOSPATCH52' running the upgrade step from version 700 to version 701.
Database 'SOPHOSPATCH52' running the upgrade step from version 701 to version 702.
Database 'SOPHOSPATCH52' running the upgrade step from version 702 to version 703.
Database 'SOPHOSPATCH52' running the upgrade step from version 703 to version 704.
Database 'SOPHOSPATCH52' running the upgrade step from version 704 to version 705.
Database 'SOPHOSPATCH52' running the upgrade step from version 705 to version 706.
RESTORE DATABASE successfully processed 309097 pages in 7.748 seconds (311.669 MB/sec).

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

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "SOPHOSPATCH52" -b -i ResetUserMappings.sql
Copy file C:\ProgramData\Sophos\ManagementServer\Backup\Databases\SOPHOS550.bak successful.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'SOPHOS550') BEGIN ALTER DATABASE SOPHOS550 SET OFFLINE WITH ROLLBACK AFTER 5 END"
Attempting restore with SQL2016 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "DECLARE @backup_path NVARCHAR(260); SET @backup_path = N'C:\ProgramData\Sophos\TempData\SOPHOS550.bak'; DECLARE @mdf_path varchar(260); SELECT @mdf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 0; DECLARE @ldf_path varchar(260); SELECT @ldf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 1; CREATE TABLE #backupSetInfo (BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier,CompressedBackupSize bigint,containment tinyint, KeyAlgorithm nvarchar(32), EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32)); CREATE TABLE #backupFileInfo (LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit ,TDEThumbprint varbinary(32),SnapshotURL nvarchar(360)); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N''' + @backup_path + N''''); INSERT #backupFileInfo EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @backup_path + N''''); DECLARE @backup_set_number smallint; SELECT TOP(1) @backup_set_number = Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC; DECLARE @mdf_name varchar(255); SELECT TOP(1) @mdf_name = LogicalName from #backupFileInfo where Type = 'D'; DECLARE @ldf_name varchar(255); SELECT TOP(1) @ldf_name = LogicalName from #backupFileInfo where Type = 'L'; IF @mdf_path IS NOT NULL BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY, MOVE @mdf_name TO @mdf_path, MOVE @ldf_name TO @ldf_path; END ELSE BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY; END; DROP TABLE #backupFileInfo; DROP TABLE #backupSetInfo;"
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE FILELIST is terminating abnormally.
Msg 3250, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2014 SP1 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "DECLARE @backup_path NVARCHAR(260); SET @backup_path = N'C:\ProgramData\Sophos\TempData\SOPHOS550.bak'; DECLARE @mdf_path varchar(260); SELECT @mdf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 0; DECLARE @ldf_path varchar(260); SELECT @ldf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 1; CREATE TABLE #backupSetInfo (BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier,CompressedBackupSize bigint,containment tinyint, KeyAlgorithm nvarchar(32), EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32)); CREATE TABLE #backupFileInfo (LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit ,TDEThumbprint varbinary(32)); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N''' + @backup_path + N''''); INSERT #backupFileInfo EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @backup_path + N''''); DECLARE @backup_set_number smallint; SELECT TOP(1) @backup_set_number = Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC; DECLARE @mdf_name varchar(255); SELECT TOP(1) @mdf_name = LogicalName from #backupFileInfo where Type = 'D'; DECLARE @ldf_name varchar(255); SELECT TOP(1) @ldf_name = LogicalName from #backupFileInfo where Type = 'L'; IF @mdf_path IS NOT NULL BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY, MOVE @mdf_name TO @mdf_path, MOVE @ldf_name TO @ldf_path; END ELSE BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY; END; DROP TABLE #backupFileInfo; DROP TABLE #backupSetInfo;"
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.

(2 rows affected)
Msg 3250, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2012/SQL2014 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "DECLARE @backup_path NVARCHAR(260); SET @backup_path = N'C:\ProgramData\Sophos\TempData\SOPHOS550.bak'; DECLARE @mdf_path varchar(260); SELECT @mdf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 0; DECLARE @ldf_path varchar(260); SELECT @ldf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 1; CREATE TABLE #backupSetInfo (BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier,CompressedBackupSize bigint,containment tinyint); CREATE TABLE #backupFileInfo (LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit ,TDEThumbprint varbinary(32)); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N''' + @backup_path + N''''); INSERT #backupFileInfo EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @backup_path + N''''); DECLARE @backup_set_number smallint; SELECT TOP(1) @backup_set_number = Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC; DECLARE @mdf_name varchar(255); SELECT TOP(1) @mdf_name = LogicalName from #backupFileInfo where Type = 'D'; DECLARE @ldf_name varchar(255); SELECT TOP(1) @ldf_name = LogicalName from #backupFileInfo where Type = 'L'; IF @mdf_path IS NOT NULL BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY, MOVE @mdf_name TO @mdf_path, MOVE @ldf_name TO @ldf_path; END ELSE BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY; END; DROP TABLE #backupFileInfo; DROP TABLE #backupSetInfo;"

(1 rows affected)

(2 rows affected)
Msg 5133, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
Directory lookup for the file "C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SOPHOS\MSSQL\DATA\SOPHOS550.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Server COR01IILI1APP01\SOPHOS, Line 1
File 'SOPHOS550' cannot be restored to 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SOPHOS\MSSQL\DATA\SOPHOS550.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
Directory lookup for the file "C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SOPHOS\MSSQL\DATA\SOPHOS550_log.LDF" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Server COR01IILI1APP01\SOPHOS, Line 1
File 'SOPHOS550_log' cannot be restored to 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SOPHOS\MSSQL\DATA\SOPHOS550_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2008/SQL2008R2 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "DECLARE @backup_path NVARCHAR(260); SET @backup_path = N'C:\ProgramData\Sophos\TempData\SOPHOS550.bak'; DECLARE @mdf_path varchar(260); SELECT @mdf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 0; DECLARE @ldf_path varchar(260); SELECT @ldf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 1; CREATE TABLE #backupSetInfo (BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier,CompressedBackupSize bigint); CREATE TABLE #backupFileInfo (LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit ,TDEThumbprint varbinary(32)); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N''' + @backup_path + N''''); INSERT #backupFileInfo EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @backup_path + N''''); DECLARE @backup_set_number smallint; SELECT TOP(1) @backup_set_number = Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC; DECLARE @mdf_name varchar(255); SELECT TOP(1) @mdf_name = LogicalName from #backupFileInfo where Type = 'D'; DECLARE @ldf_name varchar(255); SELECT TOP(1) @ldf_name = LogicalName from #backupFileInfo where Type = 'L'; IF @mdf_path IS NOT NULL BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY, MOVE @mdf_name TO @mdf_path, MOVE @ldf_name TO @ldf_path; END ELSE BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY; END; DROP TABLE #backupFileInfo; DROP TABLE #backupSetInfo;"
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.

(2 rows affected)
Msg 3250, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2005 backup schema.

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "DECLARE @backup_path NVARCHAR(260); SET @backup_path = N'C:\ProgramData\Sophos\TempData\SOPHOS550.bak'; DECLARE @mdf_path varchar(260); SELECT @mdf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 0; DECLARE @ldf_path varchar(260); SELECT @ldf_path = physical_name FROM sys.master_files WHERE database_id = DB_ID(N'SOPHOS550') AND type = 1; CREATE TABLE #backupSetInfo (BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier); CREATE TABLE #backupFileInfo (LogicalName varchar(128),PhysicalName varchar(260),Type varchar(1),FileGroupName varchar(128) ,Size numeric(20,0) ,MaxSize numeric(20,0),FileId bigint,CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier,DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,IsReadOnly bit,IsPresent bit ); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N''' + @backup_path + N''''); INSERT #backupFileInfo EXEC('RESTORE FILELISTONLY FROM DISK=N''' + @backup_path + N''''); DECLARE @backup_set_number smallint; SELECT TOP(1) @backup_set_number = Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC; DECLARE @mdf_name varchar(255); SELECT TOP(1) @mdf_name = LogicalName from #backupFileInfo where Type = 'D'; DECLARE @ldf_name varchar(255); SELECT TOP(1) @ldf_name = LogicalName from #backupFileInfo where Type = 'L'; IF @mdf_path IS NOT NULL BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY, MOVE @mdf_name TO @mdf_path, MOVE @ldf_name TO @ldf_path; END ELSE BEGIN RESTORE DATABASE SOPHOS550 FROM DISK = @backup_path WITH FILE = @backup_set_number, REPLACE, RECOVERY; END; DROP TABLE #backupFileInfo; DROP TABLE #backupSetInfo;"
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 213, Level 16, State 7, Server COR01IILI1APP01\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE FILELIST is terminating abnormally.
Msg 3250, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Failed to restore

C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S "(local)\SOPHOS" -d "master" -b -Q "ALTER DATABASE SOPHOS550 SET ONLINE"
Msg 5011, Level 14, State 5, Server COR01IILI1APP01\SOPHOS, Line 1
User does not have permission to alter database 'SOPHOS550', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Server COR01IILI1APP01\SOPHOS, Line 1
ALTER DATABASE statement failed.

Failed

Process 'C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore\TRS.bat (local)\SOPHOS SOPHOS550 "C:\ProgramData\Sophos\TempData\SOPHOS550.bak"' returned Error 1

Build FAILED.

Time Elapsed 00:00:17.84
Process 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\msbuild.exe "C:\ProgramData\Sophos\ManagementServer\Backup\DataBackupRestore"\BackupRestore.proj /t:restore /clp:NoSummary /p:SubSystem=all;DataSourceType=database;ExcludeDB=False;LocationSpecific=False;SlientMode=False;DBServerInstance=' returned Error 1

 

Doen't make any sense to me, anyone know what we are doing wrong?

 

Thanks in advance

 

Kind Regards

Stephen Akers



This thread was automatically locked due to age.
Parents
  • +1

    Hello Stephen Akers,

    it's late Friday (at least for me) so I wonder why it worked for the Patch database but doesn't for 550.

    Anyway it fails because of
    Directory lookup for the file "C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SOPHOS\MSSQL\DATA\SOPHOS550.mdf" failed with the operating system error 3(The system cannot find the path specified.).

    Did it really reside on this path on the old server (and where was the Patch database)? A WITH MOVE to the correct path on the new server should help.

    Christian

  • 0 in reply to QC

    Hi Christian,

     

    We are moving from SQL Server 2008 to 2014 so the path is different.  Instead of being MSSQL10 it is MSSQL12.

    How do we change the command so we can use the WITH MOVE option to do the restore?

    The database noted on your reply does exist there, it just appears that we are unable to move it.

     

    Any help appreciated


    Kind Regards

    Stephen

  • +1 in reply to Cashfac IT

    Hello Stephen,

    did you read the linked thread, the change should be applied in the 2012/2014 section of the RestoreDB script.

    Christian

     

     

     

  • 0 in reply to QC

    Apologies Christian

     

    Is the script ran on the old server or the new server?

  • +1 in reply to Cashfac IT

    Hello Stephen,

    sorry, weekend gap. In case you didn't figure out yet - it's the restore and naturally it has to be run on the new server. By default the SQL RESTORE DATABASE restores to the data path the backup was taken from. This is what one normally wants. This can be overridden by using WITH MOVE.

    Christian

  • 0 in reply to QC

    Hi CHristain,

     

    Apologies, where in this script am I entering this "MOVE %databaseName% TO X:\new\path\%databaseName%.mdf, MOVE %databaseName%_log TO X:\new\path\%databaseName%.ldf" ?

     

    I am unsure as to where this needs to go in this script

     

    @SETLOCAL

    @SET RETURN_ERRORLEVEL=0

    @REM *****************************
    @REM Parse command-line parameters
    @REM *****************************

    @IF {%1} == {} GOTO PrintHelp
    @IF {%1} == {-?} GOTO PrintHelp
    @IF {%1} == {/?} GOTO PrintHelp
    @IF {%1} == {?} GOTO PrintHelp

    @FOR %%I IN ( %1 ) DO SET backupFileName=%%~fI

    @SET instanceName=%2
    @IF "%2" == "" SET instanceName=%COMPUTERNAME%\SOPHOS

    @SET databaseName=%3
    @IF "%3" == "" SET databaseName=SOPHOS540

    @REM ***************************************************************
    @REM Disable access to the database to cut out any connected clients
    @REM ***************************************************************
    @ECHO.
    @ECHO Disabling access to the database ...
    sqlcmd -E -S "%instanceName%" -d "master" -b -Q "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'%databaseName%') BEGIN ALTER DATABASE %databaseName% SET OFFLINE WITH ROLLBACK AFTER 5 END"
    @IF ERRORLEVEL 1 GOTO Error

    @REM ***********************
    @REM Execute restore command
    @REM ***********************
    @SET backupSetColumns=BackupName NVARCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,DatabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),BackupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage smallint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareVersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,RecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifier,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBackupChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForceOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN numeric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUID uniqueidentifier

    @ECHO.
    @ECHO Attempting restore with SQL2016 backup schema.
    @CALL :RestoreDB "%backupSetColumns%,CompressedBackupSize bigint,containment tinyint, KeyAlgorithm nvarchar(32), EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32)" "%backupFileColumns%,TDEThumbprint varbinary(32),SnapshotURL nvarchar(360)"
    @IF NOT ERRORLEVEL 1 GOTO EnableDB

    @ECHO.
    @ECHO Attempting restore with SQL2014 SP1 backup schema.
    @CALL :RestoreDB "%backupSetColumns%,CompressedBackupSize bigint,containment tinyint, KeyAlgorithm nvarchar(32), EncryptorThumbprint varbinary(20), EncryptorType nvarchar(32)" "%backupFileColumns%,TDEThumbprint varbinary(32)"
    @IF NOT ERRORLEVEL 1 GOTO EnableDB

    @ECHO.
    @ECHO Attempting restore with SQL2012/SQL2014 backup schema.
    @CALL :RestoreDB "%backupSetColumns%,CompressedBackupSize bigint,containment tinyint"
    @IF NOT ERRORLEVEL 1 GOTO EnableDB

    @ECHO.
    @ECHO Attempting restore with SQL2008/SQL2008R2 backup schema.
    @CALL :RestoreDB "%backupSetColumns%,CompressedBackupSize bigint"
    @IF NOT ERRORLEVEL 1 GOTO EnableDB

    @ECHO.
    @ECHO Attempting restore with SQL2005 backup schema.
    @CALL :RestoreDB "%backupSetColumns%"
    @IF NOT ERRORLEVEL 1 GOTO EnableDB

    :RestoreFailed
    @REM IF THE RESTORE FAILS THEN THIS IS THE MOST IMPORTANT ERROR CODE TO RETURN.
    @SET RETURN_ERRORLEVEL=%ERRORLEVEL%
    @ECHO Failed to restore

    :EnableDB
    @REM ******************************
    @REM Restore access to the database
    @REM ******************************
    @ECHO.
    @ECHO Enabling access to the database ...
    sqlcmd -E -S "%instanceName%" -d "master" -b -Q "ALTER DATABASE %databaseName% SET ONLINE"
    @IF ERRORLEVEL 1 GOTO Error

    :End
    @REM ITS IMPORTANT TO RETURN %RETURN_ERRORLEVEL% ON THE SAME LINE AS ENDLOCAL, AS ENDLOCAL WILL RESET ALL ENVIRONMENT VARIABLES ON THE NEXT STATEMENT.
    @ENDLOCAL && EXIT /B %RETURN_ERRORLEVEL%

    :PrintHelp
    @ECHO.
    @ECHO Usage:
    @ECHO RestoreDB backup_file_path [server_name\instance_name] [database_name]
    @ECHO.
    @GOTO End

    :Error
    @ECHO.
    @ECHO Failed
    @ECHO.
    @IF %RETURN_ERRORLEVEL% EQU 0 SET RETURN_ERRORLEVEL=%ERRORLEVEL%
    @GOTO End

    :RestoreDB
    sqlcmd -E -S "%instanceName%" -d "master" -b -Q "CREATE TABLE #backupSetInfo (%~1); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N' + '''%backupFileName%'''); DECLARE @backupsetnumber smallint; SET @backupsetnumber = (SELECT TOP(1) Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC); RESTORE DATABASE %databaseName% FROM DISK = '%backupFileName%' WITH FILE = @backupsetnumber, REPLACE, RECOVERY; DROP TABLE #backupSetInfo;"

     

    Kind Regards

    Stephen

  • +1 in reply to Cashfac IT

    Hello Stephen,

    should be at the end of the script:
    :RestoreDB
    sqlcmd -E -S "%instanceName%" -d "master" -b -Q "CREATE TABLE #backupSetInfo (%~1); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N' + '''%backupFileName%'''); DECLARE @backupsetnumber smallint; SET @backupsetnumber = (SELECT TOP(1) Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC); RESTORE DATABASE %databaseName% FROM DISK = '%backupFileName%' WITH FILE = @backupsetnumber, , MOVE '%databaseName%' TO 'C:\new\path\%databaseName%.mdf', MOVE '%databaseName%_log' TO 'C:\new\path\%databaseName%_log.ldf', REPLACE, RECOVERY; DROP TABLE #backupSetInfo;"

    Christian
    [edit]P.S.: added the required quotes around the strings, corrected the filename of the .ldf DATA file[/edit] 

  • 0 in reply to QC

    Thanks again Christian, I'll let you know how I get on

     

    Appreciated

  • 0 in reply to Cashfac IT

    Hi Christian,

     

    I couldn't get it to work it still fails

     

    Kind Regards

    Stephen

  • +1 in reply to Cashfac IT

    Hello Stephen,

    same error? Ooops ... you did use C:\Program Files\Microsoft SQL Server\MSSQL12.SOPHOS\MSSQL\DATA\ (or whatever the actual path is) and not the symbolic X:\new\path\ - or?

    Christian

  • 0 in reply to QC

    Hi Christian,

     

    Got it working now, rolled back the version on our new server to 5.5.0. Just having issues with the registry changes and getting the securestore restore to work correctly, this is throwing up errors also.

     

    Kind Regards

    Stephen

  • 0 in reply to Cashfac IT

    New issue

     

    C:\Program Files (x86)\Sophos\Enterprise Console>databackuprestore -action=restore -datasourcetype=securestore
    Are you sure you want to restore securestore in all? (Y/N)
    y
    Microsoft (R) Build Engine version 4.7.3190.0
    [Microsoft .NET Framework, version 4.0.30319.42000]
    Copyright (C) Microsoft Corporation. All rights reserved.

    Build started 13/01/2020 15:39:20.
    Could not find file 'C:\ProgramData\Sophos\ManagementServer\Backup\SEC_SecureStore.bak'.

    Build FAILED.

    Time Elapsed 00:00:07.43
    Process 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\msbuild.exe "C:\Program Files (x86)\Sophos\Enterprise Console"\BackupRestore.proj /t:restore /clp:NoSummary /p:SubSystem=all;DataSourceType=securestore;ExcludeDB=False;LocationSpecific=False;SlientMode=False;DBServerInstance=' returned Error 1

Reply
  • 0 in reply to Cashfac IT

    New issue

     

    C:\Program Files (x86)\Sophos\Enterprise Console>databackuprestore -action=restore -datasourcetype=securestore
    Are you sure you want to restore securestore in all? (Y/N)
    y
    Microsoft (R) Build Engine version 4.7.3190.0
    [Microsoft .NET Framework, version 4.0.30319.42000]
    Copyright (C) Microsoft Corporation. All rights reserved.

    Build started 13/01/2020 15:39:20.
    Could not find file 'C:\ProgramData\Sophos\ManagementServer\Backup\SEC_SecureStore.bak'.

    Build FAILED.

    Time Elapsed 00:00:07.43
    Process 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\msbuild.exe "C:\Program Files (x86)\Sophos\Enterprise Console"\BackupRestore.proj /t:restore /clp:NoSummary /p:SubSystem=all;DataSourceType=securestore;ExcludeDB=False;LocationSpecific=False;SlientMode=False;DBServerInstance=' returned Error 1

Children
  • +1 in reply to Cashfac IT

    Hello Stephen,

    Could not find file 'C:\ProgramData\Sophos\ManagementServer\Backup\SEC_SecureStore.bak'.
    did you back up "everything" on the old server and then copy the \Backup\ folder and its contents to the new one (as per item 2. in chapter 8 of the Migration Guide)?

    Christian

  • 0 in reply to QC

    Hi Christian,

     

    My mistake, missed out chapter 8 (oops)

     

    I'm stuck on chapter 12, I don't know where to find the password for the Sophos credential (TOHE*****).

    Can't see anything on the website for this, the username is listed but not the password.

     

    Kind Regards

    Stephen

  • 0 in reply to Cashfac IT

    Hello Stephen,

    I don't know what the Sophos credential (TOHE*****) you are referring to is and why you're looking for something on the website. TOHE***** doesn't ring a bell. Could you show/tell where you have to enter it?

    Christian

  • 0 in reply to QC

    Hi Christian,

     

    I have found the relevant info. and have progressed to Chapter 14, all the managed computers are still pointing to the old server and have the "awaiting transfer policy" message next to them. I have the option to Protect Computers and run through the wizard, but the endpoint still points to the old server.

     

    Kind Regards

    Stephen

  • 0 in reply to Cashfac IT

    It's updating correctly now, but I can only select one endpoint at a time and run the protect computers option.

     

    Might be some time :-)

     

    Thanks for all your help Christian, it has been invaluable

  • 0 in reply to Cashfac IT

    Hello Stephen,

    for Protect you can't select the root of the group structure but any group, or from any view any number (Ctrl+click) or all (click a computer and Ctrl+A) computers. Of course you should select only a reasonable number. There are other options available but if you can use Protect for all or most of your endpoints it' probably the best method.

    Christian

Unfiltered HTML