Thread Info
  • State Not Answered
  • Locked Locked
  • Replies 12 replies
  • Subscribers 1 subscriber
  • Views 41580 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

DataBackupRestore fails

ronh

I  upgraded old server from 4.5 to 5.2, followed the documentation to migrate to new server via: http://www.sophos.com/en-us/medialibrary/PDFs/documentation/sec_52_mgeng.pdf

In section 8, for the DataBackupRestore, I get this below.  Any help would be much appreciated.

Thanks,

-Ron

-----------------------------------------------------------------------------------------------------

Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.296]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/18/2013 10:11:29 AM.
Copy file C:\ProgramData\Sophos\ManagementServer\Backup\Databases\SOPHOSENC52.ba
k successful.

C:\Users\All Users\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S
"(local)\SOPHOS" -d "master" -b -Q "IF EXISTS (SELECT name FROM master.dbo.sysd
atabases WHERE name = N'SOPHOSENC52') BEGIN ALTER DATABASE SOPHOSENC52 SET OFFLI
NE WITH ROLLBACK AFTER 5 END"
Attempting restore with SQL2012 backup schema.

C:\Users\All Users\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S
"(local)\SOPHOS" -d "master" -b -Q "CREATE TABLE #backupSetInfo (BackupName NVA
RCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate da
tetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR
(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,Da
tabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),Last
LSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),Ba
ckupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage sma
llint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,
SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareV
ersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,R
ecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifi
er,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBacku
pChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForc
eOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN nu
meric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),Differ
entialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUI
D uniqueidentifier,CompressedBackupSize INT,containment tinyint); INSERT #backup
SetInfo EXEC('RESTORE HEADERONLY FROM DISK=N' + '''C:\ProgramData\Sophos\TempDat
a\SOPHOSENC52.bak'''); DECLARE @backupsetnumber smallint; SET @backupsetnumber =
(SELECT TOP(1) Position FROM #backupSetInfo ORDER BY BackupFinishDate DESC); RE
STORE DATABASE SOPHOSENC52 FROM DISK = 'C:\ProgramData\Sophos\TempData\SOPHOSENC
52.bak' WITH FILE = @backupsetnumber, REPLACE, RECOVERY; DROP TABLE #backupSetIn
fo;"
Msg 213, Level 16, State 7, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 3250, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2008/SQL2008R2 backup schema.

C:\Users\All Users\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S
"(local)\SOPHOS" -d "master" -b -Q "CREATE TABLE #backupSetInfo (BackupName NVA
RCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate da
tetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR
(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,Da
tabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),Last
LSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),Ba
ckupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage sma
llint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,
SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareV
ersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,R
ecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifi
er,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBacku
pChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForc
eOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN nu
meric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),Differ
entialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUI
D uniqueidentifier,CompressedBackupSize INT); INSERT #backupSetInfo EXEC('RESTOR
E HEADERONLY FROM DISK=N' + '''C:\ProgramData\Sophos\TempData\SOPHOSENC52.bak'''
); DECLARE @backupsetnumber smallint; SET @backupsetnumber = (SELECT TOP(1) Posi
tion FROM #backupSetInfo ORDER BY BackupFinishDate DESC); RESTORE DATABASE SOPHO
SENC52 FROM DISK = 'C:\ProgramData\Sophos\TempData\SOPHOSENC52.bak' WITH FILE =
@backupsetnumber, REPLACE, RECOVERY; DROP TABLE #backupSetInfo;"

(1 rows affected)
Msg 5133, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSS
QL\DATA\SOPHOSENC52.mdf" failed with the operating system error 3(The system can
not find the path specified.).
Msg 3156, Level 16, State 3, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
File 'SOPHOSENC51' cannot be restored to 'C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\DATA\SOPHOSENC52.mdf'. Use WITH MOVE to identify a valid location
for the file.
Msg 5133, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSS
QL\DATA\SOPHOSENC52.ldf" failed with the operating system error 3(The system can
not find the path specified.).
Msg 3156, Level 16, State 3, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
File 'SOPHOSENC51_log' cannot be restored to 'C:\Program Files\Microsoft SQL Ser
ver\MSSQL.1\MSSQL\DATA\SOPHOSENC52.ldf'. Use WITH MOVE to identify a valid locat
ion for the file.
Msg 3119, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
Problems were identified while planning for the RESTORE statement. Previous mess
ages provide details.
Msg 3013, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Attempting restore with SQL2005 backup schema.

C:\Users\All Users\Sophos\ManagementServer\Backup\DataBackupRestore>sqlcmd -E -S
"(local)\SOPHOS" -d "master" -b -Q "CREATE TABLE #backupSetInfo (BackupName NVA
RCHAR(128),BackupDescription NVARCHAR(255),BackupType smallint,ExpirationDate da
tetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName NVARCHAR
(128),ServerName NVARCHAR(128),DatabaseName NVARCHAR(128),DatabaseVersion INT,Da
tabaseCreationDate datetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),Last
LSN numeric(25,0),CheckpointLSN numeric(25,0),DatabaseBackupLSN numeric(25,0),Ba
ckupStartDate datetime,BackupFinishDate datetime,SortOrder smallint,CodePage sma
llint,UnicodeLocaleId INT,UnicodeComparisonStyle INT,CompatibilityLevel tinyint,
SoftwareVendorId INT,SoftwareVersionMajor INT,SoftwareVersionMinor INT,SoftwareV
ersionBuild INT,MachineName NVARCHAR(128),Flags INT,BindingID uniqueidentifier,R
ecoveryForkID uniqueidentifier,Collation NVARCHAR(128),FamilyGUID uniqueidentifi
er,HasBulkLoggedData INT,IsSnapshot INT,IsReadOnly INT,IsSingleUser INT,HasBacku
pChecksums INT,IsDamaged INT,BeginsLogChain INT,HasIncompleteMetaData INT,IsForc
eOffline INT,IsCopyOnly INT,FirstRecoveryForkID uniqueidentifier,ForkPointLSN nu
meric(25,0),RecoveryModel NVARCHAR(128),DifferentialBaseLSN numeric(25,0),Differ
entialBaseGUID uniqueidentifier,BackupTypeDescription NVARCHAR(128),BackupSetGUI
D uniqueidentifier); INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N'
+ '''C:\ProgramData\Sophos\TempData\SOPHOSENC52.bak'''); DECLARE @backupsetnumb
er smallint; SET @backupsetnumber = (SELECT TOP(1) Position FROM #backupSetInfo
ORDER BY BackupFinishDate DESC); RESTORE DATABASE SOPHOSENC52 FROM DISK = 'C:\Pr
ogramData\Sophos\TempData\SOPHOSENC52.bak' WITH FILE = @backupsetnumber, REPLACE
, RECOVERY; DROP TABLE #backupSetInfo;"
Msg 213, Level 16, State 7, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
RESTORE HEADERONLY is terminating abnormally.
Msg 3250, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
The value '0' is not within range for the FILE parameter.
Msg 3013, Level 16, State 1, Server PSCVM-SOPHOS-02\SOPHOS, Line 1
RESTORE DATABASE is terminating abnormally.
Failed to restore

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

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

Build FAILED.

Time Elapsed 00:00:01.60
Process 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\msbuild.exe "C:\Users\A
ll Users\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

:38503


This thread was automatically locked due to age.
  • 0

    Hello ronh,

    at first glance I thought this is a rather easy one. Looks like your database files were not on C: (or not the mentioned path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA). Seems you backed up from SQL2008 - at least the attempts with the 2012 and 2005 schemas fail early while the section for 2008 initiates the RESTORE..

    But then there's a detail which puzzles me:

    Msg 3156, Level 16, State 3, Server PSCVM-SOPHOS-02\SOPHOS, Line 1

    File 'SOPHOSENC51' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SOPHOSENC52.mdf'

    Doesn't seem right ... you did use DataBackupRestore after the upgrade to 5.2, didn't you? And before that you had verfied the 5.2 upgrade I assume.

    Christian

    :38529
  • 0

    Greetings!

    I am having this same issue. I followed the migration guide but it fails in the database restore with the identical errors as above.

    Was there any resolution on this?

    Thanks,

    Brandon

    :39297
  • 0

    This is the issue, when you were running 4.5 it was using SQL 2005 and the path was C:\Program Files\Microsoft SQL Server\MSSQL.1\.  Even if you upgrade the same server to 5.2.x  using SQL 2008, it'll still keep that same path.

    However a fresh install of 5.2.x on a brand new server will have a new path to SQL 2008, which is C:\Program Files\Microsoft SQL Server\MSSQL10_50.SOPHOS.

    So when you follow the steps of the guide to run the DataBackupRestore, it'll fail because it'll be looking for the C:\Program Files\Microsoft SQL Server\MSSQL.1\ directory on the new server and won't find it.

    I don't have an answer for this, waiting for Sophos to respond.

    :40853
  • 0

    Experiencing the same issue here as well migrating EC from SBS2008 to SBS2011.

    Will also log a case with support, but an update on this here once a solution is found would be fantastic.

    :41135
  • 0

    Hello msid,

    arguably the DataBackupRestore.exe could be made smart enough to build the necessary command but then it could lead to undesired results in certain configurations. It (or better - the underlying SQL RESTORE) fails because the new SQL Server uses a different path for the database files. SQL RESTORE can be instructed to restore the database files to a different location.

    The solution is (hopefully) in Sqlcmd commands for accessing the Sophos core database and extracting information.

    Christian

    :41151
  • 0

    I have just logged a support call for the same issue.

    You would think this would be a fairly regular scenario. I'm surprised it's not documented or that DataBackupRestore doesn't look for it.

    :41239
  • 0

    I have precisely the same issue.  Support staff was useless.  Sophos admins, I don't believe you are on the right path.  It is only on the SQL2008 attempt that the errors show incorrect path messages, the 2005 attempt does not.

    I notice that the SQL2008 schema attempt shows (1 row affected), then displays the wrong path messages. Is it possible this affected row is causing the 2005 schema attempt to fail?

    "Column name or number of supplied values does not match table definition." indicates that the attempted sqlcmd does not fit the table properly.

    Looks to me like the remaining errors are due to the failure of ...

    INSERT #backupSetInfo EXEC('RESTORE HEADERONLY FROM DISK=N' + '''C:\ProgramData\Sophos\TempData\SOPHOSENC52.bak''')

    :42303
  • 0

    I have finally sorted this out with some help from Sophos support.

    Because we are migrating from an install that was originally using SQL server 2005 the location of the database files is different.

    I found that if I create the following path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\  on my new server and give "Network Service" permissions to it that the DataBackupRestore command works perfectly.

    Have fun with the rest of the migration though. I've spent most of this week trying to get child SUM's and clients to report back to the new server. It's all done now but not an easy process.

    :43059
  • 0

    I had to copy over the database files and attach them manually through Management Studio.  Also had to set the permissions on the databases manually through management studio.

    :43079
  • 0

    I had the same experience. It seems like the DataBackupRestore is looking for the old SQL location.

    Once I added the folders "MSSQL$SOPHOS" and "MSSQL$SOPHOS\DATA to the following path: "C:\Program Files\Microsoft SQL Server\" the database restore build was successful.

    I was pulling my hair out trying to figure this out beforehand. Hope this helps someone else.

    :43691
Unfiltered HTML