SQL Server Database Migration – DB-Migrate: A PowerShell Module to Migrate Databases.

Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users.  I have now combined all of these different functions into a single powershell module named DB-Migrate.

The module combines the below tasks; to get detailed information about these tasks please go through the respective blog links.

1. Move logins from source to destination instance

2. Move databases from source to destination instance

3. Fix orphan users at destination instance

4. Move SQL Server Agent jobs from source to destination instance

The code in the above links might be different from that of functions included in DB-Migration module, however the explanation and logic is same.

Importing Module  

To import and use module function follow the below steps.

  1. Download the module code from http://sdrv.ms/10AA1M2
  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)
  3. Once you are done with step 1 and 2, open a powershell window and run below commands.
  • Import-Module DB-Migrate

This will import the module and functions into powershell

  • Get-Module  -ListAvailable

This will list down all available modules in powershell

   
  • Get-Command -Module DB-Migrate

This will list down all functions available in the module.

The output from above commands will look as shown in below snapshot.

1_DB_Migrate_A_PowerShell_Module_to_Migrate_Databases

The functions can then be used as below.

Copy-Logins:

Copy and enable logins at the destination instance

Copy-Logins -SourceInstance “MyPC\SQL2008” -destInstance “MyPC\SQL2005” -EnableLogin $true

Copy and don’t enable logins at the destination instance.

Copy-Logins -SourceInstance “MyPC\SQL2008” -destInstance “MyPC\SQL2005” -EnableLogin $false

Copy-SQLAgentJobs

Copy-SQLAgentJobs -SourceInstance “myPC\SQL2005” -DestInstance “myPC\SQL2008”

Move-Databases

Move-Databases -SourceInstance “myPC\SQL2005” -DestInstance “myPC\SQL2008”

 Fix-OrphanUsers

Fix-OrphanUsers -inst “myPC\SQL2008”

ExecuteQuery

ExecuteQuery -inst “myPC\SQL2005” -destdb “master” -query “Select 1”

WriteTolog

WriteToLog -msg “This is the message to be written to log file”

This is it. The module is very simple to user. Please feel free to test and modify the code as per your needs.

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

   

Leave a Reply

Your email address will not be published.