sql-server – 一次将多个数据库从C：移动到D：

因为我想在我的C：驱动器上回收一些空间,我想把它们移到我的D：驱动器上.

我找到了this MSDN article,但这似乎是只移动一个数据库的过程.

是否有自动方式或脚本一次移动我的所有数据库？

解决方法

#load SMO
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
#Added line if using SQL Server 2012 or later
Import-module SQLPS
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#Create server object and output filename 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server "localhost"
$outputfile=([Environment]::GetFolderPath("MyDocuments"))+"FileMover.ps1"

#set this for your new location
$newloc="X:NewDBLocation"

#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)
{
    #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)
                $oldloc=$file.Filename.Substring(0,$file.Filename.LastIndexOf(''))
                $dbchange+="`$db.FileGroups[`""+$fg.Name+"`"].Files[`""+$file.Name+"`"].Filename=`"$newloc`"+$shortfile+"`""
                $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"

            }
        }

        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=`"$newloc`"+$shortfile+"`""
            $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"
        }

        $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
    }
}

输出将是MyDocuments文件夹中的FileMover.ps1脚本,如下所示：

Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Import-Module SQLPS
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') "localhost" | out-null
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server 
    #----------------------------------------------------------------------
    $db=$server.Databases["AdventureWorks2012"]
    $db.FileGroups["PRIMARY"].Files["AdventureWorks2012_Data"].Filename="X:NewDBLocationAdventureWorks2012_Data.mdf"
    $db.LogFiles["AdventureWorks2012_Log"].Filename="X:NewDBLocationAdventureWorks2012_log.ldf"
    $db.Alter()
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
    ROBOCOPY "C:DBData" "X:NewDBLocation" AdventureWorks2012_Data.mdf /copyall /mov
    ROBOCOPY "C:DBFilesLog" "X:NewDBLocation" AdventureWorks2012_log.ldf /copyall /mov
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET ONLINE;" -Database "master"
    #----------------------------------------------------------------------
    $db=$server.Databases["AdventureWorks2012DW"]
    $db.FileGroups["PRIMARY"].Files["AdventureWorksDW2012_Data"].Filename="X:NewDBLocationAdventureWorksDW2012_Data.mdf"
    $db.LogFiles["AdventureWorksDW2012_Log"].Filename="X:NewDBLocationAdventureWorks2012DW_log.ldf"
    $db.Alter()
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
    ROBOCOPY "C:DBData" "X:NewDBLocation" AdventureWorksDW2012_Data.mdf /copyall /mov
    ROBOCOPY "C:DBData" "X:NewDBLocation" AdventureWorks2012DW_log.ldf /copyall /mov
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET ONLINE;" -Database "master"

...

注意事项

>该脚本将所有文件(无论其源位置)移动到同一目的地.您需要调整自定义位置路径.>该脚本旨在在您需要移动的服务器上运行文件打开(参见localhost’的所有用法).将localhost替换为您的实例名称,如果您远程运行它.>您运行此用户的用户两者都需要访问移动中涉及的所有文件夹路径更新SQL服务器文件名信息并移动文件.>我使用InvokeSQLCmd进行离线/在线执行,因为.SetOffline()和.SetOnline方法具有时髦的特性.我发现这更可靠.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!