Wednesday, December 31, 2008

Script to migrate databases with PowerShell.

Hi,
In the last months, I've been writing some scripts in PowerShell to administer SQL Servers, this time, I will share my personal script to migrate database from one server to another.

You can test this script in your environment and see if it works, this is the scripts that I have used to migrate databases easily and quickly.

First, you have to kill all the processes in the databases and change them to read only, in order to guarantee that the data will not change during the migration.

The script read a txt file with the list of all databases to migrate, then it connects to source server, then, for every database it checks whether if it should migrate them, and takes a full backup, also it generates the scripts to re-create the database users, the script to add every user to its respective role and checks if there is any schema that its owner is different than dbo.

Then, it copies the backups to the backup server, restore it, change it to read_write, run the scripts to recreate users and add them to the roles.

One important thing is that it considers that if the data and log files are going to be different between the servers, it lets you specfy a new route, with some parameters.

The scripts take these parameters:
1. A txt file with the databases name to migrate. (for example: d:\databases.txt)
2. Path in the soruce server where the source server will write the backup (for example: d:\backups)
3. Path in the backup server where the backup server will take the backup (for example: d:\backups)
4. Path where will be written the scripts.
5. Source database server name.
6. Backup database server name.

For example:
# ./migradbs.ps1 d:\databases.txt d:\backups\ h:\backups\ d:\scripts "MYCURRENTSERVER" "MYNEWSERVER"

param (
[string]$filename=$(throw 'The parameter: $filename is required.'),
[string]$BackupPath=$(throw 'The parameter: $BackupPath is required.'),
[string]$RestorePath=$(throw 'The parameter: $RestorePath is required.'),
[string]$ScriptsPath=$(throw 'The parameter: $ScriptsPath is required.'),
[string]$OriginServer=$(throw 'The parameter: $OriginServer is required.'),
[string]$DestinyServer=$(throw 'The parameter: $DestinyServer is required.')
)
# MODIFY THIS PARAMETERS TO CHANGE THE PATHS FROM THE DATA AND LOGS.
# IF YOU LET IT WRONG, THE RESTORE WILL FAIL Y THE SCRIPT WILL EXIT WITH SOMETHING LIKE:
# Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server ?????'. "
# At :line:197 char:22 $restore.SqlRestore( <<<< $srv2)
[string]$path_data_source="d:\sql\"
[string]$path_log_source="d:\sql\"
[string]$path_data_backup="d:\sql\"
[string]$path_log_backup="d:\sql\"

if (! (test-path $filename)) {
throw "$($filename) is not a valid file!"
}

if (! (test-path $ScriptsPath)) {
throw "$($ScriptsPath) is not a valid path!"
}

$bds=Get-Content $filename | where {$_ -ne ""}
function okMigrate([string]$a) {
foreach ($bd in $bds){
if ([string]::Compare($a, $bd, $True) -eq 0){
return $true
break
}
}
return $false
}
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$MyScripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" $OriginServer
$MyScripter.Server=$srv

if (!$BackupPath.EndsWith("\")){ $BackupPath += "\" }
if (!$RestorePath.EndsWith("\")){ $RestorePath += "\" }
if (!$ScriptsPath.EndsWith("\")){ $ScriptsPath += "\" }
Write-Host "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
Write-Host " START SCRIPT TO MIGRATE DATABASES "
Write-Host "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
Write-Host "Backup path:" $BackupPath
Write-Host "Restore path:" $RestorePath
Write-Host "Scripts path:" $ScriptsPath
Write-Host "Source server:" $OriginServer
Write-Host "backup server:" $DestinyServer
Write-Host "DBS to migrate:" $bds
$mytime=get-date -uformat "%Y/%m/%d %H:%M:%S"
Write-Host "Current date and time:" $mytime
Write-Host "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"

foreach($database in $srv.databases) {
if (okMigrate($database.Name) -and $database.Name -ne "master" -and $database.Name -ne "model" -and $database.Name -ne "msdb" -and $database.Name -ne "tempdb"){
$backup=new-object "Microsoft.SqlServer.Management.Smo.Backup"
$backup.Action='Database'
$backup.Initialize='true'
$backup.Checksum='true'
$fil=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil.DeviceType='File'
$fil.Name=[System.IO.Path]::Combine($BackupPath, $database.Name + ".bak")
$backup.Devices.Add($fil)
$backup.Database=$database.Name
write-host "Start backup of"$database.Name
$backup.SqlBackup($srv)
write-host "Finished backup of"$database.Name
#write-host "Getting the script to recreate the users of" $database.Name
$f = [System.IO.Path]::Combine($ScriptsPath, "Users_" + $database.Name + ".sql")
$MyScripter.options.IncludeDatabaseContext = $false
$e = "USE " + $database.Name
out-file -filePath $f -inputobject $e
out-file -filePath $f -inputobject "GO" -Append
foreach ($User in $database.Users) {
if ($User.Name -ne "sys" -and $User.Name -ne "dbo" -and $User.Name -ne "INFORMATION_SCHEMA" -and $User.Name -ne "guest") {
$MyScripter.Options.IncludeIfNotExists = $true
$MyScripter.Options.ScriptDrops = $true
$MyScripter.Script($User) | Out-file $f -append
}
if ($User.Name -ne "sys" -and $User.Name -ne "dbo" -and $User.Name -ne "INFORMATION_SCHEMA" -and $User.Name -ne "guest") {
$MyScripter.options.IncludeDatabaseContext = $false
$MyScripter.Options.ScriptDrops = $false
$MyScripter.Script($User) | Out-file $f -append
out-file -filePath $f -inputobject "`n" -append
}
}
#write-host "Getting the script to add every user to its roles..."
$f = [System.IO.Path]::Combine($ScriptsPath, "roles_" + $database.Name + ".sql")
$e = "USE " + $database.Name
out-file -filePath $f -inputobject $e
out-file -filePath $f -inputobject "GO" -Append
foreach ($role in $database.Roles) {
if ($role.Name -ne "public") {
foreach ($member in $role.EnumMembers()) {
if ($member -ne "dbo") {
$var = "sys.sp_addrolemember @rolename = N'" + $role.Name+"', @membername = N'"+$member+"'"
out-file -filePath $f -inputobject $var -append
out-file -filePath $f -inputobject "GO" -append
}
}
}
}

#Write-Host "Getting the scripts to change the paths during restore"
ForEach ($fg in $database.FileGroups){
foreach ($var1 in $fg.Files){
$movedata = $var1.Name + "," + ($var1.FileName.ToLower()).Replace($path_data_source,$path_data_backup)
}
}
ForEach ($lf in $database.LogFiles){
$movedata_log = $lf.Name + "," + ($lf.FileName.ToLower()).Replace($path_log_source,$path_log_backup)
}
$f = [System.IO.Path]::Combine($ScriptsPath, "Relocate_" + $database.Name + ".relocate")
out-file -filePath $f -inputobject $movedata
$f = [System.IO.Path]::Combine($ScriptsPath, "Relocate_" + $database.Name + "_log.relocate")
out-file -filePath $f -inputobject $movedata_log

$SqlConnection1 = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection1.ConnectionString = "Server=$OriginServer;Database=master;Integrated Security=True"
$SqlConnection1.open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select * from $database.sys.schemas where name not in ('dbo','sys','guest','INFORMATION_SCHEMA','db_accessadmin','db_backupoperator','db_datareader','db_datawriter','db_ddladmin','db_denydatareader','db_denydatawriter','db_owner','db_securityadmin') and principal_id<>1"
$SqlCmd.Connection = $SqlConnection1
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object "System.Data.DataSet" "Table2"
$resultado = $SqlAdapter.fill($DataSet)
if ($resultado -gt 0) {
write-host "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
write-host "WARNING: There are schemas which its owner its not dbo!"
write-host "To fix the problem: Change the schema owner, BEFORE migrate"
write-host "To change the owner run: ALTER AUTHORIZATION ON SCHEMA:: TO dbo"
write-host "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
}
$DataSet.clear()
$SqlConnection1.close()
}
}
Write-Host "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
write-host " DATABASE BACKUP FINISHED "
Write-Host "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"

if ($OriginServer.Contains("\")){
$mypath1 = $OriginServer.Substring(0,$OriginServer.IndexOf("\"))
} elseif ($OriginServer.Contains(",")){
$mypath1 = $OriginServer.Substring(0,$OriginServer.IndexOf(","))
} else {
$mypath1 =$OriginServer
}
$tempSourceDir = "\\" + $mypath1+ "\" + ($BackupPath -replace(":","$"))

if ($DestinyServer.Contains("\")){
$mypath2 = $DestinyServer.Substring(0,$DestinyServer.IndexOf("\"))
} elseif ($DestinyServer.Contains(",")){
$mypath2 = $DestinyServer.Substring(0,$DestinyServer.IndexOf(","))
} else {
$mypath2 =$DestinyServer
}
$tempTargetDir = "\\" + $mypath2 + "\" + ($RestorePath -replace (":","$"))

$srv2=New-Object "Microsoft.SqlServer.Management.Smo.Server" $DestinyServer
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$DestinyServer;Database=master;Integrated Security=True"
$SqlConnection.Open()

foreach ($myfile in [System.IO.Directory]::GetFiles($tempSourceDir,"*.bak")) {
$mydbname = ($myfile.Remove(0,$tempSourceDir.Length)) -replace (".bak","")
if (okMigrate($mydbname) -and $database.Name -ne "master" -and $database.Name -ne "model" -and $database.Name -ne "msdb" -and $database.Name -ne "tempdb"){
Write-Host "Start copying of" $myfile "to" $tempTargetDir
Copy-Item $myfile -Destination $tempTargetDir
Write-Host "Finished copying"
$restore=new-object "Microsoft.SqlServer.Management.Smo.Restore"
$restore.Action='Database'
$fil2=new-object "Microsoft.SqlServer.Management.Smo.BackupDeviceItem"
$fil2.DeviceType='File'
$fil2.Name=[System.IO.Path]::Combine($RestorePath,$mydbname+".bak")
$restore.Database=$mydbname
$restore.ReplaceDatabase='True'
$restore.Checksum='True'
$restore.Devices.Add($fil2)
#$restore.RelocateFiles.Clear()
$relocate_data=Get-Content ($ScriptsPath + "Relocate_" + $mydbname + ".relocate")
$DataFile=$relocate_data.Split(",")
$relocateDataFile=new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")($DataFile[0],$DataFile[1])
$restore.RelocateFiles.Add($relocateDataFile) | Out-Null
$relocate_log=Get-Content ($ScriptsPath + "Relocate_" + $mydbname + "_log.relocate")
$LogFile=$relocate_log.Split(",")
$relocateLogFile=new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")($LogFile[0],$LogFile[1])
$restore.RelocateFiles.Add($relocateLogFile) | Out-Null
Write-Host "Empieza el restore de"$mydbname
$restore.SqlRestore($srv2)
Write-Host "Termina el restore de"$mydbname
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
Write-Host "Changing" $mydbname "to Read_Write"
$SqlCmd.CommandText ="ALTER DATABASE $mydbname SET READ_WRITE"
$SqlCmd.ExecuteNonQuery() | Out-Null
Write-Host "Recreating users of"$mydbname
[diagnostics.process]::start("sqlcmd.exe","-S$DestinyServer -E -i`"" + $ScriptsPath + "Users_" + $mydbname + ".sql`"").WaitForExit()
Write-Host "Adding users of"$mydbname "to its roles"
[diagnostics.process]::start("sqlcmd.exe","-S$DestinyServer -E -i`"" + $ScriptsPath + "roles_" + $mydbname + ".sql`"").WaitForExit()
$srv2.Refresh()
}
Write-Host "Finished restore of"$mydbname
}
$SqlConnection.Close()
Write-Host "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
Write-host " END OF SCRIPT "
Write-Host "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
$mytime=get-date -uformat "%Y/%m/%d %H:%M:%S"
Write-Host "Current date and time:" $mytime

No comments: