Backup SQL Server 2012 with PowerShell cmd


If you want to use the SQL Server PowerShell provider from a regular PowerShell prompt, you’ll first need to import the sqlps module into PowerShell. You can import it by typing the following:

Import-Module sqlps     

You’ll get a message indicating that unapproved verbs exist in the module. If you use the DisableNameChecking switch below, you won’t see the warning:

Import-Module sqlps –DisableNameChecking

Once you’ve loaded the module you can easily create a backup for a database using a command like this:

$dt = Get-Date -Format yyyyMMddHHmmss

$dbname = ‘test1’

Backup-SqlDatabase -ServerInstance Win-54BFGM96VHD\SQLEXPRESS -Database $dbname -BackupFile “C:\HostingSpaces\test1\$($dbname)_db_$($dt).bak”The location of backup files that i set is C:\HostingSpaces\test1\xxx, make sure that the account (MSSQL$SQLEXPRESS) has write permission to this ‘test1’ directory.
Then change the current directory to  “C:\HostingSpaces\test1″which script file is located, and  execution this script, it will backup ‘test1’ database .