Automate Migrating Databases From Source to Destination USing PowerShell

Using a combination of PowerShell and T-SQL scripting, create a simple solution for automating database migration across SQL Server versions. In my instance, I need to locate multiple databases that can be migrated using a standard procedure or at the very least a set of standard activities before proceeding to a set of customized actions. In these situations, you may want to create generic migration scripts and create a system to automate their execution. If you’re going to iterate through numerous test sessions and want to migrate a new copy before each one, a general technique like this can be extremely useful.

The activities that can be taken to make the migration process easier are as follows:

1) The backup database from the Source Instance will be transferred.
2) Restore the database that will be moved to the new instance.
3) Refresh the allocation usage.
4) Data purity can be used to check database consistency.
5) Set the level of page verification.
6) Set the database compatibility level to match the version of the destination instance (e.g. 120).
7) Statistics should be updated.
8) Remove any orphaned schemas (schemas automatically created for each database user to maintain compatibility but with no objects belonging to them).
9) Fix Orphaned Users if any exists.
10) Run Rebuild or re-organize all databases based on Migration Change window and the databases size.

Today, I’m automating the databases backup and restore of the first two Point. I still need to work on automating the other points, which are currently in process. I’m hoping that this backup and restore PowerShell script may be useful to some of the DBAs, and that I can share it now.

When you issue backup and restore commands in T-SQL, you’ll use handlers that are immediately called back by SMO, and you’ll provide a simple progress notification like the one provided by SSMS or sqlcmd. Please keep in mind that the completion handler just displays the error message.
Get-SQLInstance, DatabaseFullBackup, and RestoreDatabaseFromFullBackup are only a few of the functions in the Powershell script.

Below is the PowerShell script that will implement backup/restore procedures for each database which we want to migrate to complete the migration :

#region Load Assemblies and Global Error Handling
[System.Reflection.Assembly]::LoadWithPartialName( `
	"Microsoft.SqlServer.SMO") | Out-Null;
[System.Reflection.Assembly]::LoadWithPartialName( `
	"Microsoft.SqlServer.SMOExtended") | Out-Null;

# Simple global exception handling to see SQL Server errors
trap {
		$Exc = $_.Exception;
		while ( $Exc.InnerException )
		{
			$Exc = $Exc.InnerException;
			Write-Warning ("(generic trap) " + $Exc.Message);
		};
		break;
};
#endregion

#region Event Handlers
$PercentCompleteHandler = `
	[Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] `
	{ 
		Write-Host ([string]$_.Percent + " percent processed.");
	};
$CompleteHandler = `
	[Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] `
	{
		Write-Host $_.Error.Message;
	};
#endregion

#region Functions definition
function Get-SQLInstance($InstanceName, $Login, $Password)
{
	$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" `
							  $InstanceName;
	if ($Login -eq $null) {
		$SQLInstance.ConnectionContext.LoginSecure = $true;
	}
	else {
		$SQLInstance.ConnectionContext.LoginSecure = $false;
		$SQLInstance.ConnectionContext.Login = $Login;
		$SQLInstance.ConnectionContext.Password = $Password;
	};
	# Force connection to get an early error message
	$SQLInstance.ConnectionContext.Connect();
	return $SQLInstance;
};
function DatabaseFullBackup ($SQLInstance, $DatabaseName, $BackupPath)
{
	$Backup = New-Object "Microsoft.SqlServer.Management.Smo.Backup";
	$Backup.Action = "Database";
	$Backup.Database = $DatabaseName;
	$Backup.Initialize = $true;
	$Backup.CopyOnly = $true;
	$Backup.Devices.AddDevice($BackupPath + "\" + $DatabaseName + ".bak" `
								, "File");

	$Backup.add_PercentComplete($PercentCompleteHandler);
	$Backup.add_Complete($CompleteHandler);

	$Backup.SqlBackup($SQLInstance)
};
function RestoreDatabaseFromFullBackup ($DestinationSQLInstance, $DatabaseName `
								, $BackupPath, $DataFilesPath, $LogFilesPath)
{
	$Restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore";
	$Restore.FileNumber = 1;
	$Restore.Devices.AddDevice($BackupPath + "\" + $DatabaseName + ".bak"`
								, "File");

	foreach ($File in $Restore.ReadFileList($DestinationSQLInstance))
	{
		$NewFile = New-Object "Microsoft.SqlServer.Management.Smo.relocatefile";
		$NewFile.LogicalFileName = $File.LogicalName;
		
		#Primary Data File
		if	($File.FileID -eq 1 -and $DataFilesPath -ne "")
		{	
			$NewFile.PhysicalFileName = ($DataFilesPath + "\" + $DatabaseName `
											+ "_" + $File.LogicalName + ".mdf");
		}
		#Secondary Data File
		elseif	($File.Type -eq "D" -and $DataFilesPath -ne "")
		{	   
			$NewFile.PhysicalFileName = ($DataFilesPath + "\" + $DatabaseName`
											+ "_" + $File.LogicalName + ".ndf");
		}
		#Log File
		elseif	($File.Type -eq "L" -and $LogFilesPath -ne "")
		{
			$NewFile.PhysicalFileName = ($LogFilesPath + "\" + $DatabaseName `
											+ "_" + $File.LogicalName + ".ldf");
		};

		if ($NewFile.PhysicalFileName -ne $null) {
			[void]$Restore.RelocateFiles.add($Newfile);
		};
	};
	
	$Restore.Database = $DatabaseName;
	$Restore.ReplaceDatabase = $true;
	$Restore.NoRecovery = $false;
	$Restore.Action = "Database";

	$Restore.add_PercentComplete($PercentCompleteHandler);
	$Restore.add_Complete($CompleteHandler);

	$Restore.SqlRestore($DestinationSQLInstance); 
};
#endregion

#region Main
#Parameters
$SourceSQLInstanceName = "DESKTOP-02JIB76";                    # Mention your Source Instance Name 
$DestinationSQLInstanceName = "DESKTOP-02JIB76\INS17";         # Mention your Destination InstanceName

$BackupPath = "E:\Backup\INS17\";                              # Mention your Backup Path which can be your Local Path or Shared Path

$DataFilesPath = "D:\INS17\DATA";                              # Mention your Destination DATA File Path
$LogFilesPath = "D:\INS17\LOG";                                # Mention your Destination LOG File Path

$DatabaseNames = "BLOB","panda","dbareports";                  # Mention your Databases Names which are needed to Migrate

#Main
$SourceSQLInstance = Get-SQLInstance $SourceSQLInstanceName;
$DestinationSQLInstance = Get-SQLInstance $DestinationSQLInstanceName;

foreach ($DatabaseName in $DatabaseNames)
{
	Write-Host ("`nBackup database [" + $DatabaseName + "]");
	DatabaseFullBackup 	$SourceSQLInstance $DatabaseName $BackupPath;

	Write-Host ("`nRestore database [" + $DatabaseName + "]");
	RestoreDatabaseFromFullBackup 	$DestinationSQLInstance $DatabaseName `
									$BackupPath $DataFilesPath $LogFilesPath;

};
#endregion

You’ll need to update a few parameters in the script above, such as SourceInstanceName, Destination Instance Name, Backup Path, DATA File Path, LOG File Path and the Databases Names .

Once all of the parameters have been adjusted to your environment, the script will begin taking database backups from the source instance and restoring them to destination instance.

You can see below snippet where I don;t have any backup files , any Data and Log files:

Also I don’t have any Databases in my Destination Instance.

Now I’m backing up and restoring my “BLOB”,”panda”,”dbareports” databases with the PowerShell Script above. When I run the script, it will back up the first database, restore it to the Destination Instance after the backup is complete, and then take backups for the second database,┬áIt will begin restoring after the backup has been completed.

You can see in above Screenshot when Panda Database backup completed immediately it start s to Restore Panda Database in Destination , once the restore completed it will start backup for next Database (dbareports database) and will restore

The script completed migration of databases which I mentioned the DatabasesField

All three databases have been successfully migrated from the Source Instance to the Destination Instance. Now all you have to do is run the script in PowerShell ISE and it will take care of migrating your provided databases.