The Backup-SqlDatabase and Restore-SqlDatabase are the two main cmdlets available to do backup and restore operations. In addition, there are other cmdlets that may be required to automate backups to Windows Azure Blob storage like the set of SqlCredential cmdlets Following is a list of PowerShell cmdlets available in SQL Server 2016 that are used in backup and restore operations:

Backup-SqlDatabase

This cmdlet is used to create a SQL Server Backup.

Restore-SqlDatabase

Used to restore a database.

New-SqlCredential

This cmdlet is used to create a SQL Credential to use for SQL Server Backup to Windows Azure Storage. For more information on credentials and their use in SQL Server Backup and Restore, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

Get-SqlCredential

This cmdlet is used to retrieve the Credential object and its properties.

Remove-SqlCredential

Delete a SQL Credential object

Set-SqlCredential

This cmdlet is used to change or set the properties of the SQL Credential Object.

Nếu có lỗi code thì chạy dòng này để bật chức năng cmlet

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

Hoặc tham khảo theo link: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_execution_policies?view=powershell-5.1

Code Example:

1/, Backup one Database:

$dt = Get-Date -Format yyyyMMddHHmmss

$dbname = ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''dangtest''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Backup-SqlDatabase -ServerInstance DANG -Database $dbname -BackupFile "E:\New folder\$($dbname)_db_$($dt).bak"

2/. Backup multible Database:

Set-Location SQLSERVER:\SQL\DANG\DEfault\Databases

foreach ($db in (Get-ChildItem))

{

    $dbname = $db.Name

    $dt = Get-Date -Format yyyyMMddHHmmss

    Backup-SqlDatabase -Database $dbname -BackupFile "E:\New folder\$($dbname)_db_$($dt).bak"

}

Restore database:

Restore-SqlDatabase -ServerInstance ".\DANG" -Database "SMILE_FO_Cus" -BackupFile "E:\SMILE_FO_Cus_db_20181218214641.bak"

===================================================================

Code demo all:

function Get-Zip

{

param([string]$zipfilename)

if(test-path($zipfilename))

{

$shellApplication = new-object -com shell.application

$zipPackage = $shellApplication.NameSpace($zipfilename)

$zipPackage.Items() | Select Path

}

}

function Extract-Zip

{

param([string]$zipfilename, [string] $destination)

if(test-path($zipfilename))

{ 

$shellApplication = new-object -com shell.application

$zipPackage = $shellApplication.NameSpace($zipfilename)

$destinationFolder = $shellApplication.NameSpace($destination)

$destinationFolder.CopyHere($zipPackage.Items())

}

}

#Get-Zip E:\DATABASE\DataBase_Test_2_2.zip

extract-zip E:\DATABASE\DataBase_Test_2_2.zip E:\DATABASE

#---------------------------------------------------------------

$source = "E:\DATABASE\DataBase_Test\"

$destination = "E:\DATABASE\DataBase_Test_2.rar"

 If(Test-path $destination) {Remove-item $destination}

Add-Type -assembly "system.io.compression.filesystem"

[io.compression.zipfile]::CreateFromDirectory($Source, $destination)

#----------------------------------------------------------------