다음을 통해 공유

Automate Database Migration between SQL Server Instances

  • 아티클

Database Migration Module

Migrating databases between 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 migrating a database.  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 file http://1drv.ms/MAOeK7
  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

Above commands when run in PowerShell console returns result as shown in below image.

Function Details

  • Move logins between Instance
    1. Function Name: Copy-Logins
    2. Description: Copies logins from the source to destination instance. 
    3. Parameters:
      1. SourceInstance:  The source instance from where the logins are to be copied. 
      2. DestInstance: The destination instance where the logins are to be copied.
      3. EnableLogin:  Boolean parameter to enable logins at destination once copied. Default is false. The copied logins are disabled.
    4. Execution Example
      • To move all logins from ServerName\SQL2012 to ServerName\SQL2014 execute as below
      • Copy-Logins -SourceInstance ServerName\SQL2012 -destInstance ServerName\SQL2014 |Out-Null
      • Copy-Logins -SourceInstance ServerName\SQL2012 -destInstance ServerName\SQL2014 -EnableLogin $True |Out-Null
  • Move Database between Instances       
    1. Function Name: Copy-Databases
    2. Description: Copies user, non-mirrored active databases from source to destination instance using detach/attach method. Can only be used to copy databases from lower to higher SQL Server version.
    3. Parameters
      1. SourceInstance: The source instance from where the databases are to be copied.
      2. DestInstance: The destination instance where the databases are to be copied.
      3. DatabaeName: Specify a database name to move instead of moving all user databases.
      4. IsCopy: Boolean variable. True means that database files are to be copied to new location.
      5. IsDelete: Boolean variable. Deletes the database files from the source Instance when set to true.
      6. Datafilepath: The new data (*.mdf and *.ndf) file path.
      7. Logfilepath: The new log (*.ldf) file path.
      8. 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.
      9. 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.
    4. Execution Example
      • To Copy all databases from source to destination instance
      • Copy-Databases -SourceInstance ServerName\sql2012 -DestInstance ServerName\sql2014
      • To Copy specific database from source to destination instance
      • Copy-Databases -SourceInstance ServerName\sql2012 -DestInstance ServerName\sql2014 -DatabaseName DBSQL2012
      • To Copy a particular database to different location
      • Copy-Databases -SourceInstance ServerName\sql2012 -DestInstance ServerName\sql2014 -DatabaseName DBSQL20121 -IsCopy $true -datafilepath "D:\databasefiles" -logfilepath "D:\databasefiles" -DatafileCopyPath "D:\databasefiles" -logfileCopyPath "D:\databasefiles" |Out-Null
  • Move Database from higher to lower SQL Server version
    1. Function Name: Copy-DatabaseHighertoLowerVersion
    2. 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.
    3. Parameters:
      1. Srcinstance: The source instance from where the database is to be copied
      2. Destinstance: The destination instance where the database is to be copied.
      3. Srcdatabase: The source database to be copied.
      4. Destdatabase: The destination database to be copied from source database.
    4. Execution Example
      • Copy-DatabaseHighertoLowerVersion -srcinstance "ServerName\SQL2014" -srcdatabase "Adventworks" -destinstance "ServerName\SQL2012" -destdatabase "AdventWorks" |Out-Null
  • Fix orphan users at destination instance
    1. Function Name: Fix-OrphanUser
    2. Description: The function fixes orphan users at the specified instance. Only users with same name as that of login are fixed.
    3.  Parameters: 
      1. Inst: The sql server instance on which users are to be fixed.
    4. Execution Example
      1. Fix-OrphanUsers -inst ServerName\SQL2014
  • Move SQL Agent Jobs 

    1. Function Name: Copy-SQLAgentJobs

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

    3. Parameters

      1. SourceInstance: The SQL server instance from which jobs are to be copied.
      2. DestInstance: The SQL server instance to which jobs are to be copied.

    4.           Execution Example

      • Copy-SQLAgentJobs -SourceInstance ServerName\SQL2012 -DestInstance ServerName\sql2014 |Out-Null

Conclusion 

The article listed down the functions to automate the tedious task of database migration. It might not be a complete end to end automated solution but it can be helpful in avoiding and speeding up lot of manual work.