forked from bseltz-cohesity/scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcloneSQL.ps1
More file actions
216 lines (195 loc) · 8.21 KB
/
cloneSQL.ps1
File metadata and controls
216 lines (195 loc) · 8.21 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
### usage: ./cloneSQL.ps1 -vip 192.168.1.198 -username admin [ -domain local ] -sourceServer 'SQL2012' -sourceDB 'CohesityDB' [ -targetServer 'SQLDEV01' ] [ -targetDB 'CohesityDB-Dev' ] [ -targetInstance 'MSSQLSERVER' ] [ -wait ]
### process commandline arguments
[CmdletBinding()]
param (
[Parameter(Mandatory = $True)][string]$vip, # the cluster to connect to (DNS name or IP)
[Parameter(Mandatory = $True)][string]$username, # username (local or AD)
[Parameter()][string]$domain = 'local', # local or AD domain
[Parameter(Mandatory = $True)][string]$sourceServer, # protection source where the DB was backed up
[Parameter(Mandatory = $True)][string]$sourceDB, # name of the source DB we want to clone
[Parameter()][string]$targetServer = $sourceServer, # where to attach the clone DB
[Parameter()][string]$targetDB = $sourceDB, # desired clone DB name
[Parameter()][string]$targetInstance = 'MSSQLSERVER', # SQL instance name on the targetServer
[Parameter()][string]$logTime, # point in time log replay like '2019-09-29 17:51:01'
[Parameter()][switch]$wait, # wait for clone to finish
[Parameter()][switch]$latest, # very latest point in time log replay
[Parameter()][string]$password = $null # optional! clear text password
)
### source the cohesity-api helper code
. $(Join-Path -Path $PSScriptRoot -ChildPath cohesity-api.ps1)
### authenticate
apiauth -vip $vip -username $username -domain $domain -password $password
### search for database to clone
$searchresults = api get /searchvms?environment=SQL`&entityTypes=kSQL`&entityTypes=kVMware`&vmName=$sourceDB
### handle source instance name e.g. instance/dbname
if($sourceDB.Contains('/')){
$sourceDB = $sourceDB.Split('/')[1]
}
### narrow the search results to the correct source server
$dbresults = $searchresults.vms | Where-Object {$_.vmDocument.objectAliases -eq $sourceServer }
if($null -eq $dbresults){
write-host "Server $sourceServer Not Found" -foregroundcolor yellow
exit
}
### narrow the search results to the correct source database
$dbresults = $dbresults | Where-Object { $_.vmDocument.objectId.entity.sqlEntity.databaseName -eq $sourceDB }
if($null -eq $dbresults){
write-host "Database $sourceDB Not Found" -foregroundcolor yellow
exit
}
### if there are multiple results (e.g. old/new jobs?) select the one with the newest snapshot
$latestdb = ($dbresults | sort-object -property @{Expression={$_.vmDocument.versions[0].snapshotTimestampUsecs}; Ascending = $False})[0]
if($null -eq $latestdb){
write-host "Database Not Found" -foregroundcolor yellow
exit
}
### identify physical or vm
$entityType = $latestdb.registeredSource.type
### search for source and target servers
$entities = api get /appEntities?appEnvType=3`&envType=$entityType
$ownerId = $latestdb.vmDocument.objectId.entity.sqlEntity.ownerId
$targetEntity = $entities | where-object { $_.appEntity.entity.displayName -eq $targetServer }
if($null -eq $targetEntity){
Write-Host "Target Server Not Found" -ForegroundColor Yellow
exit
}
### handle log replay
$versionNum = 0
$validLogTime = $False
if ($logTime -or $latest) {
if($logTime){
$logUsecs = dateToUsecs $logTime
}
$dbVersions = $latestdb.vmDocument.versions
foreach ($version in $dbVersions) {
### find db date before log time
$GetRestoreAppTimeRangesArg = @{
'type' = 3;
'restoreAppObjectVec' = @(
@{
'appEntity' = $latestdb.vmDocument.objectId.entity;
'restoreParams' = @{
'sqlRestoreParams' = @{
'captureTailLogs' = $false;
'newDatabaseName' = $sourceDB;
'alternateLocationParams' = @{};
'secondaryDataFileDestinationVec' = @(@{})
};
'oracleRestoreParams' = @{
'alternateLocationParams' = @{}
}
}
}
);
'ownerObjectVec' = @(
@{
'jobUid' = $latestdb.vmDocument.objectId.jobUid;
'jobId' = $latestdb.vmDocument.objectId.jobId;
'jobInstanceId' = $version.instanceId.jobInstanceId;
'startTimeUsecs' = $version.instanceId.jobStartTimeUsecs;
"entity" = @{
"id" = $ownerId
};
'attemptNum' = 1
}
)
}
$logTimeRange = api post /restoreApp/timeRanges $GetRestoreAppTimeRangesArg
if($latest){
if(! $logTimeRange.ownerObjectTimeRangeInfoVec[0].PSobject.Properties['timeRangeVec']){
$logTime = $null
$latest = $null
break
}
}
$logStart = $logTimeRange.ownerObjectTimeRangeInfoVec[0].timeRangeVec[0].startTimeUsecs
$logEnd = $logTimeRange.ownerObjectTimeRangeInfoVec[0].timeRangeVec[0].endTimeUsecs
if($latest){
$logUsecs = $logEnd - 1000000
$validLogTime = $True
break
}
if ($logStart -le $logUsecs -and $logUsecs -le $logEnd) {
$validLogTime = $True
break
}
$versionNum += 1
}
}
$taskName = "dbClone-$(dateToUsecs (get-date))"
### create new clone task (RestoreAppArg Object)
$cloneTask = @{
"name" = $taskName;
"action" = "kCloneApp";
"restoreAppParams" = @{
"type" = 3;
"ownerRestoreInfo" = @{
"ownerObject" = @{
"jobUid" = $latestdb.vmDocument.objectId.jobUid;
"jobId" = $latestdb.vmDocument.objectId.jobId;
"jobInstanceId" = $latestdb.vmDocument.versions[0].instanceId.jobInstanceId;
"startTimeUsecs" = $latestdb.vmDocument.versions[0].instanceId.jobStartTimeUsecs;
"entity" = @{
"id" = $ownerId
}
}
"ownerRestoreParams" = @{
"action" = "kCloneVMs";
"powerStateConfig" = @{}
};
"performRestore" = $false
}
"restoreAppObjectVec" = @(
@{
"appEntity" = $latestdb.vmDocument.objectId.entity;
"restoreParams" = @{
"sqlRestoreParams" = @{
"captureTailLogs" = $false;
"instanceName" = $targetInstance;
"newDatabaseName" = $targetDB;
}
'targetHost' = $targetEntity.appEntity.entity;
'targetHostParentSource' = @{
'id' = $targetEntity.appEntity.entity.parentId;
}
}
}
)
}
}
### apply log replay time
if($validLogTime -eq $True){
$cloneTask.restoreAppParams.restoreAppObjectVec[0].restoreParams.sqlRestoreParams['restoreTimeSecs'] = $([int64]($logUsecs/1000000))
}else{
if($logTime){
Write-Host "LogTime of $logTime is out of range" -ForegroundColor Yellow
Write-Host "Available range is $(usecsToDate $logStart) to $(usecsToDate $logEnd)" -ForegroundColor Yellow
exit 1
}
}
### execute the clone task (post /cloneApplication api call)
$response = api post /cloneApplication $cloneTask
if($response){
$taskId = $response.restoreTask.performRestoreTaskState.base.taskId
"Cloning $sourceDB to $targetServer as $targetDB (task name: $taskName)"
}else{
Write-Warning "No Response"
exit(1)
}
if($wait){
$status = 'started'
$finishedStates = @('kCanceled', 'kSuccess', 'kFailure')
while($status -ne 'completed'){
$task = api get "/restoretasks/$($taskId)"
$publicStatus = $task.restoreTask.performRestoreTaskState.base.publicStatus
if($publicStatus -in $finishedStates){
$status = 'completed'
}else{
sleep 3
}
}
write-host "Clone task completed with status: $publicStatus"
if($publicStatus -eq 'kFailure'){
write-host "Error Message: $($task.restoreTask.performRestoreTaskState.base.error.errorMsg)"
}
}