还原数据库'with replace'总是出错

Question

我想在另一个Linux服务器中拥有生产SQL服务器数据库(在Windows上运行(来开发应用程序。

我在Windows SQL Server上从sqlcmd shell进行了备份，现在我尝试在Linux上新安装的mssql服务器中恢复此备份。

我已经创建了带有CREATE database BDRecup数据库

有了RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/backup/BDRecup_backup_2018_05_17_020000_9119969.bak'，我得到了mdf和ldf的名字。这个命令的输出很大，并且 shell 格式不好：

LogicalName               PhysicalName                                                                                                                                                                                                                                                        Type FileGroupName                                                                                                                    Size                 MaxSize              FileId               CreateLSN                   DropLSN                     UniqueId                             ReadOnlyLSN                 ReadWriteLSN                BackupSizeInBytes    SourceBlockSize FileGroupId LogGroupGUID                         DifferentialBaseLSN         DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint                              SnapshotUrl                                                                                                                                                                                                                                                                                                                                  
BDRecupR        F:DatabasesBDRecup.mdf                                                                                                                                                                                                                                    D    PRIMARY                                                                                                                                   11080302592       35184372080640                    1                           0                           0 69C57793-BD24-4777-A657-22CDC8DE14F2                           0                           0            509673472            4096           1 NULL                                        13031000001052800036 60E098AC-391A-4923-8498-10E208BBA95E          0         1 NULL                                       NULL                                                                                                                                                                                                                                                                                                                                            
BDRecupR_log    F:DatabasesBDRecup_log.ldf                                                                                                                                                                                                                                L    NULL                                                                                                                                       1248985088        2199023255552                    2                           0                           0 E5CC3407-C600-4A5A-B304-02489CAA7DD2                           0                           0                    0            4096           0 NULL                                                           0 00000000-0000-0000-0000-000000000000          0         1 NULL                                       NULL                                                                                                                                                                                                                                                                                                                                            
(2 rows affected)

我尝试恢复

RESTORE DATABASE BDRecup FROM DISK = '/var/opt/mssql/backup/BDRecup_backup_2018_05_17_020000_9119969.bak'
WITH REPLACE,
MOVE 'BDRecupR' TO '/var/opt/mssql/data/BDRecup.mdf',
MOVE 'BDRecupR_log' TO '/var/opt/mssql/data/BDRecup_log.ldf'
GO

但我总是收到错误

Msg 3154, Level 16, State 4, Server Desktop, Line 1
The backup set contains a copy of a database different from the existing one 'BDRecup'.
Msg 3013, Level 16, State 1, Server Desktop, Line 1

如果我删除BDRecupDROP database BDRecup我会得到The database 'BDRecup' does not exists.

Windows SQL Server 版本

Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64) 
Apr 27 2017 17:36:12 
Copyright (c) Microsoft Corporation
Web Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: ) (Hypervisor)

Linux MSSQL 版本

Microsoft SQL Server 2017 (RTM-CU7) (KB4229789) - 14.0.3026.27 (X64) 
May 10 2018 12:38:11 
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 18.04 LTS)

这是怎么回事？ 谢谢

Answer 1

你能跑吗

SELECT @@Version 

在两台服务器上检查您是否没有从较高版本还原到较低版本的SQL Server？

另外，你能分享一下结果吗

RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/data/BDRecup_backup_2018_05_17_020000_9119969.bak'

根据提供的信息，您的还原语句应为：

RESTORE DATABASE BDRecup FROM DISK = '/var/opt/mssql/backup/BDRecups_backup_2018_05_17_020000_9119969.bak'
WITH REPLACE,
MOVE 'BDRecuperacionesR' TO '/var/opt/mssql/data/BDRecup.mdf',
MOVE 'BDRecuperacionesR_log' TO '/var/opt/mssql/data/BDRecup_log.ldf'

我还注意到您的恢复文件列表仅将备份文件名为

/var/opt/mssql/data/BDRecup_backup_2018_05_17_020000_9119969.bak

在还原数据库语句中要从还原时

/var/opt/mssql/backup/BDRecups_backup_2018_05_17_020000_9119969.bak

目录和文件名的更改是有意的吗？您应该在单个备份文件上运行这两个语句

根据提供的信息和聊天的更多信息：正在使用的备份文件是差异备份，除了还原差异备份之前进行的数据库的完整备份之外，无法还原任何其他内容。

The information that the backup is a full backup or differential can be 
identified from the filelistonly command by checking the DifferentialBaseLSN
column which is null for a full backup