Good Day:
So in this series of posts, I have created some powershell scripts to lay down SQL server based on your configuration file, configure your FCI and also add your availability replica. Next if you are not using automation seeding is to backup and restore your DBs. In my case a full backup of all dbs on the primary instance, then I restore (in this simple case) one DB for sake of keeping the script short. However you could easily change it to restore all the fulls. Then cut a DIFF, restore that and finally add the DB to the AAG. I also have used a -DestinationDataDirectory and log directory if this needs to change for some reason (optional).
Hopefully my formatting comes across okay, they code editor can be a little fickle.
|
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 |
<# ##Backup ALL Databases on Primary Instance to File Share Location $existinglocation = Get-Location $dt = Get-Date -Format yyyyMMddHHmmss Set-Location "SQLSERVER:\SQL\GSO-MGT-SQL\DEFAULT\Databases" foreach ($database in (Get-ChildItem)) { $dbName = $database.Name Backup-SqlDatabase -Database $dbName -BackupFile "\\GSO-MGT-VM-ADAM\Share\FULL\$dbName$dt.bak" } Set-Location -Path $existinglocation Set-Location "SQLSERVER:\SQL\GSO-MGT-VM-ADAM\DEFAULT\Databases" Restore-DbaDatabase -SqlInstance GSO-MGT-VM-ADAM -Path "\\GSO-MGT-VM-ADAM\Share\FULL\DB4$dt.bak" -DatabaseName "DB4" -DestinationDataDirectory N:\DATA1 -DestinationLogDirectory L:\Log -NoRecovery Set-Location -Path $existinglocation #Invoke-sqlcmd -Query "exec msdb..sp_update_job @job_name = 'DBA.DatabaseBackup.USER.DATABASE.LOG', @enabled = 0 " -ServerInstance GSO-MGT-VM-ADAM Start-Sleep 5 Set-Location "SQLSERVER:\SQL\GSO-MGT-SQL\DEFAULT\Databases" foreach ($database in (Get-ChildItem)) { $dbName = $database.Name Backup-SqlDatabase -Database $dbName -BackupFile "\\GSO-MGT-VM-ADAM\Share\DIFF\$dbName$dt.bak" -Incremental } Start-Sleep 5 Set-Location "SQLSERVER:\SQL\GSO-MGT-VM-ADAM\DEFAULT\Databases" Restore-DbaDatabase -SqlInstance GSO-MGT-VM-ADAM -Path "\\GSO-MGT-VM-ADAM\Share\DIFF\DB4$dt.bak" -DatabaseName "DB4" -DestinationDataDirectory N:\DATA1 -DestinationLogDirectory L:\Log -WithReplace -Continue Set-Location "SQLSERVER:\SQL\GSO-MGT-VM-ADAM\DEFAULT\AvailabilityGroups\AG1" Add-SqlAvailabilityDatabase -Database "DB4" Start-Sleep 5 Set-Location -Path $existinglocation #Invoke-sqlcmd -Query "exec msdb..sp_update_job @job_name = 'DBA.DatabaseBackup.USER.DATABASE.LOG', @enabled = 1 " -ServerInstance GSO-MGT-VM-ADAM Set-Location -Path $existinglocation |
Leave A Reply