时间戳

首页 > 解决方案 > 还原数据库“替换”总是出错

sql-server - 还原数据库“替换”总是出错

问题描述

我想在另一个 linux 服务器上拥有生产 sql server 数据库（在 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:\Databases\BDRecup.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:\Databases\BDRecup_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)

怎么了？谢谢

标签： sql-serversqlcmd

解决方案

你能跑吗

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'

另外我注意到您的 RESTORE FILELISTONLY 的备份文件名为

/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

推荐阅读