In this recipe, we will see how to create a full database backup using PowerShell.
Using the AdventureWorks2014 database, we will create a full, compressed backup of the database to a timestamped backup (.bak) file in the C:\Backup folder. Feel free to use a database of your choice for this task.
The T-SQL syntax that will be generated by this PowerShell recipe will look similar to the following:
BACKUP DATABASE [AdventureWorks2014] TO DISK = N'C:\Backup\AdventureWorks2014_Full_20150314092409.bak' WITH NOFORMAT, INIT, NAME = N'AdventureWorks2014 Full Backup', NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
The steps to create a full database backup are as follows:
Open PowerShell ISE as administrator.
Import the
SQLPSmodule as follows:#import SQL Server module Import-Module SQLPS -DisableNameChecking
Add the following script and run:
$instanceName = "localhost" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server...