CODEWITHSUNDEEP
sql-serversqlcmd
crossmax
crossmax

Reputation: 342

Restore database 'with replace' always getting error

I want to have the production sql server database (run on Windows) in another linux server to developing applications.

I did a backup from sqlcmd shell on Windows SQL Server and now I trying to restore this backup in a new installation mssql server on Linux.

I've created the database with CREATE database BDRecup

With RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/backup/BDRecup_backup_2018_05_17_020000_9119969.bak' I get the mdf and ldf names. The output of this command is huge, and shell not formatting well:

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)

I try to restore with

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

but I always get the error

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

If I delete the BDRecup DROP database BDRecup I get The database 'BDRecup' does not exists.

Windows SQL Server version

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 version

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)

What going on? Thanks

Upvotes: 2

Views: 4907

Answers (1)

SQLApostle
SQLApostle

Reputation: 590

Can you run 

SELECT @@Version

on both servers to check that you are not restoring from a higher version to a lower version of sql server ?

Also , could you share the results of 

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

Based on the information provided, your restore statement should be : 

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'

Also I have noticed that your RESTORE FILELISTONLY has the backup file name as 

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

While in the restore database statement you are restoring from 

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

Is the change in directories and filename intentional ? You should be running both statements on a single backup file

Based on information provided and further information on chat : the backup file being used is a differential backup and cannot be restored on anything else other than a restore of the full backup of the database taken prior to the diff backup.

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

Upvotes: 4

Related Questions