Bulk Backup Database Scripts (PowerShell edition)

Begin

Back up a database yesterday to test, found that the backup file is very large. Later to check the use of the backup script, the original backup script has no compression function.

The previous backup scripts are now modified to support compressed backups and support for copy-only backups (copyonly).

Backup database (full backup) script

(Note: Initially writes this script to be able to back up the database in bulk, improving productivity, as well as restoring database scripts mentioned later.) ）

<#=====================================================================#>##备份数据库 (full backup) V2.0 Andy 2017-4-13 Adds a set of compressed backups, and whether to use the Copy backup feature##备份数据库 (full backup) V1.0 Andy####[string]$serverInstance="Ip\instancename"[string]$userName="Login"[string]$password="Password"[string]$Path="\\xxx.xxx.xxx.xxx\xxxBackup"[string]$DBList="dbname" #(optional) List of databases, separated by ', ' between databases, leaving blank for all databases[BOOL]$CopyOnly=$true;#true to copy only the backup, otherwise it is not just a copy of the backup, but a part of the usual backup sequence[Int32]$CompressionOption=1#1 boot compression, 2 disable compression[BOOL]$AddBackupTime= 1#(optional) backup file name plus backup time in the format: _yyyymmddhhmmss<#=====================================================================#>[System.Reflection.Assembly]:: LoadWithPartialName ("Microsoft.SqlServer.Smo") | out-null[system.reflection.assembly]:: LoadWithPartialName ("Microsoft.SqlServer.ConnectionInfo") | out-null[system.reflection.assembly]:: LoadWithPartialName ("Microsoft.SqlServer.SmoExtended") | out-null[system.reflection.assembly]:: LoadWithPartialName ("System.Data") | out-Null[string]$DBName=""[datetime]$StartTime=get-Date$Path=$Path+$(If($($Path. Split ("\")) [-1]-eq ""){""}Else{"\"})$ServerConnection=new-object"Microsoft.SqlServer.Management.Common.ServerConnection" $serverInstance,$userName,$passwordClear-host#clear the contents of the console#--------------------------------------#Check BACKUP Database inventory#--------------------------------------Functioncheckdb{Param([String]$DBvar)        Begin{[Boolean]$CheckResult=$false    }    Process    {        If($DBList -eq "")        {            $CheckResult=$true        }        Else        {            Foreach($x inch $DBList. Split (","))            {                If($x -eq $DBvar)                {                    $CheckResult=$true                     Break                }            }        }    }    End    {        Return $CheckResult    }}#1. Connecting Instancestry{$ServerConnection. Connect ()}catch{Write-host"Unable to connect to DB instance: $serverInstance"-Foregroundcolor Red}#2. Determine if the backup path is correct[BOOL]$PathExists=test-path$Pathif($PathExists -eq $false) {Write-host"Invalid path: $Path"-Foregroundcolor Red}#3. Backing Up the databaseif($ServerConnection. IsOpen-and $PathExists -eq $true) {Try {$Server=new-object"Microsoft.SqlServer.Management.Smo.Server" $ServerConnection        $Backup=new-object"Microsoft.SqlServer.Management.Smo.Backup"[string]$strDate=if($AddBackupTime -eq $true) {Get-date-format"_yyyymmddhhmmss"}Else{""}                $Backup. Action=[microsoft.sqlserver.management.smo.backupactiontype]::Database$Backup. Incremental =$false        $Backup. compressionoption=$CompressionOption; $Backup. copyonly=$CopyOnly; foreach($DB inch $Server. Databases | Select-object-property Name,issystemobject | Where-object-filterscript {$_. Issystemobject-eq $false})        {            $DBName=$DB. Nameif(CheckDB ($DBName)-eq $true)#determine if $dbname is in the Backup database list            {                                $Backup. Database =$DBName                $Backup. Devices.clear ()$Backup. Devices.adddevice ($Path+$DBName+$strDate+". bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)$Backup. Sqlbackupasync ($Server)#asynchronous processing of backupsWrite-host"backing up $DBName ..."-backgroundcolor Blue-Foregroundcolor White$Backup. Wait ()#wait for backup to complete before continuingWrite-host"Complete the backup $DBName. "-backgroundcolor Green}} [TimeSpan]$Times= (get-date)-$StartTime[String]$Str=if($Times. Hours-eq0) {""}Else{$Times. Hours.tostring () +"hours"}        $Str+=if($Times. Minutes-eq0) {""}Else{$Times. Minutes.tostring () +"minutes"}        $Str+=if($Times. Seconds-eq0) {""}Else{$Times. Seconds.tostring () +"seconds"} Write-host"Total backup time: $Str"} Catch {Write-error$_Write-host"An error occurred during the backup $DB."-foregroundcolor Red}}

invocation Example

Compressed backup files are only more than 1G.

Restore database can refer to the PowerShell application-batch restore database (support full, differential, transaction log)

Bulk Backup Database Scripts (PowerShell edition)