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
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
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”
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”}
