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.
- Move Logins between instances
- Move database between instances
- Fix orphan users at destination instance
- Move SQL Agent Jobs
Installing Module
Follow below steps to setup the module
- Download the module code DB-Migrate V 5.0
- 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.
- Import-Module: Imports module into PowerShell and exposes the available functions to be used.
- Get-Module: Lists all available modules
- Get-Command: List all available function in a specific module
Figure1 shows the output of above commands when run in a PowerShell console
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
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
Figure 3
– Copy specific database from source to destination instance
Copy-Databases -SourceInstance Ahmad-pc\sql2012 -DestInstance Ahmad-pc\sql2014 -DatabaseName DBSQL2012
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
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
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
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
This is all about installing and getting started with DB-Migrate module.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
