Guest blog post by Ahmad Osama
Database Migration Module
Migrating databases 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 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 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)
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
Figure 1
Let’s now see how each of the above steps is automated.
1. Move Logins between Instance
- Function Name: Copy-Logins
- Description: Copies logins from the source to destination instance.
- Parameters:
o SourceInstance: The source instance from where the logins are to be copied.
o destInstance: The destination instance where the logins are to be copied.
o 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
o Copy-Logins -SourceInstance AHMAD-PC\SQL2012 -destInstance AHMAD-PC\SQL2014 |Out-Null
o 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
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
o SourceInstance: The source instance from where the databases are to be copied.
o DestInstance: The destination instance where the databases are to be copied.
o DatabaeName: Specify a database name to move instead of moving all user databases.
o IsCopy: Boolean variable. True means that database files are to be copied to new location.
o IsDelete: Boolean variable. Deletes the database files from the source Instance when set to true.
o Datafilepath: The new data (*.mdf and *.ndf) file path.
o Logfilepath: The new log (*.ldf) file path.
o 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.
o 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
o Copy all databases from source to destination
Copy-Databases -SourceInstance Ahmad-pc\sql2012 -DestInstance Ahmad-pc\sql2014
Figure 3
o Copy specific database from source to destination instance
Copy-Databases -SourceInstance Ahmad-pc\sql2012 -DestInstance Ahmad-pc\sql2014 -DatabaseName DBSQL2012
Figure 4
o 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:
o Srcinstance: The source instance from where the database is to be copied.
o Destinstance: The destination instance where the database is to be copied.
o Srcdatabase: The source database to be copied.
o Destdatabase: The destination database to be copied from source database.
- Execution Example
o Copy-DatabaseHighertoLowerVersion -srcinstance "AHMAD-PC\SQL2014" -srcdatabase "Adventworks" -destinstance "AHMAD-PC\SQL2012" -destdatabase "AdventWorks" |Out-Null
Figure 6
3. 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
o Inst: The sql server instance on which users are to be fixed.
- Execution Example
o Fix-OrphanUsers -inst Ahmad-PC\SQL2014
4. 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
o SourceInstance: The sql server instance from which jobs are to be copied.
o DestInstance; The sql server instance to which jobs are to be copied.
- Execution Example
o Copy-SQLAgentJobs -SourceInstance Ahmad-PC\SQL2012 -DestInstance Ahmad-pc\sql2014 |Out-Null
This is all about installing and getting started with DB-Migrate module.
Thanks and Regards
Ahmad Osama
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.