search

dataplat / dbatools

🚀 SQL Server automation and instance migrations have never been safer, faster or freer
https://dbatools.io
MIT License
2.47k stars 803 forks source link

Restore-dbadatabase with striped backup sets fails #4835

Closed Dang3rM0us3 closed 5 years ago

Dang3rM0us3 commented 5 years ago

The Restore-dbadatabase seems to fail in my case when used with a striped backup set. When I output the command to a script I see 16 move sets while there is only 1 data and 1 log file.

Steps to Reproduce

Backup-DbaDatabase -SqlInstance alpha -Databases AdventureWorks2016 -Type Full  -CopyOnly -BackupDirectory \\backup\ -CompressBackup -FileCount 16 -EnableException 

Restore-DbaDatabase -SqlServer beta -Path \\backup\ -WithReplace -UseDestinationDefaultDirectories -EnableException

Expected Behavior

RESTORE DATABASE [AdventureWorks2016] FROM
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-1-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-10-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-11-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-12-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-13-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-14-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-15-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-16-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-2-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-3-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-4-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-5-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-6-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-7-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-8-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-9-of-16.bak' WITH FILE = 1,
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
NOUNLOAD, REPLACE, STATS = 10

Actual Behavior

RESTORE DATABASE [AdventureWorks2016] FROM
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-1-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-10-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-11-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-12-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-13-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-14-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-15-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-16-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-2-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-3-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-4-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-5-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-6-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-7-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-8-of-16.bak',
DISK = N'\alpha\Backup\AdventureWorks2016_201812181008-9-of-16.bak' WITH FILE = 1, MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf', MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf', MOVE N'AdventureWorks2016' TO N'D:\beta\MSSQL\Data\AdventureWorks2016.mdf',
MOVE N'AdventureWorks2016_log' TO N'L:\beta\MSSQL\LOG\AdventureWorks2016_log.ldf',
NOUNLOAD, REPLACE, STATS = 10

Environmental data

PSVersion 5.1.14393.2430 PSEdition Desktop PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...} BuildVersion 10.0.14393.2430 CLRVersion 4.0.30319.42000 WSManStackVersion 3.0 PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1

Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64) Jun 28 2018 17:24:28 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

Stuart-Moore commented 5 years ago

Just put a PR in that fixes the duplicate move statements

Not sure if that's the underlying cause of your fail. It'll have been like that for about a year, and my test instances seem to just ignore it (SQL engine is smarter than most of us :) )

If it still broken, could you send me any error messages please. The following normally gets the full errror:

$error[1] | Select-Object *

Dang3rM0us3 commented 5 years ago

Thanks for the quick reply. Troubleshooted with a smaller test database and no issue was made of the multiple duplicate move statements. Command performed successfully there.