SQL Server Database Migration between sql instances is a time taking and a tedious task which becomes even more complicated when one has more than 10-12 databases to migrate.

This PowerShell module aims to automate manual task involved in SQL Server Database Migration.  A typical database migration involves below steps.

  1. Move Logins between instances
  2. Move database between instances
  3. Fix orphan users at destination instance
  4. Move SQL Agent Jobs

Installing Module

Follow below steps to setup the module

  1. Download the module code DB-Migrate V 5.0 
  2. Save the DB-Migrate.psm1 file at any of the below location %windir%\System32\WindowsPowerShell\v1.0\Modules OR %UserProfile%\Documents\WindowsPowerShell\Modules (preferred)

Once you are done with step 1 and 2, open a PowerShell window and run commands as listed below.

  1. Import-Module: Imports module into PowerShell and exposes the available functions to be used.
  2. Get-Module: Lists all available modules
  3. Get-Command: List all available function in a specific module

Figure1 shows the output of above commands when run in a PowerShell console

1_DB_Migrate_V 5.0_PowerShell_Module_to_Migrate_Databases

Figure 1

Let’s now see how each of the above steps is automated.

– Move Logins between Instance

– Function Name: Copy-Logins

– Description: Copies logins from the source to destination instance.

– Parameters:

– SourceInstance:  The source instance from where the logins are to be copied.

– destInstance: The destination instance where the logins are to be copied.

– EnableLogin:  Boolean parameter to enable logins at destination once copied. Default is false. The copied logins are disabled.

– Execution Example

To move all logins from Ahmad-PC\SQL2012 to Ahmad-PC\SQL2014 execute as below

– Copy-Logins -SourceInstance AHMAD-PC\SQL2012 -destInstance AHMAD-PC\SQL2014 |Out-Null

– Copy-Logins -SourceInstance AHMAD-PC\SQL2012 -destInstance AHMAD-PC\SQL2014 -EnableLogin $True |Out-Null

Figure 2 shows the result of above commands when executed in PowerShell console

2_DB_Migrate_V 5.0_PowerShell_Module_to_Migrate_Databases

Figure 2

– Move Database between Instances

– Function Name: Copy-Databases

– Description: Copies user, non-mirrored active databases from source to destination instance using detach/attach method.

– Parameters

– SourceInstance: The source instance from where the databases are to be copied.\

– DestInstance: The destination instance where the databases are to be copied.

– DatabaeName: Specify a database name to move instead of moving all user databases.

– IsCopy: Boolean variable. True means that database files are to be copied to new location.

– IsDelete: Boolean variable. Deletes the database files from the source Instance when set to true.

– Datafilepath: The new data (*.mdf and *.ndf) file path.

– Logfilepath: The new log (*.ldf) file path.

– DatafileCopyPath: The new data (*.mdf and *.ndf) file path. If files are to be copied to a folder in local computer then it’s same as “datafilepath” parameter. If the files are to be    copied to remote computer  specify a UNC path.

– logfileCopyPath: The new data (*.ldf) file path. If files are to be copied to a folder in local computer then it’s same as “logfilepath” parameter. If the files are to be copied to remote    computer specify a UNC path.

– Execution Example

– Copy all databases from source to destination

Copy-Databases -SourceInstance Ahmad-pc\sql2012 -DestInstance Ahmad-pc\sql2014

3_DB_Migrate_V 5.0_PowerShell_Module_to_Migrate_Databases

Figure 3

– Copy specific database from source to destination instance

Copy-Databases -SourceInstance Ahmad-pc\sql2012 -DestInstance Ahmad-pc\sql2014 -DatabaseName DBSQL2012

4_DB_Migrate_V 5.0_PowerShell_Module_to_Migrate_Databases

Figure 4

– Copy a particular database to different location

Copy-Databases -SourceInstance Ahmad-pc\sql2012 -DestInstance Ahmad-pc\sql2014 -DatabaseName DBS

QL20121 -IsCopy $true -datafilepath “D:\databasefiles” -logfilepath “D:\databasefiles” -DatafileCopyPath “D:\databasefiles” -logfileCopyPath “D:\databasefiles” |Out-Null

5_DB_Migrate_V 5.0_PowerShell_Module_to_Migrate_Databases

Figure 5

– Function Name: Copy-DatabaseHighertoLowerVersion

– Description: Copies objects and data from source to destination instance.  Detach/Attach and backup/Restore doesn’t works when copying database from Higher to lower sql server version.    This function is used when copying database from higher to lower version.

– Parameters:

– Srcinstance: The source instance from where the database is to be copied.

– Destinstance: The destination instance where the database is to be copied.

– Srcdatabase: The source database to be copied.

– Destdatabase: The destination database to be copied from source database.

– Execution Example

Copy-DatabaseHighertoLowerVersion -srcinstance “AHMAD-PC\SQL2014” -srcdatabase “Adventworks” -destinstance “AHMAD-PC\SQL2012” -destdatabase “AdventWorks” |Out-Null

6_DB_Migrate_V 5.0_PowerShell_Module_to_Migrate_Databases

Figure 6

– Fix orphan users at destination instance

– Function Name: Fix-OrphanUsers

– Description: The function fixes orphan users at the specified instance. Only users with same name as that of login are fixed.

– Parameters

– Inst: The sql server instance on which users are to be fixed.

– Execution Example

Fix-OrphanUsers -inst Ahmad-PC\SQL2014

7_DB_Migrate_V 5.0_PowerShell_Module_to_Migrate_Databases

Figure 7

– Move SQL Agent Jobs

– Function Name: Copy-SQLAgentJobs

– Description: It scripts out the jobs at source instance into a sql file and executes the script on the destination instance.

– Parameters

– SourceInstance: The sql server instance from which jobs are to be copied.

– DestInstance; The sql server instance to which jobs are to be copied.

–  Execution Example

Copy-SQLAgentJobs -SourceInstance Ahmad-PC\SQL2012 -DestInstance Ahmad-pc\sql2014 |Out-Null

8_DB_Migrate_V 5.0_PowerShell_Module_to_Migrate_Databases

This is all about installing and getting started with DB-Migrate module.

 

Regards

Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook