SQL Server Using Powershell to Move databases between Instances

It’s sometime needed to move database between instances as in when migrating to a different instance. Let’s see how this can be achieved with powershell. This script assumes that both the instances are on same server. This script only moves databases which don’t exist on the destination instance.

To start with, let’s define the source and destination instances and get the list of the databases in each of these instances.

#source instance
$ssrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "AHMAD-PC\SQLEXPRESS"
#destination instance
$dsrv = new-object('Microsoft.SqlServer.Management.Smo.Server') "AHMAD-PC"
#get source databases - to be moved to destination instance. 
$sdbs=$ssrv.Databases
#get destinaction databases - to skip existing databases.
$ddbs=$dsrv.Databases

The variable $ssrv is the source instance server object and the variable $dsrv is the destination instance server object. The databases will be moved from $ssrv to $dsrv. The variables $sdbs and $ddbs contains the list of all databases of $ssrv and $dsrv instances respectively. The next step is to iterate through source instance databases and compare them with destination instance databases.

# iterate through source databases
for($cnt=0;$cnt -le $sdbs.Count;$cnt++)
{
    $sdb=$sdbs[$cnt]
    #initialize variable to initial value.
    $dbcount=0
    $owner=$sdb.Owner
    #iterate through destination databases
    foreach($ddb in $ddbs)
    {
       if($sdb.Name -eq $ddb.Name)
            {
                $dbcount=1
                break;
            }
     }

The above code iterates through source instance databases via for loop. It then iterates through destination databases and compares them with source databases. If a match exists then the variable $dbcount is set to 1 and it comes out of the loop. Thus, a database is moved only when $dbcount is 1. The code also keeps track of database owner as it is required to attach the database to destination instance.  The next step is to collect file details of the database to be moved.

if($dbcount -eq 0)
    {       
        $sc = new-object System.Collections.Specialized.StringCollection
        #iterate through filegroups and add datafiles
        foreach($fg in $sdb.FileGroups)
        {
            foreach($df in $fg.Files)
            {
               $sc.Add($df.FileName)
            }
        }
       #get all log files 
       foreach($lf in $sdb.LogFiles)
       {  
           $sc.Add($lf.FileName)
       }

$sdb.Filegroups returns all filegroups in a databases and $fg.files gives all files in a particular filegroup. Similarly $sdb.Logfiles returns list of log files in a database. The physical file path $df.Filename and $lf.Filename are added to the string collection object $sc to be used later to attach the databases to destination instance. The next step is to detach the database.

#kill all sessions connected to database being moved.

$ssrv.KillALLProcesses($sdb.Name)

#detach database from source

$ssrv.DetachDatabase($sdb.Name,”False”)

The above code first kills all connections to the database which is to be moved and then detaches it from the source instance. The function DetachDatabase takes three arguments, database name and a Boolean value which tells whether statics are to be updated or not.  In this case statistics aren’t updated.  The next and final step is to attach the database to destination instance.

#Attach database to destination.
    $dsrv.AttachDatabase($sdb.Name, $sc, $owner, [Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

The attachdatabase function takes 4 arguments; database name which is to be attached, list of database files as string collection object, database owner and attach options if any. In our case, $sdb.Name is the database name, $sc the string collection object which has list of database files, the database owner $owner. It doesn’t consider any of the other attach options.

The complete script can be downloaded from http://sdrv.ms/ZDCI2h

 
Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook