SQL Server
Microsoft リレーショナル データベース管理分析システムのファミリで、電子商取引、基幹業務、データ ウェアハウジングなどのソリューションで使用されています。
26 件の質問
Azureのrecovery service valutにbackupを即時取得するためのコマンドについて
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 48%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
諏訪 大空
0
評価のポイント
以下のコマンドはAzureにバックアップを取得するコマンドになっていますか？
param (
[string]$SqlServerName,
[string]$DatabaseName,
[string]$JobName,
[string]$BackupDirectory
)
SQL Server ジョブを実行する
$connectionString = "Server=$SqlServerName;Database=master;Integrated Security=True;"
$jobCommand = "EXEC msdb.dbo.sp_start_job @job_name = N'$JobName'"
Invoke-Sqlcmd -ConnectionString $connectionString -Query $jobCommand
ジョブのステータスを確認するクエリ
$statusQuery = @"
SELECT job_id, name, enabled,
CASE
WHEN current_execution_status = 1 THEN 'Executing'
WHEN current_execution_status = 4 THEN 'Idle'
ELSE 'Other'
END AS current_execution_status
FROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
WHERE name = N'$JobName' AND activity.run_requested_date IS NOT NULL
"@
ジョブが完了するまで待機
do {
Start-Sleep -Seconds 10
$statusResult = Invoke-Sqlcmd -ConnectionString $connectionString -Query $statusQuery | Select-Object -ExpandProperty current_execution_status
} while ($statusResult -eq 'Executing')
データベースのバックアップを取得
$backupFileName = "$BackupDirectory$DatabaseName-$(Get-Date -Format 'yyyyMMddHHmmss').bak"
$backupCommand = "BACKUP DATABASE [$DatabaseName] TO DISK = N'$backupFileName' WITH NOFORMAT, NOINIT, NAME = N'$DatabaseName-Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
Invoke-Sqlcmd -ConnectionString $connectionString -Query $backupCommand
Write-Output "Backup completed: $backupFileName"
{count} 件の投票
-
諏訪 大空 0 評価のポイント
2024-05-27T04:39:51.75+00:00 Azureにサインイン
Connect-AzAccount -Identity
SQL Serverとデータベースの情報を設定
$SqlserverName = 'serverName.database.windows.net'
$DatabaseName = 'master'
$JobName = 'test'
$UserId = '<your_user_id>'
$Password = '<your_password>'
SQL Serverへの接続文字列を設定
$connectionString ="Server=$SqlserverName;Database=$DatabaseName;User Id=$UserId;Password=$Password;"
実行するSQLコマンドを設定
$JobCommand = "EXEC msdb.dbo.sp_start_job @job_name = N'$JobName'"
SQLクライアントを使ってSQLコマンドを実行 try { # .NET SQLクライアントをロード $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() $command = $connection.CreateCommand() $command.CommandText = $JobCommand $command.ExecuteNonQuery() Write-Output "ジョブが正常に開始されました。" $connection.Close() } catch { Write-Output "エラーが発生しました: $_" }
-
諏訪 大空 0 評価のポイント
2024-05-27T05:42:14.0933333+00:00 Azureにサインイン
Connect-AzAccount
パラメータの設定
$resourceGroupName = "yourResourceGroupName"
$vaultName = "yourRecoveryServicesVaultName"
$vmName = "yourVMName"
$vmResourceGroupName = "yourVMResourceGroupName"
$sqlInstanceName = "MSSQLSERVER" # デフォルトインスタンスの場合
$databaseName = "yourDatabaseName"
直近一週間の開始時間を取得
$startTime = (Get-Date).AddDays(-7)
Recovery Services Vaultを取得
$vault = Get-AzRecoveryServicesVault -ResourceGroupName $resourceGroupName -Name $vaultName
Set-AzRecoveryServicesVaultContext -Vault $vault
保護されたアイテム（SQLデータベース）を取得
$protectedItem = Get-AzRecoveryServicesBackupItem -WorkloadType "SQLDataBase" -ContainerType "AzureVMAppContainer" -VaultId $vault.ID | Where-Object {
$_.Properties.FriendlyName -eq "$vmName\\$sqlInstanceName\\$databaseName"}
過去一週間のバックアップジョブの履歴を取得
$backupJobs = Get-AzRecoveryServicesBackupJob -VaultId $vault.ID -Status "Completed" -StartTime $startTime | Where-Object {
$_.WorkloadType -eq "SQLDataBase" -and $_.BackupManagementType -eq "AzureVM"}
if ($backupJobs) {
foreach ($job in $backupJobs) { Write-Output "Backup Job ID: $($job.JobID)" Write-Output "Backup Status: $($job.Status)" Write-Output "Backup Start Time: $($job.StartTime)" Write-Output "Backup End Time: $($job.EndTime)" Write-Output "Backup Type: $($job.BackupType)" Write-Output "" }} else {
Write-Output "No backup job history found in the last week."}
-
諏訪 大空 0 評価のポイント
2024-05-27T07:16:33.5866667+00:00 Azureにサインイン
Connect-AzAccount
パラメータの設定
$resourceGroupName = "yourResourceGroupName"
$vaultName = "yourRecoveryServicesVaultName"
$vmName = "yourVMName"
$vmResourceGroupName = "yourVMResourceGroupName"
$sqlInstanceName = "MSSQLSERVER" # デフォルトインスタンスの場合
$databaseName = "yourDatabaseName"
Recovery Services Vaultを取得
$vault = Get-AzRecoveryServicesVault -ResourceGroupName $resourceGroupName -Name $vaultName
Set-AzRecoveryServicesVaultContext -Vault $vault
保護されたアイテム（SQLデータベース）を取得
$protectedItem = Get-AzRecoveryServicesBackupItem -WorkloadType "SQLDataBase" -ContainerType "AzureVMAppContainer" -VaultId $vault.ID | Where-Object {
$_.Properties.FriendlyName -eq "$vmName\\$sqlInstanceName\\$databaseName"}
if ($protectedItem) {
# 保護されたアイテムのバックアップポリシーを取得 $backupPolicy = Get-AzRecoveryServicesBackupProtectionPolicy -ItemId $protectedItem.ID -VaultId $vault.ID if ($backupPolicy) { Write-Output "Backup Policy Name: $($backupPolicy.Name)" Write-Output "Backup Policy Schedule: $($backupPolicy.ScheduleType)" Write-Output "Backup Policy Retention Period: $($backupPolicy.RetentionDuration)" } else { Write-Output "No backup policy found for the database." }} else {
Write-Output "Database not found in the backup items."}
-
諏訪 大空 0 評価のポイント
2024-05-27T07:17:30.6633333+00:00 Azureにサインイン
Connect-AzAccount
パラメータの設定
$resourceGroupName = "yourResourceGroupName"
$vaultName = "yourRecoveryServicesVaultName"
$vmName = "yourVMName"
$vmResourceGroupName = "yourVMResourceGroupName"
$sqlInstanceName = "MSSQLSERVER" # デフォルトインスタンスの場合
$databaseName = "yourDatabaseName"
Recovery Services Vaultを取得
$vault = Get-AzRecoveryServicesVault -ResourceGroupName $resourceGroupName -Name $vaultName
Set-AzRecoveryServicesVaultContext -Vault $vault
保護されたアイテム（SQLデータベース）を取得
$protectedItem = Get-AzRecoveryServicesBackupItem -WorkloadType "SQLDataBase" -ContainerType "AzureVMAppContainer" -VaultId $vault.ID | Where-Object {
$_.Properties.FriendlyName -eq "$vmName\\$sqlInstanceName\\$databaseName"}
if ($protectedItem) {
# 保護されたアイテムのバックアップポリシーを取得 $backupPolicy = Get-AzRecoveryServicesBackupProtectionPolicy -ItemId $protectedItem.ID -VaultId $vault.ID if ($backupPolicy) { Write-Output "Backup Policy Name: $($backupPolicy.Name)" Write-Output "Backup Policy Schedule: $($backupPolicy.ScheduleType)" Write-Output "Backup Policy Retention Period: $($backupPolicy.RetentionDuration)" } else { Write-Output "No backup policy found for the database." }} else {
Write-Output "Database not found in the backup items."}
-
諏訪 大空 0 評価のポイント
2024-05-27T07:50:37.21+00:00 SQL Serverとデータベースの情報を設定
$serverName = "yourServerName" # サーバー名またはIPアドレス
$databaseName = "yourDatabaseName" # データベース名
$userName = "yourUserName" # SQL Serverのユーザー名
$password = "yourPassword" # SQL Serverのパスワード
ODBC接続文字列を設定
$connectionString = "Driver={SQL Server};Server=$serverName;Database=$databaseName;Uid=$userName;Pwd=$password;"
実行するSQLクエリを設定
$sqlQuery = "SELECT * FROM yourTableName" # 実行したいSQLクエリ
SQL Serverに接続してSQLクエリを実行
try {
# ODBC接続を作成 $connection = New-Object System.Data.Odbc.OdbcConnection $connection.ConnectionString = $connectionString $connection.Open() # SQLコマンドを作成 $command = $connection.CreateCommand() $command.CommandText = $sqlQuery # SQLクエリを実行し、結果を取得 $dataAdapter = New-Object System.Data.Odbc.OdbcDataAdapter $dataAdapter.SelectCommand = $command $dataSet = New-Object System.Data.DataSet $dataAdapter.Fill($dataSet) # 結果を表示 $dataTable = $dataSet.Tables[0] $dataTable | Format-Table -AutoSize # 接続を閉じる $connection.Close()} catch {
Write-Output "エラーが発生しました: $_"}
-
諏訪 大空 0 評価のポイント
2024-05-27T08:32:40.9433333+00:00 param (
[string]$SqlServerName, [string]$DatabaseName, [string]$JobName, [string]$BackupDirectory)
SQL Server ジョブを実行する
$connectionString = "Server=$SqlServerName;Database=master;Integrated Security=True;"
$jobCommand = "EXEC msdb.dbo.sp_start_job @job_name = N'$JobName'"
Invoke-Sqlcmd -ConnectionString $connectionString -Query $jobCommand
ジョブのステータスを確認するクエリ
$statusQuery = @"
SELECT job_id, name, enabled,
CASE WHEN current_execution_status = 1 THEN 'Executing' WHEN current_execution_status = 4 THEN 'Idle' ELSE 'Other' END AS current_execution_statusFROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
WHERE name = N'$JobName' AND activity.run_requested_date IS NOT NULL
"@
ジョブが完了するまで待機
do {
Start-Sleep -Seconds 10 $statusResult = Invoke-Sqlcmd -ConnectionString $connectionString -Query $statusQuery | Select-Object -ExpandProperty current_execution_status} while ($statusResult -eq 'Executing')
データベースのバックアップを取得
$backupFileName = "$BackupDirectory$DatabaseName-$(Get-Date -Format 'yyyyMMddHHmmss').bak"
$backupCommand = "BACKUP DATABASE [$DatabaseName] TO DISK = N'$backupFileName' WITH NOFORMAT, NOINIT, NAME = N'$DatabaseName-Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
Invoke-Sqlcmd -ConnectionString $connectionString -Query $backupCommand
Write-Output "Backup completed: $backupFileName"
-
諏訪 大空 0 評価のポイント
2024-05-27T08:55:42.8733333+00:00 param (
[string]$SqlServerName, [string]$DatabaseName, [string]$JobName, [string]$BackupDirectory)
SQL Server ジョブを実行する
$connectionString = "Server=$SqlServerName;Database=master;Integrated Security=True;"
$jobCommand = "EXEC msdb.dbo.sp_start_job @job_name = N'$JobName'"
Invoke-Sqlcmd -ConnectionString $connectionString -Query $jobCommand
ジョブのステータスを確認するクエリ
$statusQuery = @"
SELECT job_id, name, enabled,
CASE WHEN current_execution_status = 1 THEN 'Executing' WHEN current_execution_status = 4 THEN 'Idle' ELSE 'Other' END AS current_execution_statusFROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
WHERE name = N'$JobName' AND activity.run_requested_date IS NOT NULL
"@
ジョブが完了するまで待機
do {
Start-Sleep -Seconds 10 $statusResult = Invoke-Sqlcmd -ConnectionString $connectionString -Query $statusQuery | Select-Object -ExpandProperty current_execution_status} while ($statusResult -eq 'Executing')
データベースのバックアップを取得
$backupFileName = "$BackupDirectory$DatabaseName-$(Get-Date -Format 'yyyyMMddHHmmss').bak"
$backupCommand = "BACKUP DATABASE [$DatabaseName] TO DISK = N'$backupFileName' WITH NOFORMAT, NOINIT, NAME = N'$DatabaseName-Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
Invoke-Sqlcmd -ConnectionString $connectionString -Query $backupCommand
Write-Output "Backup completed: $backupFileName"
-
諏訪 大空 0 評価のポイント
2024-05-27T15:27:34.1466667+00:00 param (
[string]$ResourceGroupName, [string]$VaultName, [string]$SqlServerName, [string]$DatabaseName)
Azureアカウントにログイン（Run As アカウントを使用）
$connectionName = "AzureRunAsConnection"
$servicePrincipalConnection = Get-AutomationConnection -Name $connectionName
Add-AzAccount `
-ServicePrincipal ` -TenantId $servicePrincipalConnection.TenantId ` -ApplicationId $servicePrincipalConnection.ApplicationId ` -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprintRecovery Services Vaultに対してコンテキストを設定
$vault = Get-AzRecoveryServicesVault -ResourceGroupName $ResourceGroupName -Name $VaultName
Set-AzRecoveryServicesVaultContext -Vault $vault
バックアップポリシーの取得
$items = Get-AzRecoveryServicesBackupItem -WorkloadType "AzureSQL" -VaultId $vault.ID
指定されたSQL Serverデータベースのバックアップポリシーを取得
$backupItem = $items | Where-Object { $.Name -eq $DatabaseName -and $.ServerName -eq $SqlServerName }
if ($backupItem -ne $null) {
$policy = Get-AzRecoveryServicesBackupProtectionPolicy -Name $backupItem.PolicyName -VaultId $vault.ID Write-Output "Backup policy for $DatabaseName on $SqlServerName:" Write-Output $policy} else {
Write-Output "No backup policy found for $DatabaseName on $SqlServerName"}
-
諏訪 大空 0 評価のポイント
2024-05-28T01:10:09.0066667+00:00 param (
[string]$ResourceGroupName, [string]$VaultName, [string]$SqlServerName, [string]$DatabaseName)
マネージドIDを使用してAzureに接続
Connect-AzAccount -Identity
Recovery Services Vaultに対してコンテキストを設定
$vault = Get-AzRecoveryServicesVault -ResourceGroupName $ResourceGroupName -Name $VaultName
Set-AzRecoveryServicesVaultContext -Vault $vault
バックアップアイテムの取得を効率化
$backupItem = Get-AzRecoveryServicesBackupItem -VaultId $vault.ID -WorkloadType "AzureSQL" | Where-Object {
$_.Properties.FriendlyName -eq $DatabaseName -and $_.Properties.ServerName -eq $SqlServerName}
if ($backupItem -ne $null) {
$policyName = $backupItem.Properties.PolicyName $policy = Get-AzRecoveryServicesBackupProtectionPolicy -VaultId $vault.ID | Where-Object { $_.Name -eq $policyName } Write-Output "Backup policy for $DatabaseName on $SqlServerName:" Write-Output $policy} else {
Write-Output "No backup policy found for $DatabaseName on $SqlServerName"}
-
諏訪 大空 0 評価のポイント
2024-05-28T02:59:43.1433333+00:00 Azureにサインイン
Connect-AzAccount
パラメータの設定
$resourceGroupName = "yourResourceGroupName"
$vaultName = "yourRecoveryServicesVaultName"
$vmName = "yourVMName"
$vmResourceGroupName = "yourVMResourceGroupName"
$sqlInstanceName = "MSSQLSERVER" # デフォルトインスタンスの場合
$databaseName = "yourDatabaseName"
Recovery Services Vaultを取得
$vault = Get-AzRecoveryServicesVault -ResourceGroupName $resourceGroupName -Name $vaultName
Set-AzRecoveryServicesVaultContext -Vault $vault
保護されたアイテム（SQLデータベース）を取得
$container = Get-AzRecoveryServicesBackupContainer -ContainerType "AzureVMAppContainer" -ResourceGroupName $resourceGroupName -VaultName $vaultName | Where-Object {
$_.FriendlyName -eq $vmName}
if ($container) {
$protectedItem = Get-AzRecoveryServicesBackupItem -WorkloadType "SQLDataBase" -VaultId $vault.ID | Where-Object { $_.Properties.FriendlyName -eq "$vmName\\$sqlInstanceName\\$databaseName" -and $_.ContainerName -eq $container.Name } if ($protectedItem) { # 保護されたアイテムのバックアップポリシーを取得 $backupPolicy = Get-AzRecoveryServicesBackupProtectionPolicy -VaultId $vault.ID | Where-Object { $_.Name -eq $protectedItem.Properties.PolicyName } if ($backupPolicy) { Write-Output "Backup Policy Name: $($backupPolicy.Name)" Write-Output "Backup Policy Schedule: $($backupPolicy.ScheduleType)" Write-Output "Backup Policy Retention Period: $($backupPolicy.RetentionDuration)" } else { Write-Output "No backup policy found for the database." } } else { Write-Output "Database not found in the backup items." }} else {
Write-Output "Container not found."}
-
諏訪 大空 0 評価のポイント
2024-06-05T00:21:45.51+00:00 Perf
| where ObjectName == "LogicalDisk"
| where CounterName == "% Free Space" or CounterName == "Free Megabytes"
| project TimeGenerated, Computer, InstanceName, CounterName, CounterValue
| order by TimeGenerated desc
-
諏訪 大空 0 評価のポイント
2024-06-06T07:35:11.0966667+00:00 -- データベースレベルの監査仕様を確認する
SELECT
d.name AS database_name, das.audit_spec_id, das.name AS audit_spec_name, das.create_date, das.modify_date, das.is_state_enabled, sa.name AS server_audit_nameFROM
sys.database_audit_specifications AS dasJOIN
sys.databases AS d ON das.database_id = d.database_idJOIN
sys.server_audits AS sa ON das.audit_guid = sa.audit_guid; -
諏訪 大空 0 評価のポイント
2024-06-06T07:45:15.35+00:00 -- データベースレベルの監査仕様を確認する
SELECT
d.name AS database_name, das.audit_spec_id, das.name AS audit_spec_name, das.create_date, das.modify_date, das.is_state_enabled, sa.name AS server_audit_nameFROM
sys.database_audit_specifications AS dasJOIN
sys.databases AS d ON das.database_id = d.database_idJOIN
sys.server_audits AS sa ON das.audit_guid = sa.audit_guid; -
諏訪 大空 0 評価のポイント
2024-06-12T07:37:25.4733333+00:00 SELECT
database_id, total_log_size_in_bytes / 1024.0 / 1024.0 AS total_log_size_in_MB, used_log_space_in_bytes / 1024.0 / 1024.0 AS used_log_space_in_MB, (total_log_size_in_bytes - used_log_space_in_bytes) / 1024.0 / 1024.0 AS unused_log_space_in_MB, used_log_space_in_percentFROM sys.dm_db_log_space_usage;
-
諏訪 大空 0 評価のポイント
2024-06-12T07:41:14.41+00:00 SELECT
database_id, total_log_size_in_bytes / 1024.0 / 1024.0 AS total_log_size_in_MB, used_log_space_in_bytes / 1024.0 / 1024.0 AS used_log_space_in_MB, (total_log_size_in_bytes - used_log_space_in_bytes) / 1024.0 / 1024.0 AS unused_log_space_in_MB, used_log_space_in_percentFROM sys.dm_db_log_space_usage;
サインインしてコメントする