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 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
- This location show the backup occur in the working directory
Backup-SqlDatabase -Database “MainDB”
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
- 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
- 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.