MS SQL Backup

MS SQL Backup copies data from SQL server database or / and its transaction log to a backup device. SQL Backup data is used to restore and recover data after a database failure or corruption.

Protect Your Data with BDRSuite

Cost-Effective Backup Solution for VMs, Servers, Endpoints, Cloud VMs & SaaS applications. Supports On-Premise, Remote, Hybrid and Cloud Backup, including Disaster Recovery, Ransomware Defense & more!

Backup SQL Server 2016 using Powershell

Let us overview database backup for SQL Server 2016 using powershell.

Backup a complete database

Download Banner
  • This command creates a complete database backup of the database named MainDB to the default backup location of the server instance Computer\Instance. The backup file is named MainDB.bak.
  • Backup-SqlDatabase -ServerInstance “Computer\Instance” -Database “MainDB”

    SQL backup

  • Backup file is named as vembutesting.bak

SQL backup

Backup a database based on location

  • This command creates a complete database backup of the database MainDB to the default backup location of the server instance Computer\Instance
  • Set-Location “SQLSERVER:\SQL\Computer\Instance”
    Backup-SqlDatabase -Database “MainDB”

    SQL backup

  • This location show the backup occur in the working directory

SQL Backup

Backup the transaction log

Transaction log is a history of actions executed by a database management system. This command creates a backup of the transaction log of the database MainDB to the default backup location of the server instance Computer\Instance.

Backup-SqlDatabase -ServerInstance “Computer\Instance” -Database “MainDB” -BackupActionLog

SQL backup

  • Backup transaction log named as Log1.trc

SQL backup

Backup a database and prompt for credentials

  • This command creates a complete database backup of the database MainDB using the sa SQL Server login
  • Enter your credential for windows powershell credential request
  • If credential is valid, it will create complete database backup to the default location
Backup-SqlDatabase -ServerInstance “Computer\Instance” -Database “MainDB” -Credential (Get-Credential “sa”)

SQL backup

Backup a database to a network file share

  • File sharing is the public or private sharing of computer data in a network with various levels of access privilege
  • This command creates a complete database backup of the database MainDB to the file \\mainserver\databasebackup\MainDB.bak
Backup-SqlDatabase -ServerInstance “Computer\Instance” -Database “MainDB” -BackupFile “\\mainserver\databasebackup\MainDB.bak”

SQL backup

  • Vembutesting.bak backup file is shared with network

SQL

Backup all databases in a server instance

  • This command backs up all databases on the server instance Computer\Instance to the default backup location
  • Get-ChildItem “SQLSERVER:\SQL\Computer\Instance\Databases” | Backup-SqlDatabase

    SQL backup

  • Database are backup under the specific location

SQL backup

Backup all databases in a server instance to a network file share

  • This command creates a full backup for each database on the server instance Computer\Instance to the share \\mainserver\databasebackup
  • The backup files are named .bak
  • SQL backup

  • Through Network,Full database backup is taken under the mentioned location

SQL backup data

Backup all files in secondary filegroups

  • A secondary filegroup contains secondary datafiles (ndf) and database objects
  • This command creates a full file backup of every file in the secondary filegroups
  • Backup-SqlDatabase -ServerInstance “Computer\Instance” -Database “MainDB” -BackupAction Files -DatabaseFileGroup “FileGroupJan”,”FileGroupFeb”
  • This command creates a full file backup of every file in the secondary filegroups PRIMARY and SECONDARY
  • SQL backup

  • Database FileGroup is created in the specified location/li>

SQL backup

Create a differential backup

  • A differential backup is a type of backup that copies all the data that has changed since the last full backup
  • This command creates a differential backup of the database MainDB to the default backup location of the server instance Computer\Instance
  • The backup file is named MainDB.bak

SQL backup

Conclusion

MS SQL Backup using powershell is a simple obvious process to backup SQL database with transaction logs.

Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.

Rate this post