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 .
