Restore of brand new db fails when use -ReuseSourceFolderStructure option

asharovarov commented 7 years ago

Is this:

Just place "x" to indicate selection (e.g. "[x]")

[ ] feature
[X] bug:

System Details

Operating system name and version: Windows Server 2016 Standard 6.3 (Build 14393
Output from $PSVersionTable:

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

Output of dbatools version:

Major  Minor  Build  Revision
-----  -----  -----  --------
0      8      951    -1

SQL Server version for source/target SQL Server 2016 SP1 CU2 13.0.4422.0 (X64) / SQL Server 2016 SP1 CU2 13.0.4422.0 (X64)

Steps to Reproduce

Example:

Setup a sample database: IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'TestRestore') CREATE DATABASE TestRestore GO

-- Make a backup EXEC dbo.DatabaseBackup @Databases = N'TestRestore' , @Directory = N'\db-mag-prod\Backups\' , @BackupType = N'Full'

Run Command:

Get-DbaBackupHistory -SqlServer "SourceServer" -Databases DbName -Raw -LastFull | Restore-DbaDatabase -SqlServer "DestinationServer" -WithReplace -ReuseSourceFolderStructure

Received following error

WARNING: Restore-DBFromFilteredArray - Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'DestinationServer'.  ---> Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is
denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'H:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\TestRestore.mdf'.
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages, Boolean retry)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at CallSite.Target(Closure , CallSite , Object , Object )

Action Results

Example:

The Restore operation creates folders matching the target file names. And fails to restore the database because it can't create file name with matching the folder: badfolder

Expected Results

Example:

Restore should succeed. No folders with names matching target files should be created.

Attached screen shots/console output

Use of Start-Transcript can help collection of console output and exceptions
Provide screen shots of the output from your console if available.

Import - Attach dbatools logs

Latest release of dbatools now includes a messaging and logging system. You can find this information via the Get-DbaConfig function. Use of the Set-DbaConfig controls the configuration of the system.
Ensure this returns true: (Get-DbaConfig -Name errorlogenabled).value
Locate the log path for the error logs using this command: (Get-DbaConfig -Name dbatoollogpath).Value
Attach the latest xml and log files found in the path shown.

Stuart-Moore commented 7 years ago

Please rerun with outputscriptonly

Pretty sure this is an issue with the get-dbabackuphistory output I've raised in another issue.

If it is need Fred to fix the output types

Well find that no file name is issued after the folder path in the restore statement

Cheers

Stuart

On Mon, 8 May 2017, 21:59 Alexander Sharovarov, notifications@github.com wrote:

Is this:

Just place "x" to indicate selection (e.g. "[x]")

feature

bug:

System Details

Operating system name and version: Windows Server 2016 Standard 6.3 (Build 14393

Output from $PSVersionTable:

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

Output of dbatools version:

Major Minor Build Revision

0 8 951 -1

SQL Server version for source/target SQL Server 2016 SP1 CU2 13.0.4422.0 (X64) / SQL Server 2016 SP1 CU2 13.0.4422.0 (X64)

Steps to Reproduce Example:

Setup a sample database: IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'TestRestore') CREATE DATABASE TestRestore GO

-- Make a backup EXEC dbo.DatabaseBackup @Databases https://github.com/Databases = N'TestRestore' , @Directory https://github.com/Directory = N'\db-mag-prod\Backups' , @BackupType = N'Full'

Run Command:

Get-DbaBackupHistory -SqlServer "SourceServer" -Databases DbName -LastFull | Restore-DbaDatabase -SqlServer "DestinationServer" -TrustDbBackupHistory -WithReplace -ReuseSourceFolderStructure

Received following error

WARNING: Restore-DBFromFilteredArray - Microsoft.SqlServer.Management.Smo.FailedOperationException: Restore failed for Server 'SourceServer'. ---> Microsoft.SqlServer.Management.Smo.SmoException: System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'H:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\TestRestore.mdf'. at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages, Boolean retry) at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries) at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv) at CallSite.Target(Closure , CallSite , Object , Object )

Action Results Example:

The Restore operation creates folders matching the target file names. And fails to restore the database because it can't create file name with matching the folder: [image: badfolder] https://cloud.githubusercontent.com/assets/12534471/25825086/96d393e4-340f-11e7-94e3-6c6bed8cc982.png Expected Results Example:

Restore should succeed. No folders with names matching target files should be created. Attached screen shots/console output

Use of Start-Transcript can help collection of console output and exceptions

Provide screen shots of the output from your console if available.

Import - Attach dbatools logs

Latest release of dbatools now includes a messaging and logging system. You can find this information via the Get-DbaConfig function. Use of the Set-DbaConfig controls the configuration of the system.

Ensure this returns true: (Get-DbaConfig -Name errorlogenabled).value

Locate the log path for the error logs using this command: (Get-DbaConfig -Name dbatoollogpath).Value

Attach the latest xml and log files found in the path shown.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/sqlcollaborative/dbatools/issues/1159, or mute the thread https://github.com/notifications/unsubscribe-auth/AFWoZpGAr-o52CANkic-VUuhNm0-rWNUks5r34JGgaJpZM4NUfcL .

asharovarov commented 7 years ago

The issue is in Restore-DBFromFilteredArray.ps1. Line 157

if ((New-DbaSqlDirectory -Path $File -SqlServer:$SqlServer -SqlCredential:$SqlCredential).Created -ne $true)

New directories created using the file name, while the file name itself should be removed. It should be (Split-Path -Path $File -Parent) instead of $File

if ((New-DbaSqlDirectory -Path (Split-Path -Path $File -Parent) -SqlServer:$SqlServer -SqlCredential:$SqlCredential).Created -ne $true)

I can fix myself, but I need figure out how to contribute.

asharovarov commented 7 years ago

Ah... I don't have permissions to Publish my fix... Could you please advise how to get permissions? Thank you in advance.

Stuart-Moore commented 7 years ago

Needs fixing on 155 as well:

                if ((Test-SqlPath -Path (Split-path -path $File -parent) -SqlServer:$SqlServer -SqlCredential:$SqlCredential) -ne $true) {
                    if ((New-DbaSqlDirectory -Path (Split-path -path $File -parent) -SqlServer:$SqlServer -SqlCredential:$SqlCredential).Created -ne $true)

Have you used git/github before?

you'll need to clone/fork the repo, create a new branch, do your work, commit it, push it and then create a Pull request.

Don't worry if that doesn't make sense, we have some documentation - https://dbatools.io/join-us/github/

asharovarov commented 7 years ago

Yes. I fixed 155 at my local copy. Tested. It works.

No. I didn't use git before. Thanks. I joined Slack and CK gave me some advice on that.

Thanks for the help.

Stuart-Moore commented 7 years ago

👍

asharovarov commented 7 years ago

Created a pull request. Let see how it goes. ;)

Stuart-Moore commented 7 years ago

I've signed off the review (Restores are my little piece), so should get merged in when once of the controllers next comes online.

asharovarov commented 7 years ago

Thank you Stuart

asharovarov commented 7 years ago

Issue is fixed. Thank you @Stuart-Moore, @ConstantineK and @potatoqualitee for help.

dataplat / dbatools