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.
- Download the module code from http://sdrv.ms/10AA1M2
- 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 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.
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
