Hi Vladimir,
I don't think the export-VESQLDatabase would suffice, because that is intended to export the database to a bak file, which you may restore to sql of course but that is a 2-way step as the script ran on Update3a perfectly
So I have 2 remarks:
- the original commands were not only designated obsolete but changed as well, which I believe is not the correct way of implementing new commandlets.
- the restore-VESQLDatabase commandlet is (for so far I can see) not functioning properly
- I raised a ticket (03352607) and the reply I got were links to the Update3a help sites which is lacking the new commandlets
btw for any people using
pre Update4 here is the complete script:
Code: Select all
#Ends any current transcripts and starts the Auto-DB-Restore script silencing any errors
$ErrorActionPreference="SilentlyContinue"
$Startdate=get-date
$resulttotal = $null
$maildestination= "address@mailfqdn.com"
$mailserver = "mail server fqdn"
$sender="originalsender@mailfqdn.com"
$subject = "Sync databases from Production to Test"
$EventSource="Restore-Databases-to-Test"
if (! [System.Diagnostics.EventLog]::SourceExists($EventSource)) {
New-EventLog -LogName Application -Source $EventSource
}
write-eventlog -logname Application -Source "Restore-Databases-to-Test" -entrytype Information -eventid 1000 -message "Refresh Script started"
#Edit restore details below: (names should be in " ")
$SourceDBs = "Comma separated array of databases to be restored
$SourceVM = "SourceVMNAME"
$BackupJob = "Backup Job name"
#Adds VeeamPSSnapIn
Add-PSSnapin VeeamPSSnapIn -ErrorAction SilentlyContinue
<#
Destination Database Server and Instance (when using the defaul instance please use a space within quotes"
#Target DB name (in order to keep the same name of original database)
#For testing - Target DB name used when adding a date to the end of target DB name (this is currently not in use - removed the # symbol and then place a # symbol in the Target DB name option above)
#$targetDB = "{0}-{1}" -f $sourceDB, (Get-Date -Format yyyMMdd)
#>
Import-Module SQLSERVER
$targetVM = "TargetVMNAME"
$targetInstance = "TargetInstance"
Set-Location SQLSERVER:\SQL\$TargetVM\$targetInstance
#If any SQL Users needs to added to the databaseEasy update and insert rights aswell
$SQLUsers="SQLUser"
$DomainUsers="Domain\username"
#THIS IS WHERE THE SCRIPT ACTUALLY STARTS WORKING
#*****TARGET CREDENTIALS - LEAVE INTACT*****
$targetCreds = Get-VBRCredentials -Name "VBR ServiceAccount"
$RestorePoint = Get-VBRRestorePoint -Backup $backupJob -Name "$sourceVM" | Sort-Object creationtime -Descending | Select-Object -First 1
foreach ($sourceDB in $SourceDBs){
$Database = Get-VBRSQLDatabase -ApplicationRestorePoint $restorePoint -Name $sourceDB
$RestoreSession = Start-VBRSQLDatabaseRestore -force -wait -Database $database -ServerName $targetVM -InstanceName $targetInstance -GuestCredentials $targetCreds -SqlCredentials $targetCreds
$RestoreStatus = Get-VBRRestoreSession -id $restoreSession.Id
Invoke-Sqlcmd "USE [$SourceDB];DBCC SHRINKFILE (2,TRUNCATEONLY)"
# Add sql users to restored databases for XXX
foreach ($SQLUser in $SQLUSERS) {
Invoke-SqlCmd "USE [$SourceDB]; CREATE USER $SQLUSER FOR LOGIN $SQLUSER"
Invoke-Sqlcmd "USE [$SourceDB]; EXEC sp_addrolemember N'db_datareader', N'$SQLUSER'"
Invoke-Sqlcmd "USE [$SourceDB]; GRANT INSERT ON OBJECT::dbo.Table1 TO $SQLUSER"
Invoke-Sqlcmd "USE [$SourceDB]; GRANT UPDATE ON OBJECT::dbo.Table2 TO $SQLUSER"
Invoke-Sqlcmd "USE [$SourceDB]; GRANT UPDATE ON OBJECT::dbo.Table3 TO $SQLUSER"
}
foreach ($DomainUser in $DomainUsers) {
Invoke-SqlCmd "USE [$SourceDB]; CREATE USER [$DomainUser] FOR LOGIN [$DomainUser]"
Invoke-Sqlcmd "USE [$SourceDB]; EXEC sp_addrolemember N'db_datareader', [$DomainUser]"
invoke-Sqlcmd "USE [$SourceDB]; GRANT EXECUTE ON dbo.Table4 TO [$DomainUser]"
}
$result = "Database $sourceDB restore which started at: " + $RestoreStatus.CreationTime + " and ended at: " + $RestoreStatus.EndTime+ " ended with result: " + $RestoreStatus.result
switch ($RestoreStatus.Result){
"Succes" {write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Information -eventid 1000 -message $result}
"Warning"{write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Warning -eventid 5001 -message $result}
"Failed" {write-eventlog -logname Application -Source "Restore-Exact-Admins-to-Test" -entrytype Error -eventid 9000 -message $result}
}
$resulttotal = $resulttotal + ([Environment]::NewLine) + $result
}
$finishdate=get-date
$lasted=$finishdate - $startdate
$lhour=$lasted.hours
$lminute=$lasted.minutes
$sourcedate=$RestorePoint.CreationTime
$body = "These are the results of the last sync (Source backup dated: $sourcedate) started at: $startdate and lasted $lhour hours and $lminute minutes `r`n$resulttotal `r`n`nWith kind regards."
send-mailmessage -to $maildestination -from $sender -Subject $subject -Body $body -smtpserver $mailserver
if ($?) {write-eventlog -logname Application -Source "Restore-Databases-to-Test" -entrytype Information -eventid 1000 -message "Mailmessage send succesfully with body:`r`n$body"}
if ($resulttotal -contains "failed"){send-mailmessage -to helpdesk@mailfqdn.com -from $sender -Subject $subject -Body $body -smtpserver $mailserver
write-eventlog -logname Application -Source "Restore-Databases-to-Test" -entrytype Error -eventid 1000 -message "One of the Backups failed:`r`n$body"}
#Stop-Transcript