February 22, 2017 at 4:00 am
Hello,
Currently while doing a refresh of our QA environment from LIVE ( restoring AG databases from LIVE to QA) I do the following steps:
1. backup LIVE databases
2. Remove the QA databases from the Availability Group
3. Restore the databases to the QA environment
4.Add the databases back to the AG
All this is a manual process.
I want to automate this whole exercise and schedule this as a job, so that this could be done on a regular basis ( like monthly)
Please provide any suggestions or me in the right direction for this automation.
Regards,
TJ
February 22, 2017 at 6:06 am
This sounds like a job for....Powershell.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 22, 2017 at 6:25 am
Henrico Bekker - Wednesday, February 22, 2017 6:06 AMThis sounds like a job for....Powershell.
Thanks Bekker, I could figure that out ..but the issue is that I don't have much knowledge of Powershell and wanted to see if other DBAs might have figured out a way around that..
February 22, 2017 at 6:36 am
Agreed. Powershell to the rescue. Just script out each step you do manually and work them into Powershell. One step at a time. Test each one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 22, 2017 at 6:57 am
Some searching would have led you to some good material, but here is a good start for you.
Just be aware, your exact requirement is not to refresh your primary DB, but secondary, so just filter the steps that is applicable.
https://sqldbawithabeard.com/2014/09/04/refreshing-availability-group-database-with-powershell/
and from MSDN, the generic piece of code that will perform the basic backup, restore, add to AG.
$DatabaseBackupFile = "\\share\backups\MyDatabase.bak"
$LogBackupFile = "\\share\backups\MyDatabase.trn"
$MyAgPrimaryPath = "SQLSERVER:\SQL\PrimaryServer\InstanceName\AvailabilityGroups\MyAg"
$MyAgSecondaryPath = "SQLSERVER:\SQL\SecondaryServer\InstanceName\AvailabilityGroups\MyAg"
Backup-SqlDatabase -Database "MyDatabase" -BackupFile $DatabaseBackupFile -ServerInstance "PrimaryServer\InstanceName"
Backup-SqlDatabase -Database "MyDatabase" -BackupFile $LogBackupFile -ServerInstance "PrimaryServer\InstanceName" -BackupAction 'Log'
Restore-SqlDatabase -Database "MyDatabase" -BackupFile $DatabaseBackupFile -ServerInstance "SecondaryServer\InstanceName" -NoRecovery
Restore-SqlDatabase -Database "MyDatabase" -BackupFile $LogBackupFile -ServerInstance "SecondaryServer\InstanceName" -RestoreAction 'Log' -NoRecovery
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database "MyDatabase"
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database "MyDatabase"
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply