Backup files are not being removed after database is successfully restored using Copy-SqlDatabase
Verbose PS Log:
PS C:\Copy-SqlDatabase -Source SQL-A\PROD -Destination UATSQL-A\UAT -Database '#CopyDatabase-DCTest' -BackupRestore -NetworkShare \uatsql-a\DatabaseBackup -force -verbose
Attempting to connect to Sql Servers..
VERBOSE: Test-SqlPath - reusing SMO connection
VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup
VERBOSE: Test-SqlPath - reusing SMO connection
VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup
Resolving NetBIOS names
Performing SMO version check
Checking to ensure the source isn't the same as the destination
Checking to ensure network path is valid
VERBOSE: [Copy-SqlDatabase][16:51:48] \uatsql-a\DatabaseBackup share can be accessed.
Checking to ensure server is not SQL Server 7 or below
Checking to ensure detach/attach is not attempted on SQL Server 2000
Checking to ensure SQL Server 2000 migration isn't directly attempted to SQL Server 2012
VERBOSE: [Copy-SqlDatabase][16:51:49] Warning if migration from 2005 to 2012 and above and attach/detach is used.
Ensuring user databases exist (counting databases)
Ensuring destination server version is equal to or greater than source
Writing warning about filestream being enabled
Building database list
VERBOSE: [Copy-SqlDatabase][16:51:52] Performing count
Building file structure inventory for 1 databases
VERBOSE: [Get-SqlFileStructure][16:51:53] #CopyDatabase-DCTest
VERBOSE: - reusing SMO connection
VERBOSE: - reusing SMO connection
######### Database: #CopyDatabase-DCTest #########
VERBOSE: [Copy-SqlDatabase][16:51:53] Checking for accessibility
VERBOSE: [Copy-SqlDatabase][16:51:53] Checking Availability Group status
VERBOSE: Performing the operation "DROP DATABASE #CopyDatabase-DCTest" on target "UATSQL-A\UAT".
CopyDatabase-DCTest already exists. -Force was specified. Dropping #CopyDatabase-DCTest on UATSQL-A\UAT.
VERBOSE: Performing the operation "Showing start time" on target "console".
Started: 07/21/2017 16:51:53
VERBOSE: Performing the operation "Backup #CopyDatabase-DCTest from SQL-A\PROD and restoring." on target "UATSQL-A\UAT".
VERBOSE: Connecting to [SQL-A\PROD]
VERBOSE: Backup-DbaDatabase - 1 database to backup
VERBOSE: Backup-DbaDatabase - Backup up database [#CopyDatabase-DCTest]
VERBOSE: Backup-DbaDatabase - Sorting Paths
VERBOSE: Backup-DbaDatabase - Striping for Filecount of 3
VERBOSE: Backup-DbaDatabase - Devices added
VERBOSE: [Copy-SqlDatabase][16:52:16] Resuse = False
VERBOSE: Restore-DbaDatabase - Trust Database Backup History Set
VERBOSE: Restore-DbaDatabase - Remote server, checking folders
VERBOSE: Restore-DbaDatabase - sorting uniquely
VERBOSE: Get-FilteredRestoreFile - Starting
VERBOSE: Get-FilteredRestoreFile - Trusted backup history loop
VERBOSE: Get-FilteredRestoreFile - 3 Files to filter
VERBOSE: Get-FilteredRestoreFile - 1 database to process
VERBOSE: Get-FilteredRestoreFile - Find Newest Full backup -
VERBOSE: Get-FilteredRestoreFile - Got a Full backup, now to find diffs if they exist
VERBOSE: Get-FilteredRestoreFile - Got a Full/Diff backups, now find all Tlogs needed
VERBOSE: Get-FilteredRestoreFile - Filtered 0 down to 0
VERBOSE: Get-FilteredRestoreFile - 0 Transaction Log backups found
VERBOSE: Get-FilteredRestoreFile - Returning Results to caller
VERBOSE: Restore-DbaDatabase - dbs to restore
VERBOSE: Restore-DbaDatabase - Starting FileSet
VERBOSE: Test-DbaLsnChain - Testing LSN Chain
VERBOSE: Test-DbaLsnChain - Passed LSN Chain checks
VERBOSE: Test-DbaRestoreVersion - RestoreVersion is 13
VERBOSE: Restore-DBFromFilteredArray - Starting
VERBOSE: Restore-DBFromFilteredArray - reusing SMO connection
VERBOSE: Restore-DBFromFilteredArray - reusing SMO connection
VERBOSE: Restore-DBFromFilteredArray - Trusted File checks
VERBOSE: Restore-DBFromFilteredArray - Checking \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-1-of-3.bak exists
VERBOSE: Test-SqlPath - reusing SMO connection
VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-1-of-3.bak
VERBOSE: Restore-DBFromFilteredArray - Checking \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-2-of-3.bak exists
VERBOSE: Test-SqlPath - reusing SMO connection
VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-2-of-3.bak
VERBOSE: Restore-DBFromFilteredArray - Checking \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-3-of-3.bak exists
VERBOSE: Test-SqlPath - reusing SMO connection
VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-3-of-3.bak
VERBOSE: RPcount = 1
VERBOSE: Restore-DBFromFilteredArray - Restoring #CopyDatabase-DCTest backup starting at order 1 - LSN in \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-1-of-3.bak
VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf
VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf
VERBOSE: Restore-DBFromFilteredArray - Moving E:\SQLDATA#CopyDatabase-DCTest_log.ldf
VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf
VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf
VERBOSE: Restore-DBFromFilteredArray - Moving E:\SQLDATA#CopyDatabase-DCTest_log.ldf
VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf
VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf
VERBOSE: Restore-DBFromFilteredArray - Moving E:\SQLDATA#CopyDatabase-DCTest_log.ldf
VERBOSE: Restore-DBFromFilteredArray - Relocating #CopyDatabase-DCTest to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf,
Relocating #CopyDatabase-DCTest_log to F:\SQLDATA#CopyDatabase-DCTest_log.ldf,
Relocating #CopyDatabase-DCTest to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf,
Relocating #CopyDatabase-DCTest_log to F:\SQLDATA#CopyDatabase-DCTest_log.ldf,
Relocating #CopyDatabase-DCTest to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf,
Relocating #CopyDatabase-DCTest_log to F:\SQLDATA#CopyDatabase-DCTest_log.ldf
VERBOSE: Restore-DBFromFilteredArray - Beginning Restore of #CopyDatabase-DCTest
VERBOSE: Restore-DBFromFilteredArray - restoring #CopyDatabase-DCTest to latest point in time
VERBOSE: Restore-DBFromFilteredArray - restore action = Database
VERBOSE: Restore-DBFromFilteredArray - Doing Recovery on last file
VERBOSE: Restore-DBFromFilteredArray - Adding device
VERBOSE: Restore-DBFromFilteredArray - Adding device
VERBOSE: Restore-DBFromFilteredArray - Adding device
VERBOSE: Restore-DBFromFilteredArray - Performing restore action
VERBOSE: Restore-DBFromFilteredArray - Succeeded, Closing Server connection
VERBOSE: Database #CopyDatabase-DCTest restored successfully
Successfully restored #CopyDatabase-DCTest to UATSQL-A\UAT
Updating database owner
VERBOSE: Performing the operation "Showing elapsed time" on target "console".
Finished: 07/21/2017 16:52:22
Elapsed time: 00:00:29
VERBOSE: Performing the operation "Showing migration time elapsed" on target "console".
Bug Report
Backup files are not being removed after database is successfully restored using Copy-SqlDatabase
Verbose PS Log:
PS C:\Copy-SqlDatabase -Source SQL-A\PROD -Destination UATSQL-A\UAT -Database '#CopyDatabase-DCTest' -BackupRestore -NetworkShare \uatsql-a\DatabaseBackup -force -verbose Attempting to connect to Sql Servers.. VERBOSE: Test-SqlPath - reusing SMO connection VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup VERBOSE: Test-SqlPath - reusing SMO connection VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup Resolving NetBIOS names Performing SMO version check Checking to ensure the source isn't the same as the destination Checking to ensure network path is valid VERBOSE: [Copy-SqlDatabase][16:51:48] \uatsql-a\DatabaseBackup share can be accessed. Checking to ensure server is not SQL Server 7 or below Checking to ensure detach/attach is not attempted on SQL Server 2000 Checking to ensure SQL Server 2000 migration isn't directly attempted to SQL Server 2012 VERBOSE: [Copy-SqlDatabase][16:51:49] Warning if migration from 2005 to 2012 and above and attach/detach is used. Ensuring user databases exist (counting databases) Ensuring destination server version is equal to or greater than source Writing warning about filestream being enabled Building database list VERBOSE: [Copy-SqlDatabase][16:51:52] Performing count Building file structure inventory for 1 databases VERBOSE: [Get-SqlFileStructure][16:51:53] #CopyDatabase-DCTest VERBOSE: - reusing SMO connection VERBOSE: - reusing SMO connection
######### Database: #CopyDatabase-DCTest ######### VERBOSE: [Copy-SqlDatabase][16:51:53] Checking for accessibility VERBOSE: [Copy-SqlDatabase][16:51:53] Checking Availability Group status VERBOSE: Performing the operation "DROP DATABASE #CopyDatabase-DCTest" on target "UATSQL-A\UAT".
CopyDatabase-DCTest already exists. -Force was specified. Dropping #CopyDatabase-DCTest on UATSQL-A\UAT.
VERBOSE: Performing the operation "Showing start time" on target "console". Started: 07/21/2017 16:51:53 VERBOSE: Performing the operation "Backup #CopyDatabase-DCTest from SQL-A\PROD and restoring." on target "UATSQL-A\UAT". VERBOSE: Connecting to [SQL-A\PROD] VERBOSE: Backup-DbaDatabase - 1 database to backup VERBOSE: Backup-DbaDatabase - Backup up database [#CopyDatabase-DCTest] VERBOSE: Backup-DbaDatabase - Sorting Paths VERBOSE: Backup-DbaDatabase - Striping for Filecount of 3 VERBOSE: Backup-DbaDatabase - Devices added VERBOSE: [Copy-SqlDatabase][16:52:16] Resuse = False VERBOSE: Restore-DbaDatabase - Trust Database Backup History Set VERBOSE: Restore-DbaDatabase - Remote server, checking folders VERBOSE: Restore-DbaDatabase - sorting uniquely VERBOSE: Get-FilteredRestoreFile - Starting VERBOSE: Get-FilteredRestoreFile - Trusted backup history loop VERBOSE: Get-FilteredRestoreFile - 3 Files to filter VERBOSE: Get-FilteredRestoreFile - 1 database to process VERBOSE: Get-FilteredRestoreFile - Find Newest Full backup - VERBOSE: Get-FilteredRestoreFile - Got a Full backup, now to find diffs if they exist VERBOSE: Get-FilteredRestoreFile - Got a Full/Diff backups, now find all Tlogs needed VERBOSE: Get-FilteredRestoreFile - Filtered 0 down to 0 VERBOSE: Get-FilteredRestoreFile - 0 Transaction Log backups found VERBOSE: Get-FilteredRestoreFile - Returning Results to caller VERBOSE: Restore-DbaDatabase - dbs to restore VERBOSE: Restore-DbaDatabase - Starting FileSet VERBOSE: Test-DbaLsnChain - Testing LSN Chain VERBOSE: Test-DbaLsnChain - Passed LSN Chain checks VERBOSE: Test-DbaRestoreVersion - RestoreVersion is 13 VERBOSE: Restore-DBFromFilteredArray - Starting VERBOSE: Restore-DBFromFilteredArray - reusing SMO connection VERBOSE: Restore-DBFromFilteredArray - reusing SMO connection VERBOSE: Restore-DBFromFilteredArray - Trusted File checks VERBOSE: Restore-DBFromFilteredArray - Checking \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-1-of-3.bak exists VERBOSE: Test-SqlPath - reusing SMO connection VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-1-of-3.bak VERBOSE: Restore-DBFromFilteredArray - Checking \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-2-of-3.bak exists VERBOSE: Test-SqlPath - reusing SMO connection VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-2-of-3.bak VERBOSE: Restore-DBFromFilteredArray - Checking \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-3-of-3.bak exists VERBOSE: Test-SqlPath - reusing SMO connection VERBOSE: Test-SqlPath - Path check is \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-3-of-3.bak VERBOSE: RPcount = 1 VERBOSE: Restore-DBFromFilteredArray - Restoring #CopyDatabase-DCTest backup starting at order 1 - LSN in \uatsql-a\DatabaseBackup#CopyDatabase-DCTest_201707211652-1-of-3.bak VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf VERBOSE: Restore-DBFromFilteredArray - Moving E:\SQLDATA#CopyDatabase-DCTest_log.ldf VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf VERBOSE: Restore-DBFromFilteredArray - Moving E:\SQLDATA#CopyDatabase-DCTest_log.ldf VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf VERBOSE: Restore-DBFromFilteredArray - Moving D:\SQLDATA\MSSQL13.PROD\MSSQL\DATA#CopyDatabase-DCTest.mdf to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf VERBOSE: Restore-DBFromFilteredArray - Moving E:\SQLDATA#CopyDatabase-DCTest_log.ldf VERBOSE: Restore-DBFromFilteredArray - Relocating #CopyDatabase-DCTest to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf, Relocating #CopyDatabase-DCTest_log to F:\SQLDATA#CopyDatabase-DCTest_log.ldf, Relocating #CopyDatabase-DCTest to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf, Relocating #CopyDatabase-DCTest_log to F:\SQLDATA#CopyDatabase-DCTest_log.ldf, Relocating #CopyDatabase-DCTest to D:\SQLDATA\MSSQL13.UAT\MSSQL\DATA#CopyDatabase-DCTest.mdf, Relocating #CopyDatabase-DCTest_log to F:\SQLDATA#CopyDatabase-DCTest_log.ldf VERBOSE: Restore-DBFromFilteredArray - Beginning Restore of #CopyDatabase-DCTest VERBOSE: Restore-DBFromFilteredArray - restoring #CopyDatabase-DCTest to latest point in time VERBOSE: Restore-DBFromFilteredArray - restore action = Database VERBOSE: Restore-DBFromFilteredArray - Doing Recovery on last file VERBOSE: Restore-DBFromFilteredArray - Adding device VERBOSE: Restore-DBFromFilteredArray - Adding device VERBOSE: Restore-DBFromFilteredArray - Adding device VERBOSE: Restore-DBFromFilteredArray - Performing restore action VERBOSE: Restore-DBFromFilteredArray - Succeeded, Closing Server connection VERBOSE: Database #CopyDatabase-DCTest restored successfully Successfully restored #CopyDatabase-DCTest to UATSQL-A\UAT Updating database owner VERBOSE: Performing the operation "Showing elapsed time" on target "console". Finished: 07/21/2017 16:52:22 Elapsed time: 00:00:29 VERBOSE: Performing the operation "Showing migration time elapsed" on target "console".
Database migration finished Migration started: 07/21/2017 16:51:53 Migration completed: 07/21/2017 16:52:23 Total Elapsed time: 00:00:29
Version Information
Additional information
Both SQL Server instance service accounts and account running powershell session have full control over share and directory