Backing Up an SQL Database with PowerShell

Before making any changes in the production environment, every DBA should take a backup of their SQL database so that they can readily restore it if something goes wrong during the migration. If you have SQL Server Management Studio (SSMS), you can backup and restore databases with ease (Refer this artcle: Backup and Restore SQL Database using SSMS). Using Server Management Objects in Powershell, you may backup and restore SQL databases (SMO).

We’ll use the Server Management Objects (SMO) namespace class Microsoft.SqlServer.Management.Smo.Backup to get a SQL Database backup. To finish the backup procedure, follow the instructions below.

1. Paste the Powershell script below into a Notepad file.
2. Replace $sqlName, $dbname, and $backupPath with the names of your SQL Server instance, database, and backup destination path, respectively.
3. Save the Notepad file with the extension on your hard drive.
Backup-SQL-Database-Script.ps1 is an example.

Download Backup-SQL-Databse.ps1 from Powershell.

# Set SQL Server instance name
$sqlName= "SQLExpress"

# Set the databse name which you want to backup
$dbname= "Clear2allDB"

# Set the backup file path
$backupPath= "D:\Clear2allDB.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

#Create SMO Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")

$dbBackup.Database = $dbname

#Add the backup file to the Devices
$dbBackup.Devices.AddDevice($backupPath, "File")

#Set the Action as Database to generate a FULL backup
$dbBackup.Action="Database"

#Call the SqlBackup method to complete backup
$dbBackup.SqlBackup($sqlServer)

Write-Host "...Backup of the database"$dbname" completed..."

Using a Powershell script, restore a SQL Server database.

We’ll use the Server Management Objects (SMO) namespace class Microsoft.SqlServer.Management.Smo.Restore to restore a SQL database backup. To complete the database restore process, follow the steps below.

1. Paste the Powershell script below into a Notepad file.
2. Replace the values for $sqlName, $dbname, and $backupPath with your actual SQL Server instance name, database name, and backup file destination path.
3. Save the Notepad file with the extension on your hard drive.
ps1 (for example, Restore-SQL-Database-Script.ps1)

# Set SQL Server instance name
$sqlName= "SQLExpress"

# Set new or existing databse name to restote backup
$dbname= "Clear2allDB"

# Set the existing backup file path
$backupPath= "D:\Clear2allDB.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

# Create SMo Restore object instance
$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")

# Set database and backup file path
$dbRestore.Database = $dbname
$dbRestore.Devices.AddDevice($backupPath, "File")

# Set the databse file location
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $dbname + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "" + $dbRestore.Database + "_Log.ldf"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

# Call the SqlRestore mathod to complete restore database
$dbRestore.SqlRestore($sqlServer)

Write-Host "...SQL Database"$dbname" Restored Successfullyy..."

 

Using Simple powershell for SQL Server

Many people use PowerShell to back up SQL databases. The command-line interface can conduct full database backups, file backups, and transaction log backups.

There are many ways to backup a database in PowerShell, but one of the simplest is to use the Backup-SqlDatabase command.

Backup-SqlDatabase command example:

PS C:\> Backup-SqlDatabase -ServerINstance “Computer\Instance” -Database “Databasecentral”
This will create a database backup of a database with the name ‘Databasecentral’ (or the name of your chosen database’.

To back up a transaction log:

PS C:\> Backup-SqlDatabase -ServerInstance “Computer\Instance” -Database “Databasecentral” -BackupAction Log

This will create a transaction log of the selected database.

For more sql articles visit https://sqlserver-dba.co.uk