Skip to content

Automate SQL Database Migration Using PowerShell from Source to Destination

  • by

Make a quick fix for automating database migration across SQL Server versions using PowerShell and T-SQL scripting. Before moving on to a set of customised actions, I need to find several databases that can be moved using a standard procedure or at the very least a collection of standard activities. You could want to write general migration scripts and set up a system to automate their execution in these circumstances. A general method like this can be quite helpful if you’re going to iterate through several test sessions and want to migrate a fresh copy before each one.
The following actions can be made to simplify the migrating process:

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.

I’m automating the first two Point’s, databases today for backup and restore. I still have to finish automating the other points, but I’m working on it. I’m hoping that by sharing this backup and restore PowerShell script today, some of the DBAs may find it useful.
You will use handlers that SMO will immediately call back when you execute backup and restore commands in T-SQL, and you will provide a straightforward progress notification similar to the ones offered by SSMS or sqlcmd. Please remember that the completion handler does nothing more than display the error.

A handful of the PowerShell script’s functions include Get-SQLInstance, DatabaseFullBackup, and RestoreDatabaseFromFullBackup.

Click the link below top open the PowerShell script that will implement backup/restore procedures for each database which we want to migrate to complete the migration :

A few variables in the script above, including SourceInstanceName, Destination Instance Name, Backup Path, DATA File Path, LOG File Path, and Databases Names, will need to be updated.
The script will start taking database backups from the source instance and restoring them to the destination instance once all the parameters have been customised to your environment.

Additionally, my destination instance doesn’t have any databases. Using the aforementioned PowerShell script, I’m currently backing up and restoring my “DB1,” “DB2,” and “DB3” databases. When I run the script, it first takes a backup of the first database, restores it to the destination instance once the backup is finished, and then takes a backup of the second database. It starts restoring the second database once the backup is finished.

As soon as the DB1 Database backup is complete, the destination DB1 Database is instantly restored. After the restoration is complete, the backup for the following database (the DB2 database) is initiated, etc.,
Migration of the databases I mentioned in the Databases Field was completed by the script.

The migration of all three databases from the source instance to the destination instance has been completed successfully. Now all you have to do to migrate your given databases is to run the script in PowerShell ISE.

Leave a Reply

%d bloggers like this: