I'm using the following script to move databases on a server, however, when I look inside the script it seems to add 'C:\Windows\system32\' to the start of the destination folder some of the time for some of the databases.
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server "localhost"
$outputfile=([Environment]::GetFolderPath("MyDocuments"))+"\FileMover.ps1"
#Robocopy log file location
$RoboLogFile="F:\RoboCopy_Log\RCLog.txt"
#set this for your new location
$newDBloc="E:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data"
$newLogloc="F:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log"
#get your databases
$db_list=$server.Databases
#build initial script components
"Add-PSSnapin SqlServerCmdletSnapin100" > $outputfile
"Add-PSSnapin SqlServerProviderSnapin100" >> $outputfile
"Import-Module SQLPS" >> $outputfile
"[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') `"localhost`" | out-null" >> $outputfile
"`$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server " >> $outputfile
foreach($db_build in $db_list)
#Write-Host $db_build
{
Write-Host $db_build
#only process user databases
if(!($db_build.IsSystemObject))
{
#script out all the file moves
"#----------------------------------------------------------------------" >> $outputfile
"`$db=`$server.Databases[`""+$db_build.Name+"`"]" >> $outputfile
$dbchange = @()
$robocpy =@()
foreach ($fg in $db_build.Filegroups)
{
foreach($file in $fg.Files)
{
$shortfile=$file.Filename.Substring($file.Filename.LastIndexOf('\')+1)
Write-Host $shortfile
Write-Host $file
$oldloc=$file.Filename.Substring(0,$file.Filename.LastIndexOf('\'))
$dbchange+="`$db.FileGroups[`""+$fg.Name+"`"].Files[`""+$file.Name+"`"].Filename=`"$newDBloc`\"+$shortfile+"`""
Write-Host $dbchange
$robocpy+="ROBOCOPY `"$oldloc`" `"'$newDBloc'`" `"' $shortfile`" /copyall /mov /LOG+:$RoboLogFile"
Write-Host $robocpy
}
}
foreach($logfile in $db_build.LogFiles)
{
$shortfile=$logfile.Filename.Substring($logfile.Filename.LastIndexOf('\')+1)
$oldloc=$logfile.Filename.Substring(0,$logfile.Filename.LastIndexOf('\'))
$dbchange+="`$db.LogFiles[`""+$logfile.Name+"`"].Filename=`"$newLogloc`\"+$shortfile+"`""
Write-Host $dbchange
$robocpy+="ROBOCOPY `"$oldloc`" `"'$newLogloc'`" `"'$shortfile'`" /copyall /mov /LOG+:$RoboLogFile"
Write-Host $robocpy
}
$dbchange+="`$db.Alter()"
$dbchange+="Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET OFFLINE WITH ROLLBACK IMMEDIATE;`" -Database `"master`""
$dbchange >> $outputfile
$robocpy >> $outputfile
"Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET ONLINE;`" -Database `"master`"" >> $outputfile
}
}
This is the error I received:
The following exception occurred while trying to enumerate the collection: "An exception occurred while executing a
Transact-SQL statement or batch.".
At C:\Users\NLytle\Documents\WindowsPowerShell\MoveDBs\MoveDBs.ps1:40 char:25
+ foreach ($fg in $db_build.Filegroups)
+ ~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
+ FullyQualifiedErrorId : ExceptionInGetEnumerator
Also, the script does not delete the source files as I would expect it to.
Any ideas as to what the issue might be? I am really new to Powershell and am trying my best with searching the Net and books, but not sure what I am looking for.
Nancy
nlytle It's all in a dream in the blink of an eye on the whim of a wish of a cloud
