Powershell & SMO–Copy and attach database

Mission: Copy mdf and ldf files from a source location to a destination computer and then attach them to the destination computer:

Script:

#### Variables that need to be specified....

##variables needed for copy: source
$mdfSourcePath = "\\SourceServer\backup\myDb.mdf";
$ldfSourcePath = "\\SourceServer\backup\myDb.ldf";

##variables needed for copy: destination
$mdfCopyToDestinationPath ="\\DestinationServer\d$\MSSQLData";
$ldfCopyToDestinationPath ="\\DestinationServer\l$\MSSQLLog";

##variables regarding the destination database
$databaseServer = "DestinationSqlServer";
$databaseName = "databaseName";
$mdfDestinationFolderForAttach = "d:\MsSqlData\";
$ldfDestinationFolderForAttach = "l:\MsSqlLog\"

##### end of variables that need to be specified

$mdfFileName = [System.IO.Path]::GetFileName($mdfSourcePath);
$ldfFileName = [System.IO.Path]::GetFileName($ldfSourcePath);

cls;

####### Drop the destination database if it already exists

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$sqlServerSmo = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server ($databaseServer)

Write-Host "Checking to see if $databaseName needs to be dropped....";
if ($sqlServerSmo.databases[$databaseName] -ne $null)
{
  Write-Host "Dropping database $databaseName on server $databaseServer"
  $sqlServerSmo.KillAllProcesses($databaseName)
  $sqlServerSmo.databases[$databaseName].drop()
  Write-Host "Database $databaseName on server $databaseServer has been dropped"
}
else
{
    Write-Host "Database $databaseName does not exist on server $databaseServer"
}
Write-Host "";
######### Copy files from source to destination

Write-Host "Copying mdf $mdfSourcePath to $mdfCopyToDestinationPath...."
Copy-Item $mdfSourcePath $mdfCopyToDestinationPath -Force
Write-Host "Copy complete!"

Write-Host "Copying mdf $ldfSourcePath to $ldfCopyToDestinationPath...."
Copy-Item $ldfSourcePath $ldfCopyToDestinationPath -Force
Write-Host "Copy complete!"
Write-Host "";
#######  Attach the database to detsination server

$datafile = [System.IO.Path]::Combine($mdfDestinationFolderForAttach, $mdfFileName);
$logfile = [System.IO.Path]::Combine($ldfDestinationFolderForAttach, $ldfFileName);

$sc = new-object System.Collections.Specialized.StringCollection;
$sc.Add($datafile) | Out-Null;
$sc.Add($logfile) | Out-Null;

Write-Host "Attaching $datafile and $logfile to $databaseServer....";
$Error.Clear();
try
{
    $sqlServerSmo.AttachDatabase($databaseName, $sc);
}
catch
{
    Write-Host $_.Exception;
    if ($Error.Count -gt 0)
    {
        Write-Host "Error Information" -BackgroundColor Red;
        $error[0] | fl -force ;
    }
}

Write-Host "Completed!!!" -BackgroundColor DarkGreen;

Read-Host "Press any key to continue....";