MS SQL Backup using Powershell

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.

Backup SQL Server 2016 using Powershell

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

Backup a complete database

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

• Backup file is named as vembutesting.bak

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”

• This location show the backup occur in the working directory

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 transaction log named as Log1.trc

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

• Vembutesting.bak backup file is shared with network

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

• Database are backup under the specific location

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

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

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

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

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

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.

Saranya Venkatraman

I am a system administrator in Vembu technologies for past six months. I have a lot of interest in learning and sharing new technologies in networking and virtualization.