locked
Powershell adding location segment to my new location variable RRS feed

  • Question

  • 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

    Wednesday, January 8, 2020 4:26 PM

All replies

  • Please post code according to the forum rules:

    How to post code in Technet Forums

    Please edit your original post and fix the code.


    \_(ツ)_/

    Wednesday, January 8, 2020 5:10 PM
  • We do not fix code you have found on the Internet. Please contact the author of the script for help.

    The error is not a PowerShell error,  The error clearly states that it is a SQL error:"An exception occurred while executing a
    Transact-SQL statement or batch.".


    \_(ツ)_/


    • Edited by jrv Wednesday, January 8, 2020 5:13 PM
    Wednesday, January 8, 2020 5:13 PM