Occasionally you may need to create a backup of a SQL Database outside of the normal backup schedule. For instance, a test environment where you need to restore back to a known state in the future, or if the DB Log Files are growing out of control and you need to shrink them, etc. This post will cover how to create a database backup using PowerShell cmdlets included with SQL Server 2012 and later releases.
SQL PowerShell Module Basics
First the basics: When working with the SQL PowerShell module you can either open it via the Start Menu Shortcut, the links within SQL Server Management Studio (SSMS) or by simply importing the module with the following command:
Import-Module SQLPS -DisableNameChecking
Once imported, it will change the current working directory to the SQL Server, where you can explore everything with standard
cd commands. For instance you would view the databases by going to:
servername is the local SQL Servername and
instancename is the instance name, which is usually “default” if you don’t use named SQL instances.
Backing up a SQL Database
Now, once you are in the databases “folder”, you can backup a database by using something similar to:
Backup-SqlDatabase -Database DatabaseName -BackupAction Database -BackupFile "U:\MSSQL\SQL_BACKUP\Databasename-psbackup.bak" -Compression On -Initialize
DatabaseName is the name of the DB you want to backup,
U:\MSSQL\.... is the location of where the backup should be located. Note that normally this needs to be on a local disk of the SQL Server.
Restoring a SQL Database
Now, if for some reason you need to restore a DB from a backup, go back into the “database” folder, then execute something similar to:
Restore-SqlDatabase -Database DatabaseName -BackupFile "U:\MSSQL\SQL_BACKUP\Databasename-psbackup.bak" -ReplaceDatabase
DatabaseName is the name of the DB you want to restore and
U:\MSSQL\.... is the location of the backup file to use. Note that this command will drop all connections to the database and restore the data from the backup.