PowerShell, SQL Server, Technology

How to (Easily) backup MS SQL Databases using Powershell

Backing up SQL databases with Powershell is quite useful and is probably easier than you think. This can be used in situations where you only have the SQL Server Express edition installed and can’t manage backups through the SQL Management Studio, or if you just want to do some one off backups

Contents

Install SQLServer Powershell module

Get a list of all databases on a remote server 

This will prompt you for credentials to use, then get a list of all the databases in the remote server SQL instance:
Get-SqlDatabase -Credential $Credential -ServerInstance yoursqlserver

Backing up a single database  

The following will backup a single database to the default MS SQL backup directory, with the default backup name:
Backup-SqlDatabase -ServerInstance yourSQLServer -Database yourdatabase

By adding the ‘BackupFile’ parameter we can select the location of where to put the backup:
Backup-SqlDatabase -ServerInstance yourSQLServer -Database yourdatabase -BackupFile “C:\Backups\BackupName.bak”

Backup all databases in an SQL instance and name with todays date  

The following gets all the databases in an instance (except the temp database) , then backups these databases and names them with todays date:
Get-SqlDatabase -Credential $Credential -ServerInstance yourSQLServer | foreach{
Backup-SqlDatabase -DatabaseObject $_ -BackupFile “$($_.NAME)_db_$(Get-Date -UFormat %Y%m%d%H%M).bak”}

Leave a Reply

Your email address will not be published. Required fields are marked *